|
1 |
originally posted 2014-12-16 |
|
1 2 |
Note: This T-SQL uses SQLCMD variables which can be enabled in SQL Server Management Studio. Enable SQLCMD mode : Tools -> Options -> Query Execution -> Check the box "By default, open new queries in SQLCMD mode". |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
:setvar DB "FOO" begin try ALTER DATABASE $(DB) SET SINGLE_USER WITH ROLLBACK IMMEDIATE; BACKUP DATABASE [$(DB)] TO DISK = N'c:\temp\$(DB).bak' WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'$(DB)', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'$(DB)' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'$(DB)' ) if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''$(DB)'' not found.', 16, 1) end RESTORE VERIFYONLY FROM DISK = N'c:\temp\$(DB).bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND ALTER DATABASE $(DB) SET MULTI_USER; end try begin catch ALTER DATABASE $(DB) SET MULTI_USER; declare @err nvarchar(max) = @@ERROR; raiserror(@err,11,100) end catch |