Auto generate SQL Server database restore scripts (Part 2)

-------------------------------------------------------------------------------------------------------------------------------------------------------

Model:2  Auto generate SQL Server restore script from backup files in a directory

ProblemOne of the ongoing challenges of a DBA is to backup and restore databases.  Backups are done on an automated schedule, but restores can take on many different versions, you may need to restore a production database, restore a development or test database or just create another copy of the database somewhere else.  There are several ways of automating the restore process and creating a script, but this approach shows a way this can be done by just reading the contents of a directory for the backup files that exist.

SolutionThe following is one simple approach of reading the contents of a directory and creating the restore commands that need to be issued to restore the database.  This script will work for full, differential and transaction log backups.

Before we get started the script below assumes the following:

1. The restored database will have the same name as the backed up database
2. The restored database will be restored in the same location as the backed up database
3. The files have the following naming format
   • dbName_YYYYMMDDHHMM.xxx
4. File extensions are as follows
   • Full backup - BAK
   • Differential backup - DIF
   • Transaction log backup - TRN
5. XP_CMDSHELL is enabled
6. There are no missing transaction logs that may break the restore chain

So let's say we are creating our backups on the following schedule:

• Full backups at midnight
• Differential backups every 3 hours starting at 3:15am
• Log backups every 30 minutes starting at 1am

At 9am we would have the following backup files created for September 10, 2008 for the "Customer" database following the rules above.

• Customer_200809100000.BAK
• Customer_200809100100.TRN
• Customer_200809100130.TRN
• Customer_200809100200.TRN
• Customer_200809100230.TRN
• Customer_200809100300.TRN
• Customer_200809100315.DIF
• Customer_200809100330.TRN
• Customer_200809100400.TRN
• Customer_200809100430.TRN
• Customer_200809100500.TRN
• Customer_200809100530.TRN
• Customer_200809100600.TRN
• Customer_200809100615.DIF
• Customer_200809100630.TRN
• Customer_200809100700.TRN
• Customer_200809100730.TRN
• Customer_200809100800.TRN
• Customer_200809100830.TRN
• Customer_200809100900.TRN

If we wanted to do a restore of the latest Full, Differential and Transaction Log backups to 9am we would need to restore the following files:

• Customer_200809100000.BAK
• Customer_200809100615.DIF
• Customer_200809100630.TRN
• Customer_200809100700.TRN
• Customer_200809100730.TRN
• Customer_200809100800.TRN
• Customer_200809100830.TRN
• Customer_200809100900.TRN

The script below will read through the directory and create the restore script for us.  The only two parameters that would need to change are the @dbName and the @backupPath.

USE Master; GO SET NOCOUNT ON-- 1 - Variable declaration DECLARE @dbName sysname DECLARE @backupPath NVARCHAR(500) DECLARE @cmd NVARCHAR(500) DECLARE @fileList TABLE (backupFile NVARCHAR(255)) DECLARE @lastFullBackup NVARCHAR(500) DECLARE @lastDiffBackup NVARCHAR(500) DECLARE @backupFile NVARCHAR(500)-- 2 - Initialize variables SET @dbName = 'Customer' SET @backupPath = 'D:\SQLBackups\'-- 3 - get list of files SET @cmd = 'DIR /b ' + @backupPathINSERT INTO @fileList(backupFile) EXEC master.sys.xp_cmdshell @cmd-- 4 - Find latest full backup SELECT @lastFullBackup = MAX(backupFile)  FROM @fileList  WHERE backupFile LIKE '%.BAK'    AND backupFile LIKE @dbName + '%'SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''        + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE' PRINT @cmd-- 4 - Find latest diff backup SELECT @lastDiffBackup = MAX(backupFile)  FROM @fileList  WHERE backupFile LIKE '%.DIF'    AND backupFile LIKE @dbName + '%'    AND backupFile > @lastFullBackup-- check to make sure there is a diff backup IF @lastDiffBackup IS NOT NULL BEGIN    SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''        + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'    PRINT @cmd    SET @lastFullBackup = @lastDiffBackup END-- 5 - check for log backups DECLARE backupFiles CURSOR FOR    SELECT backupFile    FROM @fileList    WHERE backupFile LIKE '%.TRN'    AND backupFile LIKE @dbName + '%'    AND backupFile > @lastFullBackupOPEN backupFiles -- Loop through all the files for the database  FETCH NEXT FROM backupFiles INTO @backupFile WHILE @@FETCH_STATUS = 0 BEGIN    SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''        + @backupPath + @backupFile + ''' WITH NORECOVERY'    PRINT @cmd    FETCH NEXT FROM backupFiles INTO @backupFile  END CLOSE backupFiles DEALLOCATE backupFiles -- 6 - put database in a useable state SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY' PRINT @cmd

If you run the above code in a query window, assuming the listed files above existed, you will get the following output.  At this point you can copy and paste this code into another query window and run the query to do the actual restore.

As you can see it does a Full restore, the latest Differential restore and all Transaction Logs after that.  The script also does a WITH RECOVERY at the end to put the database in a useable state.

-------------------------------------------------------------------------------------------------------------------------------------------------------
Model:3
Problem
When a failure occurs you need to act quickly to possibly restore your database.  When you are performing both full and transaction log backups there are multiple files that will need to be restored and therefore your restore script could get quite long and tedious to write.  When using Enterprise Manager or SQL Server Management Studio the GUI gives you the list of files that should be restored, but what if you need to do this manually or you would rather have a script to perform the restore process instead of using the GUI.  How can you easily generate the restore script?

Solution
For every backup that occurs in SQL Server an entry is made into the system tables that reside in the MSDB database.   This includes both native SQL Server backups as well as backups that occur using third party tools.  These tables that hold this backup information are:

• backupfile -- contains one row for each data file or log file backed up
• backupmediafamily -- contains one row for each media family
• backupmediaset -- contains one row for each backup media set
• backupset -- contains one row for each backup set

By querying these tables you can determine when the last backups occurred, what type of backups occurred and also where the files were physically written.  These tables on their own are not all that helpful, but when you combine the contents from each of these tables you can piece together your entire backup string and create an easy to use restore script.

The following is a simple script that queries the backupset and backupmediafamily tables.  This script assumes that the backup files are being written to disk and not directly to tape, but this script can be modified to include these checks as well.  The idea behind this is that you will want to always restore your latest backup set, which is probably the set of files you still have on disk.  In most cases when a failure occurs or someone accidentally deleted important data you will probably only need to go back to the latest full backup and the appropriate transaction logs.

Here is the script, the only parameter that needs to be set is the database that you want to retrieve backup information for.  The process will then find the latest Full backup and all transaction log backups that have occurred after this full backup.  The last step is to create a WITH RECOVERY command that puts the database online and makes it accesible.

DECLARE @databaseName sysname DECLARE @backupStartDate datetime DECLARE @backup_set_id_start INT DECLARE @backup_set_id_end INT-- set database to be used SET @databaseName = 'enterDatabaseNameHere' SELECT @backup_set_id_start = MAX(backup_set_id)  FROM  msdb.dbo.backupset WHERE database_name = @databaseName AND type = 'D'SELECT @backup_set_id_end = MIN(backup_set_id)  FROM  msdb.dbo.backupset WHERE database_name = @databaseName AND type = 'D' AND backup_set_id > @backup_set_id_startIF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999 SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''                + mf.physical_device_name + ''' WITH NORECOVERY' FROM    msdb.dbo.backupset b,            msdb.dbo.backupmediafamily mf WHERE    b.media_set_id = mf.media_set_id            AND b.database_name = @databaseName           AND b.backup_set_id = @backup_set_id_start UNION SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''                + mf.physical_device_name + ''' WITH NORECOVERY' FROM    msdb.dbo.backupset b,            msdb.dbo.backupmediafamily mf WHERE    b.media_set_id = mf.media_set_id            AND b.database_name = @databaseName           AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end           AND b.type = 'L' UNION SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY' ORDER BY backup_set_id

Auto generate SQL Server All databases restore scripts

DECLARE @databaseName sysname  DECLARE @backupStartDate datetime  DECLARE @backup_set_id_start INT  DECLARE @backup_set_id_end INT   DECLARE @dbname varchar(50) DECLARE C CURSOR FOR SELECT name FROM master..sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb','tempdb') OPEN C FETCH NEXT FROM C INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN  ---PRINT 'EXECUTE YOUR PROCEDURE HERE WITH THE db NAME ' + @dbname  -- set database to be used  SET @databaseName = '@dbname'    FETCH NEXT FROM C INTO @dbname  SELECT @backup_set_id_start = MAX(backup_set_id)   FROM  msdb.dbo.backupset   WHERE database_name = @dbname AND type = 'D'   SELECT @backup_set_id_end = MIN(backup_set_id)   FROM  msdb.dbo.backupset   WHERE database_name = @dbname AND type = 'D'  AND backup_set_id > @backup_set_id_start  IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999  SELECT backup_set_id, 'RESTORE DATABASE ' + @dbname + ' FROM DISK = '''                  + mf.physical_device_name + ''' WITH NORECOVERY' AS Query FROM    msdb.dbo.backupset b,             msdb.dbo.backupmediafamily mf  WHERE    b.media_set_id = mf.media_set_id             AND b.database_name = @dbname            AND b.backup_set_id = @backup_set_id_start  UNION  SELECT backup_set_id, 'RESTORE LOG ' + @dbname + ' FROM DISK = '''                  + mf.physical_device_name + ''' WITH NORECOVERY'  FROM    msdb.dbo.backupset b,             msdb.dbo.backupmediafamily mf  WHERE    b.media_set_id = mf.media_set_id             AND b.database_name = @dbname            AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end            AND b.type = 'L'        UNION  SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @dbname + ' WITH NORECOVERY' AS Query ORDER BY backup_set_id  END CLOSE C DEALLOCATE C

When the above is run for database "DButil" the following result set is generated.  This generated 4 lines of code for this database.  The T-SQL code in column (no column name) could then be copied and pasted into a query window to be executed to do the restore or it could be modified to only include the transaction log files you need, especially if you needed to do a point in time recovery. 

Line 1 is the full backup, lines 2 and 3 are the transaction log backups and line 4 is the WITH RECOVERY option which takes the database out of the loading state and makes the database accessible.

SummaryThis is a very basic process of finding the latest full backup and all of the transaction log backups.  It does not take into consideration additional backups that are written to the same file or differential backups.  There are several tweaks that could be made to further enhance this script, but hopefully this gives you a starting point to automate your restore scripts instead of having to rely on the GUI to provide this data or having to manually type each command for every single backup.