Auto generate SQL Server restore scripts after each backup completes

By:   |   Comments (20)   |   Related: 1 | 2 | 3 | 4 | > Restore


Problem

There are a lot of scripts out there that will allow you to use the msdb to auto-generate restore scripts, but what if you cannot access the msdb database. What is the easiest way to make sure I have the proper restore scripts on hand without relying on the msdb database?

Solution

The best way to make sure you have the proper restore scripts when the time comes is to make use of PRINT statements and OUTPUT files. Every time your backup job runs, whether it is a Full or Log, place a step at the end of the job to read the new data from the msdb database and write it to an output file. I generally place the restore script in the root of my backup folders and make sure the script is backed up as well.

First, create a new SQL Agent Job step called "Generate Restore Script" at the end of your backup job and insert the script listed below.

generate restore script
SET NOCOUNT ON 
DECLARE @databaseName sysname 

CREATE TABLE #TmpCommands 
(ID INT IDENTITY(1,1), 
Cmd VARCHAR(8000) ) 

DECLARE dbnames_cursor CURSOR 
FOR 
   SELECT name  
   FROM master..sysdatabases 
   WHERE name NOT IN ( 'model','tempdb', 'pubs','northwind') 
   AND (status & 32) =0         -- Do not include loading 
   AND (status & 64) =0         -- Do not include loading 
   AND (status & 128) =0        -- Do not include recovering 
   AND (status & 256) =0        -- Do not include not recovered 
   AND (status & 512) =0        -- Do not include offline 
   AND (status & 32768) =0      -- Do not include emergency 
   AND (status & 1073741824) =0 -- Do not include cleanly shutdown 
  
OPEN dbnames_cursor 
  
FETCH NEXT FROM dbnames_cursor INTO @databaseName 
WHILE (@@FETCH_STATUS <> -1) 
   BEGIN 
   IF (@@FETCH_STATUS <> -2) 
   BEGIN 
      INSERT INTO #TmpCommands(Cmd) 
      VALUES ('----------------Script to Restore the ' + @databaseName + ' Database--------------') 

      DECLARE @backupStartDate datetime  
      DECLARE @backup_set_id_start INT  
      DECLARE @backup_set_id_end INT 

      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_start 

      IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999 

      INSERT INTO #TmpCommands(Cmd) 
      SELECT Cmd FROM( 
      SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''  
      + mf.physical_device_name + ''' WITH NORECOVERY --' Cmd 
      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 FILE = ' + CAST(position AS VARCHAR(10)) + ', NORECOVERY --' Cmd 
      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 --' Cmd 
      ) A 
      ORDER BY backup_set_id 
   END 

   FETCH NEXT FROM dbnames_cursor INTO @DatabaseName  

END 

CLOSE dbnames_cursor 
DEALLOCATE dbnames_cursor 

DECLARE @PrintCommand VARCHAR(8000) 

DECLARE Print_cursor CURSOR 
FOR  
   SELECT Cmd FROM #TmpCommands 
   ORDER BY ID 
  
OPEN Print_cursor 
  
FETCH NEXT FROM Print_cursor INTO @PrintCommand 
WHILE (@@FETCH_STATUS <> -1) 
   BEGIN 
   IF (@@FETCH_STATUS <> -2) 
      BEGIN  
         PRINT @PrintCommand 
   END  

   FETCH NEXT FROM Print_cursor INTO @PrintCommand  
END 

CLOSE Print_cursor 
DEALLOCATE Print_cursor 
  
DROP TABLE #TmpCommands 

Next, go to the Advanced Tab of the Job Step and enter a destination for the Output File as shown below.

job step properties

Here is sample of what the output looks like:

restore database

So if I want to restore the "test2" database, I would just use the three lines highlighted above.

That's all there is to it. The next time your backup runs it will generate a script in the output file that can be used to restore any of the databases for the instance. Since the script runs when the backup runs, you will always have a current restore script on hand.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, February 28, 2017 - 4:17:04 AM - Murali Krishna Back To Top (46757)

 

we have differential backups also in my environment , can someone kindly help me the script for differential backups 


Monday, June 1, 2015 - 2:25:25 PM - Greg Robidoux Back To Top (37349)

Hi Bill,

you could also take a look at this tip: http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

This uses a list of backup files in a folder, but it also uses differential backups as well.  If you need to pull from the system tables, you might be able to tweak it to meet your needs.

-Greg


Monday, June 1, 2015 - 1:30:16 PM - Bill Hughes Back To Top (37348)

How could this be modified to include Differential backups? One of my servers has weekly full backups, daily differential backups and hourly transaction log backups. The script gives me the full backup and every transaction log since then (even though these are deleted daily) but not the differential.


Wednesday, July 23, 2014 - 7:52:54 AM - hari prasad Back To Top (32838)

Hi ,

 

I m very new to MSSQL.

Requirement :copy the database backup files from prod to PPE and restore .for this requirement i need a automate script.

Kindly help me in this regard.

 

Thanks,

Hari


Thursday, June 28, 2012 - 9:37:56 AM - Crudeli Eric Back To Top (18251)

Hello,

I tried the last script but I have a question. I made one day of the week a backup full and the rest of the time backup diff. In this case, after running your script I got only Line for restore backup full and restore backup transaction files. Why I didn't get a line for restore backup diff ?

Thanks for your help,

Eric

 

 


Thursday, May 24, 2012 - 9:38:57 PM - Max Back To Top (17651)

This script is great!  But it doesn't deal well with databases that contain spaces in the name.  To fix this, I modified the following lines:

Lines 49 and 50 from:

SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
 + mf.physical_device_name + ''' WITH NORECOVERY --' Cmd

to

SELECT backup_set_id, 'RESTORE DATABASE "' + @databaseName + '" FROM DISK = '''
 + mf.physical_device_name + ''' WITH NORECOVERY, REPLACE --' Cmd

 

(note I also introduced a WITH REPLACE here to ensure the existing DB was overwritten instead of the script erroring out if the DB already exists)

 

Line 57 from:

SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''

to

SELECT backup_set_id, 'RESTORE LOG "' + @databaseName + '" FROM DISK = '''

 

Line 66 from:

SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY --' Cmd

to

SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE "' + @databaseName + '" WITH RECOVERY --' Cmd


Wednesday, July 22, 2009 - 9:10:42 AM - Tripp Back To Top (3765)

First let me say thank you for another, excellent, time-saving tip.  I love this script and have been using it on my SQL 2005 servers without issue for several months now.  I'm now trying to use it with three, legacy SQL Server 2000 instances but am getting mixed results. 

 

In two of the three SQL 2000 (sp4) environments, I get the following error when the script is parsed:

  Msg 137, Level 15, State 1, Line 82Must declare the variable '@DatabaseName'.

 

The point of failure is the last @DatabaseName; the fetch from dbnames_cursor into @DatabaseName.  The DatabaseName variable was declared at the beginning of the script and used 9 times by this point. 

 

What’s stranger still, is that the exact SQL executes without an error on a third SQL Server 2000 (sp4) database instance.  I'm in the process of combing through the configurations of the database servers for any differences, but since these three instances are the database backend for three completely autonomous application environments there will be numerous intentional configuration differences.  Any ideas as to the cause of this error?    

 


Friday, November 21, 2008 - 8:28:34 AM - hansontd Back To Top (2258)

There sure is....it has to be this Bridgehead agent that's creating these other backups....


Friday, November 21, 2008 - 8:15:09 AM - KenSimmons Back To Top (2257)

It is getting the information from the backupmediafamily table. Run the following query and see if you see anything like that in the physical_device_name column.

Select * from msdb.dbo.backupmediafamily


Friday, November 21, 2008 - 7:20:39 AM - hansontd Back To Top (2253)

Not that I'm aware of....all backups write to the same drive on the server (SAN spaced assigned to the box) here is a snapshot of that:

Not sure but it's just strange that it's referencing the log files corrrectly but not the backups....


Thursday, November 20, 2008 - 6:49:55 PM - KenSimmons Back To Top (2245)

Just by looking at the results, it looks like HTSQLServerINOW6 may be the name of a backup device.  Do you know if anyone may have changed the backup to use a backup device?


Thursday, November 20, 2008 - 1:19:40 PM - hansontd Back To Top (2244)
Been using the script for a couple of weeks and it has been working great....listing the last full backup and log backups per the articles description...However I checked the file today and found the following, no changes have been made to the server that I'm aware of. The output below is with the newest version of the code listed in this thread but I was using the original version prior with success and both produced a similar result...

-- Script to Restore the INOW6 Database -- [SQLSTATE 01000]

GO [SQLSTATE 01000]

RESTORE DATABASE INOW6 FROM DISK = 'HTSQLServerINOW6' WITH NORECOVERY, STATS =10 [SQLSTATE 01000]

GO [SQLSTATE 01000]

RESTORE LOG INOW6 FROM DISK = 'F:\BACKUPS\INOW6_backup_200811201200.trn' WITH FILE = 1, NORECOVERY, STATS =10 [SQLSTATE 01000]

GO [SQLSTATE 01000]

RESTORE DATABASE INOW6 WITH RECOVERY, STATS =10 [SQLSTATE 01000]

GO [SQLSTATE 01000]

I'm just curious as to why the restore database segment points to the HTSQLServerINOW6 versus the F:\ location that the restore log statement points to? Guess I should clarify that both files ARE in the same location.

 Thanks!

 


Monday, November 10, 2008 - 1:09:33 PM - KenSimmons Back To Top (2184)

That depends on your situation. Most of the times you do not have to worry about the model database because it is just used as a model to create new databases, but if you make changes to the model database that get applied to any new database, you should back it up.  Generally the main system databases I worry about is master and msdb. You can modify the script to include whatever databases you may need.


Monday, November 10, 2008 - 10:00:09 AM - ppcx Back To Top (2183)
Ok. Here's a probably dumb question:

If I'm building scripts for disaster recovery of the database, even though I would need to recover the master in single-user mode, wouldn't it be nice to have the commands already generated? For example, "model" is not simple-recovery so it has transaction logs. It would be nice to have the commands to apply all of the transaction logs to the database.


Monday, November 10, 2008 - 8:33:48 AM - Dr DBA Back To Top (2181)

 Hello everone,

From the discussion here I decided to modify the orginal code with the following

1) Added the GO statements so you can now execute the complete output

2) Added STAT=10 meaning that you will get an indication of the restore as show as 10% increaments

3) No restore scripts generated for any system databases (master, model, msdb) as you need to place the server in single user mode to restore the master and then you should manually restore the msdb and model

4) No restore script generated for tempdb

This script works fine with SQL 2000 (x32) and SQL 2005 (x32 and x64)

Enjoy

 

SET NOCOUNT ON
DECLARE @databaseName sysname

CREATE TABLE #TmpCommands
(ID INT IDENTITY(1,1),
Cmd VARCHAR(8000) )

PRINT '            *************************************************************************'
PRINT '            *                Database Recovery Script Auto Generator                    *'
PRINT '            *                            Version 3.0                                    *'
PRINT '            *                                                                        *'
PRINT '            * Execution of this script will create an output to restore databases    *'
PRINT '            * from this server. Just copy the output, verify and execute.            *'
PRINT '            *                                                                        *'
PRINT '            *Note that this script will not generate the code to restore the system *'
PRINT '            *    databases. But if needed, it can be easily modified                    *'
PRINT '            *                                                                        *'
PRINT '            *                There is no output for the tempdb database!                *'
PRINT '            *                                                                        *'
PRINT '            *************************************************************************'
PRINT ''
PRINT 'USE MASTER'
DECLARE dbnames_cursor CURSOR
FOR
 SELECT name
 FROM master..sysdatabases
 WHERE name NOT IN ( 'master','model','msdb','tempdb', 'pubs','northwind')
 AND (status & 32) =0 -- Do not include loading
 AND (status & 64) =0 -- Do not include loading
 AND (status & 128) =0 -- Do not include recovering
 AND (status & 256) =0 -- Do not include not recovered
 AND (status & 512) =0 -- Do not include offline
 AND (status & 32768) =0 -- Do not include emergency
 AND (status & 1073741824) =0 -- Do not include cleanly shutdown
 
 OPEN dbnames_cursor
 
 PRINT ''
 
 FETCH NEXT FROM dbnames_cursor INTO @databaseName
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
 IF (@@FETCH_STATUS <> -2)
 BEGIN


INSERT INTO #TmpCommands(Cmd)
VALUES ('                   -- Script to Restore the ' + @databaseName + ' Database -- ')

DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT

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_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

INSERT INTO #TmpCommands(Cmd)
SELECT Cmd FROM(
SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
 + mf.physical_device_name + ''' WITH NORECOVERY, STATS =10' Cmd
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 FILE = ' + CAST(position AS VARCHAR(10)) + ', NORECOVERY, STATS =10' Cmd
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, STATS =10' Cmd
) A
ORDER BY backup_set_id

END


FETCH NEXT FROM dbnames_cursor INTO @DatabaseName
 END

CLOSE dbnames_cursor
 DEALLOCATE dbnames_cursor

DECLARE @PrintCommand VARCHAR(8000)

DECLARE Print_cursor CURSOR
FOR
 SELECT Cmd FROM #TmpCommands
 ORDER BY ID
 
 OPEN Print_cursor
 
 FETCH NEXT FROM Print_cursor INTO @PrintCommand
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
 IF (@@FETCH_STATUS <> -2)
 BEGIN
 PRINT @PrintCommand
PRINT 'GO'
 END
 FETCH NEXT FROM Print_cursor INTO @PrintCommand
 PRINT ''

 END
 CLOSE Print_cursor
 DEALLOCATE Print_cursor
 
DROP TABLE #TmpCommands

GO

 


Monday, November 10, 2008 - 7:43:40 AM - ppcx Back To Top (2178)
When I try to run the script in sqlcmd it seems to want a "go" between each line and doesn't recognize that each line is its own separate command to be run. If I copy/paste the lines for the "master" and "msdb" databases

RESTORE DATABASE master FROM DISK = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master_backup_200811070200.bak' WITH NORECOVERY -- [SQLSTATE 01000]
RESTORE DATABASE master WITH RECOVERY -- [SQLSTATE 01000]
----------------Script to Restore the msdb Database-------------- [SQLSTATE 01000]
RESTORE DATABASE msdb FROM DISK = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_backup_200811070200.bak' WITH NORECOVERY -- [SQLSTATE 01000]
RESTORE DATABASE msdb WITH RECOVERY -- [SQLSTATE 01000]

this is what sqlcmd looks like:

S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>sqlcmd -S sqlcluster01 -E
1> RESTORE DATABASE master FROM DISK = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master_backup_200811070200.bak' WITH NORECOVERY -- [SQLSTATE 01000]
2> RESTORE DATABASE master WITH RECOVERY -- [SQLSTATE 01000]
3> ----------------Script to Restore the msdb Database-------------- [SQLSTATE 01000]
4> RESTORE DATABASE msdb FROM DISK = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_backup_200811070200.bak' WITH NORECOVERY -- [SQLSTATE 01000]
5> RESTORE DATABASE msdb WITH RECOVERY -- [SQLSTATE 01000]

It doesn't see each line as a command to run and just spools them all up together and then complains that the whole huge line isn't valid.


Friday, November 7, 2008 - 7:12:34 PM - KenSimmons Back To Top (2169)

[quote user="ppcx"]I'm new to MSSQL so this may be a simple question, but I can't get the generated script to run. If I take each line separately copy/paste into a sqlcmd prompt and type "go" then that line will work. But I can't figure out how to make the whole script run. This seems like it would be a great disaster recovery script if I can figure out how to make it work.[/quote]

What are you having an issue with?  Do you get an error message?


Friday, November 7, 2008 - 7:10:34 PM - KenSimmons Back To Top (2168)

A lot of times as soon as a backup is complete Full or Transaction log it is copied to some other source.  If you have to pull the backups from another source and restore them to another server that is already running this could help.  I generally don't rely on the GUI to perform everything.  If you had to manually write the script for some reason this would be a big time saver in a disaster scenario.


Friday, November 7, 2008 - 3:41:42 PM - ppcx Back To Top (2167)
I'm new to MSSQL so this may be a simple question, but I can't get the generated script to run. If I take each line separately copy/paste into a sqlcmd prompt and type "go" then that line will work. But I can't figure out how to make the whole script run. This seems like it would be a great disaster recovery script if I can figure out how to make it work.

Friday, November 7, 2008 - 3:11:36 PM - pmk003 Back To Top (2166)

I like this concept, but I do have a question... Why would you need these scripts instead of using the GUI to restore?  When wouldn't you have access to msdb to run your restore - an entire server crash???  But in that case, we store the backups on the box itself and wouldn't have access to the most current backups anyways...  What case(s) would there be where the server would be available (such that you could get to the backup files) but that SQL Server msdb wouldn't be available?















get free sql tips
agree to terms