Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Auto generate SQL Server restore scripts after each backup completes


By:   |   Read Comments (20)   |   Related Tips: 1 | 2 | 3 | 4 | More > 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.

(note: to select all text, triple click in the table below)

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=-- Do not include loading
 
AND (status 64=-- Do not include loading
 
AND (status 128=-- Do not include recovering
 
AND (status 256=-- Do not include not recovered
 
AND (status 512=-- Do not include offline
 
AND (status 32768=-- Do not include emergency
 
AND (status 1073741824=-- 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.

Here is sample of what the output looks like:

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



Last Update:






About the author





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

 

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


Monday, June 01, 2015 - 2:25:25 PM - Greg Robidoux Back To Top

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 01, 2015 - 1:30:16 PM - Bill Hughes Back To Top

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

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

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

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

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

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

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

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

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

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

 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
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 07, 2008 - 7:12:34 PM - KenSimmons Back To Top

[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 07, 2008 - 7:10:34 PM - KenSimmons Back To Top

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 07, 2008 - 3:41:42 PM - ppcx Back To Top
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 07, 2008 - 3:11:36 PM - pmk003 Back To Top

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?


Learn more about SQL Server tools