Auto generate SQL Server database restore scripts

By:   |   Updated: 2007-05-14   |   Comments (16)   |   Related: 1 | 2 | 3 | 4 | > Restore


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_start 

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

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.

column name

Summary

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

Next Steps
  • Take a look at other backup and restore tips
  • Start with this base script and add additional functionally to meet your needs. If you create a better version, please send to [email protected] so we can make this avaiable to the entire MSSQLTips.com community.
  • Turn this script into a stored procedure that takes the database name as a parameter
  • Setup a process where this runs on a set schedule and automatically generates the script for you. This way if there is a need to do a fast recovery the restore script is already available.
  • Check out this updated script by Glynne Smith from the MSSQLTips community. This update handles a single backup striped across multiple files. Thanks Glynne for the update.
  • Here is another version from Chris Gelhaus. This updated version handles differential backups as well as multiple backups written to the same file. Thanks Chris.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

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

View all my tips


Article Last Updated: 2007-05-14

Comments For This Article




Tuesday, August 30, 2016 - 12:55:44 PM - Mike Back To Top (43224)

Hey Greg,

 

          I tested your script and it worked great.  The only missing piece from it is Diff.  If you could make it complete, that would be wonderful and really practical for real world situation.  Thank you again for this wonderful work.

 

-Mike

Tuesday, August 30, 2016 - 12:19:55 PM - Greg Robidoux Back To Top (43223)

Hi Mike,

Not sure if John G is still following this or not.  I will see if I can update the script to include Differential backups, just not sure when I will be able to get to this.

-Greg

Tuesday, August 30, 2016 - 11:58:42 AM - Mike Back To Top (43222)

I tested John G's script, NOT working with many errors.  Can you please improve your script?  This looks promissing.  Thanks.

 

Msg 4145, Level 15, State 1, Line 24
An expression of non-boolean type specified in a context where a condition is expected, near 'SELECT'.

--For here "SELECT @backup_set_id_DIFF = MAX(bs.backup_set_id)"


Msg 4145, Level 15, State 1, Line 35
An expression of non-boolean type specified in a context where a condition is expected, near 'if'.

--For here "if (@backup_set_id_DIFF is NULL)"


Msg 4145, Level 15, State 1, Line 94
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

--for here "AND b.type = 'L'"

 

I also found out some errors

such as

"WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @recover_database
AND b.backup_set_id >= @backup_set_id_DIFF
AND b.backup_set_id      --?????????
AND b.type = 'L'
UNION"

"SELECT @backup_set_id_FULL = MAX(bs.backup_set_id)
FROM backupset bs
INNER JOIN backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bmf.device_type = 2
    AND bs.type = 'D'
AND bs.database_name = @recover_database
AND bs.backup_start_date      ----????????
 "

Tuesday, November 24, 2015 - 11:24:21 AM - John G Back To Top (39130) 
--
-- Added in allowance for a differential backup (Tonny,  grobido)and a point-in-time recovery
--   also moved non-code fields to end of line behind "--" so you can copy-paste
--   all of the output as a script (smgs87)
--
-- Database-based recovery script. You will need to find and copy all of the
--   required files to the proper directory. Besure to specify the database
--   and desired recovery point in the appropriate variables.
--
use [msdb]
GO
 
SET NOCOUNT ON
 
DECLARE @backup_set_id_FULL INT
DECLARE @backup_set_id_DIFF INT
DECLARE @backup_set_id_TLOG INT
DECLARE @recover_database SYSNAME
DECLARE @recover_datetime DATETIME
 
SET @recover_database = 'DatabaseNameHere'
SET @recover_datetime = '2015-11-24T11:23:45.000'
 
-- Get the backup_set_id of the last full backup before @recover_datetime
SELECT @backup_set_id_FULL = MAX(bs.backup_set_id)
FROM backupset bs
INNER JOIN backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bmf.device_type = 2
    AND bs.type = 'D'
AND bs.database_name = @recover_database
AND bs.backup_start_date
 
-- Get the backup_set_id of the last differential backup before @recover_datetime
--  AND after the full backup from above. (it may not exist!)
SELECT @backup_set_id_DIFF = MAX(bs.backup_set_id)
FROM backupset bs
INNER JOIN backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bmf.device_type = 2
    AND bs.type = 'I'
AND bs.database_name = @recover_database
AND bs.backup_set_id > @backup_set_id_FULL
AND bs.backup_start_date
 
-- If there is no differential to use, the transaction log query should
--   reference the full backup set id instead
if (@backup_set_id_DIFF is NULL)
    set @backup_set_id_DIFF = @backup_set_id_FULL
 
-- get the backup set id of the first transactipn log with a finish datetime
--   AFTER @recover_datetime
SELECT @backup_set_id_TLOG = MIN(bs.backup_set_id)
FROM backupset bs
INNER JOIN backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bmf.device_type = 2
    AND bs.type = 'L'
AND bs.database_name = @recover_database
AND bs.backup_set_id > @backup_set_id_DIFF
AND bs.backup_finish_date >= @recover_datetime
 
-- Select all the backup media required for the restore and build the T-SQL
-- The full backup, identified by backup set id
SELECT 'RESTORE DATABASE ' + @recover_database + ' FROM DISK = ''' + mf.
physical_device_name + ''' WITH FILE = ' + convert(VARCHAR(10), b.position) + 
', NORECOVERY' AS [-- CommandSet]
,'--' AS [Comment]
,backup_set_id
,backup_start_date
,backup_finish_date
FROM msdb.dbo.backupset b
,msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @recover_database
AND b.backup_set_id = @backup_set_id_FULL
UNION
-- The differential backup identified by backup set id (UNION will resolve the
--   duplication if @backup_set_id_DIFF = @backup_set_id_FULL
SELECT 'RESTORE DATABASE ' + @recover_database + ' FROM DISK = ''' + mf.
physical_device_name + ''' WITH ' + 'FILE = ' + convert(VARCHAR(10), b.position) + 
', NORECOVERY' AS CommandSet
,'--'
,backup_set_id
,backup_start_date
,backup_finish_date
FROM msdb.dbo.backupset b
,msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @recover_database
AND b.backup_set_id = @backup_set_id_Diff
UNION
-- The transaction log backups since the full or differential backup up to and
--   including the one transaction log backup that goes past @recover_datetime
SELECT 'RESTORE LOG ' + @recover_database + ' FROM DISK = ''' + mf.physical_device_name + 
''' WITH ' + 'FILE = ' + convert(VARCHAR(10), b.position) + ', STOPAT = ''' + convert(
VARCHAR(30), @recover_datetime, 126) + ''', NORECOVERY' AS CommandSet
,'--'
,backup_set_id
,backup_start_date
,backup_finish_date
FROM msdb.dbo.backupset b
,msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @recover_database
AND b.backup_set_id >= @backup_set_id_DIFF
AND b.backup_set_id
AND b.type = 'L'
UNION
-- Wrap up the recovery explicitly
SELECT 'RESTORE DATABASE ' + @recover_database + ' WITH RECOVERY' AS CommandSet
,'--'
,999999998
,getdate() AS backup_start_date
,getdate() AS backup_finish_date
UNION
-- Displays @recover_datetime on the last line to compare to the last backup set id
--  finish date.
SELECT '-- verify last finish date against @recover_datetime'
,'--'
,999999999
,@recover_datetime
,@recover_datetime
ORDER BY backup_set_id
GO
 

Thursday, November 19, 2015 - 3:53:31 PM - John G Back To Top (39107)

I was just going to sit down and figure out how to write this very script. Once again MSSQLTips has saved me hours of effort!

Monday, August 24, 2015 - 7:15:06 AM - Per Gunnar Hansų Back To Top (38519)

Thanks for this nifty script, Greg :-)

To support multiple backup sets, the script could be modified like this:

 

declare @first_member int
declare @last_member int

select @first_member = min(mf.family_sequence_number)
FROM  msdb.dbo.backupset b
join msdb.dbo.backupmediafamily mf
on b.media_set_id = mf.media_set_id
WHERE database_name = @databaseName
    AND type = 'D'
    and backup_set_id = @backup_set_id_FULL

select @last_member = max(mf.family_sequence_number)
FROM  msdb.dbo.backupset b
join msdb.dbo.backupmediafamily mf
on b.media_set_id = mf.media_set_id
WHERE database_name = @databaseName
    AND type = 'D'
    and backup_set_id = @backup_set_id_FULL

select CommandSet from (
-- UNION the Full backup with the Differential, with the trailing TLOG backups
SELECT backup_set_id, mf.family_sequence_number as family_member,
        case when mf.family_sequence_number = @first_member then
             ('RESTORE DATABASE ' + @databaseName + ' FROM ')
             else '' end
             +
             'DISK = ''' + mf.physical_device_name + ''''
             +
             case when mf.family_sequence_number = @last_member then (' WITH ' + 'FILE = ' + convert(varchar(10), b.position) +  ', NORECOVERY, REPLACE')
             else ',' end
                as CommandSet
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_Full
UNION
SELECT backup_set_id, 0, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''  
               + mf.physical_device_name + ''' WITH ' + 'FILE = ' + convert(varchar(10), b.position) +  ', 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_Diff
UNION
SELECT backup_set_id, 0 , 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''  
               + mf.physical_device_name + ''' WITH ' + 'FILE = ' + convert(varchar(10), b.position) +  ', 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_Diff AND b.backup_set_id < @backup_set_id_end
          AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id, 0, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
) a
ORDER BY backup_set_id, family_member

 

Best regards,

Per Gunnar Hansø

Tuesday, June 30, 2015 - 7:06:16 AM - Greg Robidoux Back To Top (38076)

Hi AWEInCA,

to be honest I don't remember why I did that.  Like you said this post is really old.

Looking at the code it doesn't make any sense why that should be there.  If you already selected the MAX there shouldn't be any later entries.

-Greg

Monday, June 29, 2015 - 7:23:02 PM - AWEInCA Back To Top (38067)

This is a really old post, but I'm curious about this part of the script:

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 

In what situation would this ever return anything? Am I missing a subtle case that needs to be accounted for? 

Sunday, September 21, 2014 - 7:50:31 AM - Hany Helmy Back To Top (34644)

Really wonderfull & usefull script, tested, working fine in SQL Server 2008 R2 & 2012 SP2.

Thanx

Thursday, June 19, 2014 - 9:17:10 AM - Greg Robidoux Back To Top (32314)

Hi Megharaj,

check out this article about how to restore a database using T-SQL: http://www.mssqltips.com/sqlservertutorial/110/how-to-restore-a-sql-server-backup/

Thursday, June 19, 2014 - 6:03:00 AM - megharaj Back To Top (32308)

Hi,

 

how to restore database in query ....and simple method...

 

Tuesday, November 26, 2013 - 7:32:59 AM - durgasandesh Back To Top (27606)

with little modification we can get script for all existing databases in an instance.

 

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

 

 

Monday, April 30, 2012 - 11:24:02 AM - Hans Back To Top (17209)

This script doesn't work with Backups striped over multiple files and neither does the scripts from 'Glynne Smith' and 'Chris Gelhaus'...

Tuesday, October 28, 2008 - 2:09:42 PM - grobido Back To Top (2102)

This could be easily changed to do that.  Just need to find the last DIFFERENTIAL and then all TRANS LOGs after that.

Tuesday, October 28, 2008 - 7:08:32 AM - Tonny Back To Top (2099)

Why not include the script to also restore diff backup as part of the restore job?

Wednesday, September 24, 2008 - 12:33:18 PM - smgs87 Back To Top (1867)

 IF you were to take the sequence numbers, place them after the restore command, prefixed with a comment indicator ('--'), then you could execute the query directly to a recovery script to restore the database, then execute the script in its entirety if desired, or edit before execute.














get free sql tips
agree to terms