Styjun

You know how in SQL Server you can right click a table and choose to generate a drop and create sql and generate a restore sql as well? That's what I have listed below. The first list is hard-coded to work with one database. And the second list is my attempt which isn't working. It has a syntax error near the first + symbol.

Works

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'pubs'
GO
use [pubs]

GO
use [master]

GO
USE [master]
GO
ALTER DATABASE [pubs] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [pubs] Script Date: 3/25/2019 2:58:04 PM ******/
DROP DATABASE [pubs]
GO
RESTORE DATABASE [pubs] FROM DISK = N'C:UsersRSantiapubs.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
GO

My Attempt

DECLARE @lib nvarchar(500) = 'pubs'

EXECUTE sp_executesql

N'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = ' + '''' + @lib + '''' +
'GO
use ' + @lib + 
'GO
use [master]
GO
USE [master]
GO
ALTER DATABASE ' + @lib + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [rstestdb100] Script Date: 3/25/2019 2:58:04 PM ******/
DROP DATABASE ' + @lib + 
'GO
RESTORE DATABASE ' + @lib + ' FROM DISK = C:UsersRSantia' + @lib + '.bak WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
GO'

When I do larger dynamic SQL statements such as this I store the generated statement in an NVARCHAR(MAX) variable and then PRINT it before trying to execute it. You can then parse the generated statement to look for syntax errors which will lead you to where your dynamic SQL is falling down. Like this:

DECLARE @lib nvarchar(500) = 'pubs'

DECLARE @SQL NVARCHAR(MAX) = 

N'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = ' + '''' + @lib + '''' +
'GO
use ' + @lib + 
'GO
use [master]
GO
USE [master]
GO
ALTER DATABASE ' + @lib + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [rstestdb100] Script Date: 3/25/2019 2:58:04 PM ******/
DROP DATABASE ' + @lib + 
'GO
RESTORE DATABASE ' + @lib + ' FROM DISK = C:UsersRSantia' + @lib + '.bak WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
GO'

PRINT @SQL

Your generated SQL statement looks like this:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = 'pubs'GO
use pubsGO
use [master]
GO
USE [master]
GO
ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [rstestdb100] Script Date: 3/25/2019 2:58:04 PM ******/
DROP DATABASE pubsGO
RESTORE DATABASE pubs FROM DISK = C:UsersRSantiapubs.bak WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
GO

You can easily see there that your generated statements have some errors. Remember that just because your script has carriage returns doesn't mean that the generate SQL will also have them.

I think this is probably what you want:

DECLARE @lib nvarchar(500) = 'pubs'

DECLARE @SQL NVARCHAR(MAX) = 

N'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = ' + '''' + @lib + '''
GO
use ' + @lib + '
GO
use [master]
GO
USE [master]
GO
ALTER DATABASE ' + @lib + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [rstestdb100] Script Date: 3/25/2019 2:58:04 PM ******/
DROP DATABASE ' + @lib + 
'GO
RESTORE DATABASE ' + @lib + ' FROM DISK = C:UsersRSantia' + @lib + '.bak WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
GO'

EXEC sys.sp_executesql @SQL