Automate Database Restore




Automate Database Restore


On several occasions I have been required to restore a database from production to development or test. The requirements are that I must restore

1. The most recent full back up

2. The most recent differential backup

3. All transaction logs

4. The database must be left in STANDBY until the last transaction log is applied

This can be done through SSMS, but I wanted a way to do this using T-SQL and automate the process. The below script uses the two msdb tables backupset and backupmediafamily to get the fully qualified path of the backup file and ascertain the steps to take to insure the proper order and whether the recovery is set WITH STANDBY or WITH RECOVERY. This can easily be ported to a stored procedure that would accept an input parameter of the database name. The script can be downloaded here.

USE MASTER;
GO

DECLARE @file VARCHAR(150);
DECLARE @diffcount SMALLINT;
DECLARE @lastdate DATETIME

--Obtain the most recent full backup path
SET @file = (
SELECT TOP 1 m.physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND b.TYPE = 'D'    
    
AND is_copy_only = 0
ORDER BY backup_finish_date DESC
);

--Retrieve the most recent date fo the last full backup
SET @lastdate = (
SELECT TOP 1 backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND TYPE = 'D'
    
AND is_copy_only = 0
ORDER BY backup_finish_date DESC
);

--Query to see if a differential database backup exists in the current set
SET @diffcount =
(SELECT COUNT(*)
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND b.TYPE = 'I'
    
AND is_copy_only = 0
    
AND backup_finish_date > @lastdate);

--If a differential exists the full backup must be applied followed by the most recent diff
IF @diffcount > 0
BEGIN
PRINT
'Restorig most recent full backup before differential ' + @file;

RESTORE DATABASE Adventureworks2012
FROM DISK = @file
WITH NOUNLOAD,  
    
REPLACE,  
    
STANDBY = 'C:\Backup\standby.txt';

--Find the most recent differential
SET @file =
(SELECT TOP 1 m.physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND b.TYPE = 'I'
    
AND is_copy_only = 0
    
AND backup_finish_date > @lastdate
ORDER BY backup_finish_date DESC);
DECLARE @i INT;

PRINT 'Restoring the most recent differential backup ' + @file
RESTORE DATABASE Adventureworks2012
FROM DISK = @file
WITH NOUNLOAD,  
    
REPLACE,  
    
STANDBY = 'C:\Backup\standby.txt';

--If a differential exists then only T-Logs taken after the differential can be restored
--Reset the @lastdate variable to reflect after differential
SET @lastdate = (
SELECT TOP 1 backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND TYPE = 'I'
ORDER BY backup_finish_date DESC
);

END
ELSE
PRINT
'Restorig most recent full backup no differential exists ' + @file;

RESTORE DATABASE Adventureworks2012
FROM DISK = @file
WITH NOUNLOAD,  
    
REPLACE,  
    
STANDBY = 'C:\Backup\standby.txt';

--2014-08-25 09:20:35.000
--Obtain all of the T-Log backups in the order in which they were taken
SELECT m.physical_device_name,
      
backup_finish_date,
      
ROW_NUMBER() OVER(ORDER BY backup_finish_date DESC) AS row
INTO #tlog
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND TYPE = 'L'
AND backup_finish_date > @lastdate;

SELECT @i = (SELECT MAX(row) FROM #tlog)

/*Loop through all T-Log backups and restore each in the order in which they were taken
finally restore the final T-Log WITH RECOVERY*/
WHILE @i > 0
BEGIN
SELECT
@file = (SELECT physical_device_name FROM #tlog WHERE row = @i)
PRINT @file
IF @i = 1
BEGIN
PRINT
'Restoring log WITH RECOVERY ' + @file
RESTORE LOG Adventureworks2012
FROM DISK = @file;

DROP TABLE #tlog;
RETURN
END

ELSE
PRINT 'Restoring Log WITH STANDBY' + @file;

RESTORE LOG Adventureworks2012
FROM DISK = @file
WITH STANDBY = 'C:\Backup\standby.txt';
SET @i -= 1
END


Leave a Reply


Hit Counter provided by Sign Holders