Home > Administration, Restores, SQL Server, T-SQL > Stored Procedure to Automate Restores – Mark III

Stored Procedure to Automate Restores – Mark III

February 16, 2014 Leave a comment Go to comments

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 Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

%d bloggers like this: