Archive

Archive for the ‘Restores’ Category

SQL Saturday Columbus

It’s been a while since my last blog post.  Since then, I’ve been to PASS Summit, which was the best one yet.  I’ve presented at SQL Saturdays in Portland, Cleveland, and Iowa City. And, most excitingly, started a new job with BlueMetal.  I am in the middle of week 3 and am really excited about the new challenge.

I’ll be presenting on Using PowerShell to Automate Your Restore Strategy at SQL Saturday Columbus on July 16th.  It will be my first time in Columbus, Ohio and I am looking forward to seeing old friends, making new ones, and teaching and learning.  You can register and find more information at the link above.  If you are in the area, don’t miss the opportunity to get a free day of training while networking with your peers in the SQL Server community.

Upcoming Presentations

March 24, 2014 Leave a comment

I'm a presenting fool!

I’m a presenting fool!


I am continuing to work toward my goal of averaging a presentation a month this year. So far I’ve presented Designing a Restore Strategy at I-380 PASS, QCPASS, and IndyPASS. I’m presenting it at the Chicago SQL Server User Group and SQL Saturday 291 – Chicago in April. The presentation centers around my stored procedure sp_AutomateDBRestores, which I hope to have documented fully this week. You can download the code here. If you are interested in attending the user group, you can RSVP here. If you haven’t registered for SQL Saturday Chicago, I recommend it highly. It is a wonderful opportunity to get a day’s worth of free training and network with your peers.

If you are looking for additional training, take a look at the pre-conference sessions being offered by Adam Machanic (b|t) and David Klee (b|t). Adam is the author of sp_whoisactive, which I can’t recommend highly enough, and is presenting Better Performance Through Parallelism. David is an expert in virtualizing SQL Server, so it is fitting that he is presenting Virtualization for SQL Server DBAs.

Stored Procedure to Automate Restores – Mark III

February 16, 2014 Leave a comment

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

Stored Procedure to Automate Restores – Mark II

January 13, 2014 Leave a comment

As promised in my last post, I’ve updated the automated restore stored procedure to handle error. There are now custom error messages for a database with no full backup files and for databases that have more than one log file. Additionally, I have added code to handle differential backup files.

Once again, I hope you can put this to use.

UPDATE
I’ve modified the proc to change the physical data and log file names if the @sourcedbname and @restoredbname are different. The previous version worked if the @sourcedbname was contained in the physical file names.

USE master
GO
-- =============================================
-- Frank Gill - skreebydba.com - 2013-01-12
-- sp_AutomateDatabaseRestores
-- This script is provided with no guarantees
-- Please run in your test environment before deploying to production
-- The author takes no responsibility if your server descends into a smoking hole
-- 2013-01-13 - Updated procedure to append _restoredb to the physical data and 
-- log file if @sourcedbname and @restoredbname are different
-- =============================================
  
-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'sp_AutomateDatabaseRestores'
)
   DROP PROCEDURE dbo.sp_AutomateDatabaseRestores
GO
  
CREATE PROCEDURE dbo.sp_AutomateDatabaseRestores
    @sourcedbname SYSNAME, 
    @restoredbname SYSNAME,
    @droprestoredb TINYINT = 1,
    @noexec TINYINT = 0
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
(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)
  
--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
--If restoring to a different database name, append _restore onto the end of the physical file names
IF @sourcedbname <> @restoredbname
BEGIN

	SELECT @logfile = LogicalName, @physlogfile = REPLACE(PhysicalName,'.ldf','_restore.ldf')
	FROM #databasefiles
	WHERE filetype = 1
	
END
ELSE
BEGIN

	SELECT @logfile = LogicalName, @physlogfile = PhysicalName
	FROM #databasefiles
	WHERE filetype = 1

END
  
--If the database contains one data file, build the MOVE clause for a single file
IF @datafilecount = 1
BEGIN

	IF @sourcedbname <> @restoredbname
	BEGIN
  
		SELECT @datafile = LogicalName, @physdatafile = REPLACE(PhysicalName,'.mdf','_restore.mdf')
		FROM #databasefiles
		WHERE filetype = 0
		
	END
	ELSE
	BEGIN
	
		SELECT @datafile = LogicalName, @physdatafile = PhysicalName
		FROM #databasefiles
		WHERE filetype = 0
	
	END      
    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
          
        IF @sourcedbname <> @restoredbname
		BEGIN
  
		    SET @physdatafile = (SELECT TOP (1) REPLACE(PhysicalName,'.mdf','_restore.mdf') FROM #databasefiles WHERE filetype = 0)
		    
		END
		ELSE
		BEGIN
		
			SET @physdatafile = (SELECT TOP (1) PhysicalName FROM #databasefiles WHERE filetype = 0)
		
		END
        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
  
--Get the most recent full backup file for the source database and insert it into the #backupfiles temp table
INSERT INTO #backupfiles
SELECT
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 =
    (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
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 >
    (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
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 >
    (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
 
--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
          
            SET @sqlstr = @sqlstr + ' WITH RECOVERY'
          
        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
  
END TRY
BEGIN CATCH
 
    SELECT
    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: Restores, SQL Server, T-SQL Tags: ,

Stored Procedure to Automate Database Restores

January 9, 2014 Leave a comment

It’s been awhile since I’ve posted here, and I’ve already fallen behind on my New Year’s resolution to post once a week. I will do what I can to keep up over the next 51 weeks.

For the past few months, I’ve been working to standardize the backup processes at my current job. Recently, I’ve needed to develop a process to restore a read-only copy of a production database using the most recent full backup file and any subsequent log backup files. The procedure below does that. It will work for database backups containing multiple backup files and databases containing multiple data files. However, if you have more than one log file in your database, you are out of luck. See Paul Randal’s post Multiple Log Files and Why They’re Bad for an excellent explanation of why you shouldn’t do that. I will add error handling to deal with this scenario this weekend.

I hope you can put this to use. Let me know if you have questions.

USE YourDatabaseName
GO
-- =============================================
-- Frank Gill - skreebydba.com
-- sp_AutomateDatabaseRestores
-- This script is provided with no guarantees
-- Please run in you test environment before deploying to production
-- The author takes no responsibility if your server descends into a smoking hole
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'sp_AutomateDatabaseRestores' 
)
   DROP PROCEDURE dbo.sp_AutomateDatabaseRestores
GO

CREATE PROCEDURE dbo.sp_AutomateDatabaseRestores
	@sourcedbname SYSNAME, 
	@restoredbname SYSNAME,
	@droprestoredb TINYINT = 1,
	@noexec TINYINT = 0
AS
	
SET NOCOUNT ON

--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
(physicalfilename VARCHAR(2000)
,recoverymodel 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 @datafile VARCHAR(2000)
DECLARE @moveparm VARCHAR(2000)
DECLARE @fromstatement VARCHAR(2000)
DECLARE @logfile SYSNAME
DECLARE @physlogfile VARCHAR(2000)
DECLARE @physdatafile VARCHAR(2000)

--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

--Get the logical and physical log file names
SELECT @logfile = LogicalName, @physlogfile = REPLACE(PhysicalName,@sourcedbname,@restoredbname)
FROM #databasefiles
WHERE filetype = 1

--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,@sourcedbname,@restoredbname) 
	FROM #databasefiles
	WHERE filetype = 0
	
	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,@sourcedbname,@restoredbname) 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

--Get the most recent full backup file for the source database and insert it into the #backupfiles temp table
INSERT INTO #backupfiles
SELECT
b.physical_device_name, 'FULL' AS [RecoveryModel]
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 =
	(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 REVERSE(SUBSTRING(REVERSE(d.physical_device_name),1,3)) = 'BAK')
order by a.backup_finish_date ASC

--Get all log backupfiles run after the latest full backup and insert it into the #backupfiles temp table
INSERT INTO #backupfiles
SELECT
b.physical_device_name, 'LOG'  AS [RecoveryModel]
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 REVERSE(SUBSTRING(REVERSE(b.physical_device_name),1,3)) = 'TRN'
AND backup_finish_date >
	(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 REVERSE(SUBSTRING(REVERSE(d.physical_device_name),1,3)) = 'BAK')
order by a.backup_finish_date ASC

--Get counts of all backup files, full backup files, and log backup files for use in loop processing below
SELECT @filecount = COUNT(*) FROM #backupfiles
SELECT @fullfilecount = COUNT(*) FROM #backupfiles WHERE recoverymodel = 'FULL'
SELECT @logbackupcount = COUNT(*) FROM #backupfiles WHERE recoverymodel = 'LOG'

--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 recoverymodel 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
		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 recoverymodel = 'FULL')
				SET @fromstatement = @fromstatement + 'DISK = ' + '''' + @backupfile + '''' + ','
				DELETE TOP (1) FROM #backupfiles WHERE recoverymodel = '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
	--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
		
			SET @sqlstr = @sqlstr + ' WITH RECOVERY'
		
		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


GO
Categories: Restores, SQL Server, T-SQL Tags: ,