Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

SQL Server backup automation and best practices

Providing High Availability to an Existing SQL Server Workload

Backup made easy with SQL Safe Backup

High Availability, Disaster Recovery, Low Cost Storage, and SIOS DataKeeper

SQL Server Performance Monitoring in the Cloud

Auto generate SQL Server database restore scripts

MSSQLTips author Greg Robidoux By:   |   Read Comments (8)   |   Related Tips: 1 | 2 | 3 | 4 | More > Restore

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?

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
@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
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'
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.

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 recovery tips
  • Start with this base script and add additional functionally to meet your needs.  If you create a better version, please send to tips@mssqltips.com 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.

Last Update: 5/14/2007

About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Sunday, September 21, 2014 - 7:50:31 AM - Hany Helmy Read The Tip

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


Thursday, June 19, 2014 - 9:17:10 AM - Greg Robidoux Read The Tip

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 Read The Tip



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


Tuesday, November 26, 2013 - 7:32:59 AM - durgasandesh Read The Tip

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







-- set database to be used 

SET @databaseName = '@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 





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'       


SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @dbname + ' WITH NORECOVERY' AS Query

ORDER BY backup_set_id











Monday, April 30, 2012 - 11:24:02 AM - Hans Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

 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.

Sponsor Information