Auto Generate SQL Server Restore Script from Backups in a Directory

Problem

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

Solution

The 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 "' + @backupPath + '"'
INSERT 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 > @lastFullBackup 
OPEN 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.

messages

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.

Next Steps

  • This is a pretty straight forward and simple approach.  As mentioned above it restores using the same name and also restores to the same file location.  Try making some modifications to restore it to another database name as well as restoring the files to a different location by incorporating the RESTORE FILELISTONLY command
  • This script will work on any server where the files exists and you can run a SQL Server query.  So you can copy the files from one server to another, run this script and then have your restore script ready to go.
  • Check out these other restore scripts:

Related Posts

8 Comments

  2. Hi Greg,

    I was used this script to restore db full backup to run automation job. While running this job I got the below error

    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
    SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online.

    Due to the security, we can’t be able to enable xp_cmdshell. So, can we run this script without using xp_cmdshell. Please guide me to resolve this and we have some request to restore multiple databases on daily basis.

  4. Sure you can do that. Just need to change the cursor to loop over multiple databases and just look for the full backup files.

  5. Hi Grig, Thanks for the script. Can we use this script for multiple databases to restore from backup location on regular basis. I have to restore only Fullbackup and we have to schedule it as Automated job to run every day.

  7. I couldn’t quite get this to work for me for various reasons, but it got me off to a good start. 1. Tweaked it to use Hallengren-style FULL, DIFF and LOG folders. 2 Changed the way it outputs the restore commands. 3. Changed it a little to filter out NULL entries from @fileList

    Copying it here in case someone else finds it useful.

    USE Master;
    GO
    SET NOCOUNT ON

    — Declare your variables
    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);
    DECLARE @restoreCommands TABLE (cmd NVARCHAR(MAX));

    — Set values required
    — Pay attention to the \ character at the end of @backupPath
    SET @dbName = ‘database-name’;
    SET @backupPath = ‘\\server\folder\’;

    — Get FULL backup filelist
    SET @cmd = ‘DIR /b “‘ + @backupPath + ‘FULL\”‘;
    INSERT INTO @fileList (backupFile)
    EXEC master.sys.xp_cmdshell @cmd;

    — Get DIFF backup filelist
    SET @cmd = ‘DIR /b “‘ + @backupPath + ‘DIFF\”‘;
    INSERT INTO @fileList (backupFile)
    EXEC master.sys.xp_cmdshell @cmd;

    — Get LOG backup filelist
    SET @cmd = ‘DIR /b “‘ + @backupPath + ‘LOG\”‘;
    INSERT INTO @fileList (backupFile)
    EXEC master.sys.xp_cmdshell @cmd;

    — Filter out NULL entries
    DELETE FROM @fileList WHERE backupFile IS NULL;

    — Find latest FULL backup
    SELECT @lastFullBackup = MAX(backupFile)
    FROM @fileList
    WHERE backupFile LIKE ‘%.bak’
    AND backupFile LIKE ‘%_FULL_%’
    AND backupFile LIKE ‘%’ + @dbName + ‘%’;

    — Build the FULL backup restore command
    IF @lastFullBackup IS NOT NULL
    BEGIN
    SET @cmd = ‘RESTORE DATABASE [‘ + @dbName + ‘] FROM DISK = ”’
    + @backupPath + ‘FULL\’ + @lastFullBackup + ”’ WITH NORECOVERY, REPLACE’;
    INSERT INTO @restoreCommands (cmd) VALUES (@cmd);
    END

    — Find latest DIFF backup
    SELECT @lastDiffBackup = MAX(backupFile)
    FROM @fileList
    WHERE backupFile LIKE ‘%.bak’
    AND backupFile LIKE ‘%’ + @dbName + ‘%’
    AND backupFile LIKE ‘%DIFF%’;

    — Build the DIFF backup restore command if there is a DIFF backup
    IF @lastDiffBackup IS NOT NULL
    BEGIN
    SET @cmd = ‘RESTORE DATABASE [‘ + @dbName + ‘] FROM DISK = ”’
    + @backupPath + ‘DIFF\’ + @lastDiffBackup + ”’ WITH NORECOVERY’;
    INSERT INTO @restoreCommands (cmd) VALUES (@cmd);
    END

    — Check for log backups more recent than the DIFF
    DECLARE backupFiles CURSOR FOR
    SELECT backupFile
    FROM @fileList
    WHERE backupFile LIKE ‘%.TRN’
    AND backupFile LIKE ‘%’ + @dbName + ‘%’
    AND (
    ( @lastDiffBackup IS NOT NULL AND RIGHT(LEFT(backupFile, LEN(backupFile) – 4), 15) > RIGHT(LEFT(@lastDiffBackup, LEN(@lastDiffBackup) – 4), 15) )
    OR
    ( @lastDiffBackup IS NULL AND RIGHT(LEFT(backupFile, LEN(backupFile) – 4), 15) > RIGHT(LEFT(@lastFullBackup, LEN(@lastFullBackup) – 4), 15) )
    );

    OPEN backupFiles;

    — Build the LOG restore commands
    FETCH NEXT FROM backupFiles INTO @backupFile;

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @cmd = ‘RESTORE LOG [‘ + @dbName + ‘] FROM DISK = ”’
    + @backupPath + ‘LOG\’ + @backupFile + ”’ WITH NORECOVERY’;
    INSERT INTO @restoreCommands (cmd) VALUES (@cmd);
    FETCH NEXT FROM backupFiles INTO @backupFile;
    END

    CLOSE backupFiles;
    DEALLOCATE backupFiles;

    — Now put the database in a working state
    — WITH RECOVERY
    SET @cmd = ‘RESTORE DATABASE [‘ + @dbName + ‘] WITH RECOVERY’;
    INSERT INTO @restoreCommands (cmd) VALUES (@cmd);

    — Display all of the restore commands for use
    SELECT cmd FROM @restoreCommands;

Leave a Reply

Your email address will not be published. Required fields are marked *