I am setting up a SQL script that creates a database from a variable name, and then takes that newly created database and restores it from a .bak file. I am having issues with some syntax in one of my commands that I am setting up, and wanted to ask if anybody could help me spot my syntax error? I am only going to paste my troubled snippet of code and its declarations, and if I am correct the issue lies in the way that I am declaring the file name paths. I have tried setting the paths to variables, but I still received errors due to the apostrophe placement. Thanks!!!
declare @DBname varchar(10), @sqlcommand Nvarchar(max)
set @DBname = 'testdb'
Code to create database, and set new database to single user mode
--restore database
set @sqlcommand = N'Restore DATAbase ' + @DBname + ' from disk = ''C:/loc_smartz_db0_template.bak'' with move '
+ @DBname + ' to ''C:/ProgramFiles/Microsoft SQL Server/MSSQL/Data/TestDatabase1.mdf'', move ' + @DBname + ' to ''C:/ProgramFiles/Microsoft SQL Server/MSSQL/Data/TestDatabase1.ldf'', Replace'
EXECUTE sp_executesql @sqlcommand
Code that sets database back to multiuser, and prints that the database was successfully created
See Question&Answers more detail:os