Home
> Administration, Restores, SQL Server, T-SQL > Stored Procedure to Automate Restores – Mark III
Stored Procedure to Automate Restores – Mark III
sp_AutomateDatabaseRestores, now with STOPAT! I've added parameter @stopat to allow generation and execution of a RESTORE statement WITH STOPAT. Check the Presentation Slides and Scripts link for a script to demonstrate this functionality.
USE [master] GO /****** Object: StoredProcedure [dbo].[sp_automateDBRestores] Script Date: 2/16/2014 8:48:07 PM ******/ IF EXISTS(SELECT 1 FROM master.sys.objects WHERE name = 'sp_automateDBRestores') BEGIN DROP PROCEDURE [dbo].[sp_automateDBRestores] END GO /****** Object: StoredProcedure [dbo].[sp_automateDBRestores] Script Date: 2/16/2014 8:48:07 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_automateDBRestores] @sourcedbname SYSNAME, @restoredbname SYSNAME, @droprestoredb TINYINT = 1, @noexec TINYINT = 0, @stopat DATETIME = NULL AS SET NOCOUNT ON BEGIN TRY --Drop temp tables if they exist IF OBJECT_ID('tempdb..#backupfiles') IS NOT NULL BEGIN DROP TABLE #backupfiles END IF OBJECT_ID('tempdb..#databasefiles') IS NOT NULL BEGIN DROP TABLE #databasefiles END --Create temp tables CREATE TABLE #backupfiles (backupstartdate DATETIME ,backupfinishdate DATETIME ,physicalfilename VARCHAR(2000) ,backuptype CHAR(4)) CREATE TABLE #databasefiles (DatabaseName SYSNAME ,LogicalName SYSNAME ,PhysicalName VARCHAR(2000) ,filetype TINYINT) --Declare local variables DECLARE @filecount SMALLINT DECLARE @fullfilecount SMALLINT DECLARE @loopcount SMALLINT DECLARE @datafileloopcount SMALLINT DECLARE @backuploopcount SMALLINT DECLARE @backupfile VARCHAR(2000) DECLARE @sqlstr VARCHAR(MAX) DECLARE @backupfiletype CHAR(4) DECLARE @datafilecount SMALLINT DECLARE @logfilecount SMALLINT DECLARE @logbackupcount SMALLINT DECLARE @diffbackupcount SMALLINT DECLARE @datafile VARCHAR(2000) DECLARE @moveparm VARCHAR(2000) DECLARE @fromstatement VARCHAR(2000) DECLARE @logfile SYSNAME DECLARE @physlogfile VARCHAR(2000) DECLARE @physdatafile VARCHAR(2000) DECLARE @fullbackupdate DATETIME DECLARE @logbackupdate DATETIME DECLARE @logbackuplimit DATETIME DECLARE @errormessage VARCHAR(1000) --Drop the restored database if it exists and @droprestoredb = 1 IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @restoredbname) AND @droprestoredb = 1 BEGIN SET @sqlstr = 'DROP DATABASE ' + @restoredbname EXEC (@sqlstr) END --Initialize variables SET @moveparm = ' WITH' SET @fromstatement = ' FROM ' SET @loopcount = 1 SET @datafileloopcount = 1 SET @backuploopcount = 1 --Build dynamic SQL to get a list of data and log files for the source database SET @sqlstr = 'INSERT INTO #databasefiles SELECT ' + '''' + @sourcedbname + '''' + ' AS DatabaseName, name, physical_name, type FROM ' + @sourcedbname + '.sys.database_files' --Execute dynamic SQL to return data and log files for the source database EXEC (@sqlstr) --Get counts of data files and log files SELECT @datafilecount = COUNT(*) FROM #databasefiles WHERE filetype = 0 SELECT @logfilecount = COUNT(*) FROM #databasefiles WHERE filetype = 1 IF @logfilecount > 1 BEGIN RAISERROR ('You have more than one log file, which is a really bad idea. No backup for you. See this link for more information. http://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/', -- Message text. 16, -- Severity. 1 -- State. ); END --Get the logical and physical log file names SELECT @logfile = LogicalName, @physlogfile = REPLACE(PhysicalName,'.ldf','_testrestore.ldf') FROM #databasefiles WHERE filetype = 1 SELECT @physlogfile AS PhysLogFile --If the database contains one data file, build the MOVE clause for a single file IF @datafilecount = 1 BEGIN SELECT @datafile = LogicalName, @physdatafile = REPLACE(PhysicalName,'.mdf','_testrestore.mdf') FROM #databasefiles WHERE filetype = 0 SELECT @physdatafile AS PhysDataFile SET @moveparm = @moveparm + ' MOVE ' + '''' + @datafile + '''' + ' TO ' + '''' + @physdatafile + '''' + ',' + ' MOVE ' + '''' + @logfile + '''' + ' TO ' + '''' + @physlogfile + '''' + ', NORECOVERY' END ELSE --If the database contains more than one data file, loop through the list of files and build a multi-file MOVE statement IF @datafilecount > 1 BEGIN --SELECT @datafileloopcount, @datafilecount WHILE @datafileloopcount <= @datafilecount BEGIN --SELECT TOP (1) LogicalName, REPLACE(PhysicalName,@sourcedbname,@restoredbname) FROM #databasefiles WHERE filetype = 0 SET @physdatafile = (SELECT TOP (1) REPLACE(PhysicalName,'.mdf','_testrestore.mdf') FROM #databasefiles WHERE filetype = 0) SET @datafile = (SELECT TOP (1) LogicalName FROM #databasefiles WHERE filetype = 0) SET @moveparm = @moveparm + ' MOVE ' + '''' + @datafile + '''' + ' TO ' + '''' + @physdatafile + '''' + ',' DELETE TOP (1) FROM #databasefiles WHERE filetype = 0 SET @datafileloopcount += 1 END --Append the MOVE command for the log file to the MOVE clause and set it to NORECOVERY SET @moveparm = @moveparm + ' MOVE ' + '''' + @logfile + '''' + ' TO ' + '''' + @physlogfile + '''' + ', NORECOVERY' END IF @droprestoredb = 0 BEGIN SET @moveparm = @moveparm + ', REPLACE' END SELECT @fullbackupdate = MAX(c.backup_finish_date) FROM msdb.dbo.backupset c INNER JOIN msdb.dbo.backupmediafamily d ON c.media_set_id = d.media_set_id WHERE c.database_name = @sourcedbname AND c.type = 'D' SELECT @logbackupdate = MAX(c.backup_start_date) FROM msdb.dbo.backupset c INNER JOIN msdb.dbo.backupmediafamily d ON c.media_set_id = d.media_set_id WHERE c.database_name = @sourcedbname AND c.type IN ('D', 'I') --Get the most recent full backup file for the source database and insert it into the #backupfiles temp table INSERT INTO #backupfiles SELECT a.backup_start_date, a.backup_finish_date, b.physical_device_name, 'FULL' AS [backuptype] from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id where a.database_name = @sourcedbname AND backup_finish_date = @fullbackupdate --(SELECT MAX(c.backup_finish_date) -- FROM msdb.dbo.backupset c -- INNER JOIN msdb.dbo.backupmediafamily d -- ON c.media_set_id = d.media_set_id -- WHERE c.database_name = @sourcedbname -- AND c.type = 'D') order by a.backup_finish_date ASC --Get a list of differential backup files older than the most recent full backup INSERT INTO #backupfiles SELECT TOP 1 a.backup_start_date, a.backup_finish_date, b.physical_device_name, 'DIFF' AS [backuptype] from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id where a.database_name = @sourcedbname AND a.type = 'I' AND backup_finish_date > @fullbackupdate --(SELECT MAX(c.backup_finish_date) -- FROM msdb.dbo.backupset c -- INNER JOIN msdb.dbo.backupmediafamily d -- ON c.media_set_id = d.media_set_id -- WHERE c.database_name = @sourcedbname -- AND c.type = 'D') order by a.backup_finish_date DESC --Get all log backupfiles run after the latest full or differential backup and insert it into the #backupfiles temp table INSERT INTO #backupfiles SELECT a.backup_start_date, a.backup_finish_date, b.physical_device_name,'LOG' AS [backuptype] from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id where a.database_name = @sourcedbname AND a.type = 'L' AND backup_finish_date > @logbackupdate --(SELECT MAX(c.backup_start_date) -- FROM msdb.dbo.backupset c -- INNER JOIN msdb.dbo.backupmediafamily d -- ON c.media_set_id = d.media_set_id -- WHERE c.database_name = @sourcedbname -- AND c.type IN ('D', 'I')) order by a.backup_finish_date ASC IF @stopat < @fullbackupdate BEGIN SET @errormessage = 'The @stopat values provided ' + CAST(@stopat AS VARCHAR(26)) + 'is older than the most recent full backup date of ' + CAST(@fullbackupdate AS VARCHAR(26)) + '. Please check the value and rerun.' RAISERROR (@errormessage, -- Message text. 16, -- Severity. 1 -- State. ); END IF @stopat IS NOT NULL BEGIN SELECT TOP 1 @logbackuplimit = backupfinishdate FROM #backupfiles WHERE backupfinishdate > @stopat DELETE FROM #backupfiles WHERE backupfinishdate > @logbackuplimit SELECT * FROM #backupfiles END --Get counts of all backup files, full backup files, differential backup files, and log backup files for use in loop processing below SELECT @filecount = COUNT(*) FROM #backupfiles SELECT @fullfilecount = COUNT(*) FROM #backupfiles WHERE backuptype = 'FULL' SELECT @diffbackupcount = COUNT(*) FROM #backupfiles WHERE backuptype = 'DIFF' SELECT @logbackupcount = COUNT(*) FROM #backupfiles WHERE backuptype = 'LOG' IF @fullfilecount = 0 BEGIN RAISERROR ('There are no full backup files for your database.', -- Message text. 16, -- Severity. 1 -- State. ); END --While backup files exists, loop through them to generate the restore statement WHILE @loopcount <= @filecount BEGIN --Get the top backup file from the temp table SET @backupfiletype = (SELECT TOP 1 backuptype FROM #backupfiles) --If it is a full backup file, build the restore statement IF @backupfiletype = 'FULL' BEGIN --If there are no log backups to apply, replace NORECOVERY with RECOVERY in the MOVE clause IF (@logbackupcount = 0 AND @diffbackupcount = 0) BEGIN SET @moveparm = REPLACE(@moveparm, 'NORECOVERY', 'RECOVERY') END --If there is a single full backup file, build the RESTORE statement with that file IF @fullfilecount = 1 BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' + @moveparm --PRINT @sqlstr DELETE TOP (1) FROM #backupfiles END --If there is more than one full backup file, build the RESTORE command for all full backup files ELSE IF @fullfilecount > 1 BEGIN WHILE @backuploopcount <= @fullfilecount BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles WHERE backuptype = 'FULL') SET @fromstatement = @fromstatement + 'DISK = ' + '''' + @backupfile + '''' + ',' DELETE TOP (1) FROM #backupfiles WHERE backuptype = 'FULL' SET @backuploopcount += 1 END --Truncate the trailing comma from the FROM clause SET @fromstatement = SUBSTRING(@fromstatement,1,(LEN(@fromstatement) - 1)) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + @fromstatement + @moveparm SET @loopcount += 1 --PRINT @sqlstr END END --One RESTORE datement will be built for the most recent differential backup ELSE IF @backupfiletype = 'DIFF' BEGIN IF @diffbackupcount = 1 AND @logbackupcount = 0 BEGIN SET @moveparm = REPLACE(@moveparm, 'NORECOVERY', 'RECOVERY') END SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' + @moveparm --PRINT @sqlstr DELETE TOP (1) FROM #backupfiles END --If it is a log backup file, build a RESTORE log statement --One RESTORE datement will be built for each log backup file ELSE IF @backupfiletype = 'LOG' BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE LOG ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' --If there is another log backup file, set the RESTORE to WITH NORECOVERY IF (SELECT COUNT(*) FROM #backupfiles) > 1 BEGIN SET @sqlstr = @sqlstr + ' WITH NORECOVERY' END --If there is not another log backup file, set the RESTORE to WITH RECOVERY ELSE BEGIN IF @stopat IS NOT NULL BEGIN --CAST(@stopat AS VARCHAR(26)) SET @sqlstr = @sqlstr + ' WITH NORECOVERY, STOPAT = ' + '''' + CONVERT(VARCHAR(30),@stopat,121) + '''' END ELSE BEGIN SET @sqlstr = @sqlstr + ' WITH RECOVERY' END END DELETE TOP (1) FROM #backupfiles END --If @noexec = 1, PRINT @sqlstr IF @noexec = 1 BEGIN PRINT @sqlstr END --If @noexec <> 1, EXEC @sqlstr ELSE BEGIN EXEC (@sqlstr) END --Increment the loop count and continue processing backup files SET @loopcount += 1 END IF @stopat IS NOT NULL BEGIN SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' WITH RECOVERY' IF @noexec = 1 BEGIN PRINT @sqlstr END ELSE BEGIN EXEC(@sqlstr) END END END TRY BEGIN CATCH SELECT @sourcedbname AS DatabaseName, ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END CATCH GO
Categories: Administration, Restores, SQL Server, T-SQL
SQL Server, T-SQL
Comments (0)
Trackbacks (0)
Leave a comment
Trackback