Stored Procedure to Automate Restores – Mark II
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
SQL Server, T-SQL
Comments (0)
Trackbacks (0)
Leave a comment
Trackback
