![]() |
|
|
By: Greg Robidoux | Read Comments (23) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: 1 | 2 | 3 | 4 | More |
|
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:
So let's say we are creating our backups on the following schedule:
At 9am we would have the following backup files created for September 10, 2008 for the "Customer" database following the rules above.
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:
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; |
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.

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
| Share: | Share | Tweet |
|
![]() |
|
|
Free SQL Server Learning |
| Tuesday, September 16, 2008 - 10:46:48 AM - ChrisAVWood | Read The Tip |
|
Be carefull if you have 2 transaction backups with the same filename. We just had the situation where an SQL2005 maintenance plan created backup job wrote 2 log backups to the same filename. To differentiate you need to add the with file= parameter. Chris
|
|
| Tuesday, September 16, 2008 - 1:18:52 PM - grobido | Read The Tip |
|
Thanks Chris for the input. That is true this process assumes there is only one backup per file, so this is not taken into consideration. I am surprised that a SQL Maintenance plan wrote two backups to the same file, but I guess it is possible. The default is to "Append" new backups if the file already exists. So yes there are some future improvements that could be made to this script. If the RESTORE HEADERONLY option is used you could see what is in the file first and then figure out how to do build the restore script. |
|
| Monday, September 29, 2008 - 8:44:50 AM - jkli | Read The Tip |
|
This information is very useful as I need to run backup and restore programatically. All that is missing from this post is running the restore script programmatically. Is there anyway to send the generated script to a script file so that it can be run it through c# code? |
|
| Tuesday, October 28, 2008 - 2:16:48 PM - grobido | Read The Tip |
|
This could be run interactively where the restores happen in place of the PRINT commands. If you run this on another SQL Server and replace the PRINT @cmd with EXEC (@cmd) this will do the actual restore for you instead of having to create a script file. |
|
| Tuesday, October 28, 2008 - 3:08:29 PM - jkli | Read The Tip |
|
Thanks. I will be accessing these scripts through c#. I think it will work.
|
|
| Tuesday, February 16, 2010 - 5:51:15 PM - avadhanam | Read The Tip |
|
In my case the RESTORE LOG commands generated by the script were in random order. so I had to modify the following so that the RESTORE LOG commands are all generated in the correct order for each of the TRN files: Original line: DIR /B Modified line: DIR /B /O D the /O indicates that the files need to be fetched from the file system based on the file creation date. This guaranteed that the RESTORE log commands were all generated in the same order that the TRNs were created and were ready to be executed AS IS. |
|
| Tuesday, May 17, 2011 - 6:20:19 AM - tommyketchup | Read The Tip |
|
Hi I tried that script, but I kept getting failures. This is the first generated script with the DB name and location of files RESTORE DATABASE Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105160917.bak' WITH NORECOVERY, REPLACE
When I try to run this, I get loads of errors which carry on like this :-
|
|
| Tuesday, May 17, 2011 - 6:39:36 AM - tommyketchup | Read The Tip |
|
However if I script the action, then the command actually looks like this :- RESTORE LOG [Binary 5_01] FROM DISK = N'k:\Binary 5_01_backup_201105161005.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 rather than RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170625.trn' WITH NORECOVERY Regards TK |
|
| Tuesday, May 17, 2011 - 7:23:27 AM - Greg Robidoux | Read The Tip |
|
You need to change the script to include square brackets. For each line like this: SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = ''' Change to this: SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = ''' There are a few places in the script where you will need to add the square brackets. |
|
| Tuesday, May 17, 2011 - 7:33:20 AM - tommyketchup | Read The Tip |
|
Thanks Greg, that worked. However this command tried to recover the DB to the default locations of the MSSQL install, which is currently the C drive, with 10GB free space. The DB plus log files comes to 17GB, and it fails each time. Is there a move command I can incorporate into the script which will allow me to set the destination of the SQL DB and log files?
Regards TK |
|
| Tuesday, May 17, 2011 - 7:48:35 AM - Greg Robidoux | Read The Tip |
|
Take a look at this page: http://www.mssqltips.com/tutorial.asp?tutorial=122 You can generate the above and then add in the WITH MOVE. You only need to do this for the DATABASE restore. The logs will restore to the same location as where you restored the Full Backup. |
|
| Monday, November 28, 2011 - 4:39:54 PM - Ankit Shah | Read The Tip |
|
This Script works if i want to restore Full Backup and Differential Backup only every day ? We do Full backup once in week every sunday . Differential backup every night . I have to setup automated restore after differential backup finished every night. So can this script helps me? |
|
| Monday, November 28, 2011 - 5:19:06 PM - Greg Robidoux | Read The Tip |
|
Yes this should still work because it gets the last FULL backup and last DIFFERENTIAL backup.
|
|
| Monday, November 28, 2011 - 5:37:47 PM - Ankit Shah | Read The Tip |
|
So to runt his script both Full backup file and Differential backup file sholud be at one place or in one folder ? or we can define different path ? I have Full backup file on local machine and Differential backup file on shared drive so can you help me how i can put path in that script please? Because this script i want to put in sql agent job which runs mid night . |
|
| Wednesday, November 30, 2011 - 4:47:51 PM - Scott C | Read The Tip |
|
This seems like such a simple problem, until you start running into "What if there are multiple backups per file?" and "What if the files need to be relocated?". The script below handles most of the issues that I have come across so far in my career. This script is fairly robust, but it still has a list of assumptions that will cause it to fail if violated. All backup operations must be simple one-file commands. No striped backups, no partial backups, no file or filegroup backups. It can handle multiple backups per file. /* Restore a database backup from a different server Assumes all relevant backup files are still online in their original locations. Possible extension: use "EXEC sys.xp_fileexist" to check that all required -- Create a temporary linked server named SourceServer to get backup history EXEC master.dbo.sp_addlinkedserver @server = N'SourceServer', @srvproduct=N'SQL_Server', @provider=N'SQLNCLI', @datasrc=N'$(SOURCE_SERVER)' ; -- Decide where to put the restored database SET @HKLM = 'HKEY_LOCAL_MACHINE' ; -- read the directory locations /* If a separate folder is desired for a database with multiple data files, or to avoid filename conflicts: DECLARE @cmd NVARCHAR(MAX) ; -- Initial restore of full backup, including MOVE clauses -- Differential restore, if present (otherwise @cmd and @LastBackup are unchanged) -- Log restores, if present SET @cmd = @cmd + N'; RESTORE DATABASE [$(DBNAME)] WITH RECOVERY;' ; -- Update the database compatibility level if the database was at the current level on the old server, PRINT REPLACE(@cmd, ';', CHAR(10)) ; -- Drop the SourceServer linked server |
|
| Wednesday, November 30, 2011 - 4:53:40 PM - Scott C | Read The Tip |
|
PS: The lines that begin with :SETVAR are SQLCMD commands. This script runs in SQLMCD mode, which can be found on the Query menu in Management Studio. I use SQLCMD mode a lot, so I customize the SSMS toolbar to add the SQLCMD mode button from the Query group. |
|
| Thursday, December 01, 2011 - 12:32:48 PM - Ankit Shah | Read The Tip |
|
I am Trying to run this Script to Restore Full and Differential Backup But it giving me only this message " RESTORE DATABASE AdventureWorks WITH RECOVERY". I am testing on AdventureWorks DB and Full and Diff backup file stored at C:\Temp. Can you help me out with this script ?
Thanks
|
|
| Thursday, December 01, 2011 - 12:53:08 PM - Scott C | Read The Tip |
|
The "RESTORE ... WITH RECOVERY" command is the only one that doesn't require backup history. The other queries rely on data in backup history tables to know which files to restore. Did you copy the files to C:\Temp, or is that your actual backup folder? Are you setting the SOURCE_SERVER variable to the server that ran the backup commands? |
|
| Thursday, December 01, 2011 - 2:03:52 PM - Ankit Shah | Read The Tip |
|
I had 1 full backup file ,1 diff backup file and 2 T-log file on local machine C:\Temp\... Just giving me same Message . When i ran the other query to check history here what i got .. http://www.mssqltips.com/sqlservertip/1243/auto-generate-sql-server-database-restore-scripts/ backup_set_id (No column name) |
|
| Thursday, December 01, 2011 - 3:00:28 PM - Scott C | Read The Tip |
|
The original script lists the backup files in the specified directory to create the RESTORE commands. This requires making some assumptions about backup file naming conventions and extensions, and that there is only one backup per file. My script gets the filenames from the backup history tables and so makes no assumptions about file names or locations. But it only works if it can read the backup history tables on the server that ran the BACKUP statements, and if the backup files have not been moved. I doubt that you have one server writing backups to C:\Temp on the other server, so you are violating these assumptions. If you want to restore backups from a couple of files in C:\Temp, the original script should work. If my script adds something that you need, such as generating the MOVE clauses, then you'll have to merge the two scripts to suit yourself. |
|
| Thursday, December 01, 2011 - 3:19:08 PM - Ankit Shah | Read The Tip |
|
Thanks for your kind Help your script is nice but the script how i want to lokking for i found it from here . http://www.sqlservercentral.com/scripts/Restore/61810/ Thanks again |
|
| Thursday, May 10, 2012 - 2:41:52 AM - Jahirul Hassan | Read The Tip |
|
I run the script, It shows the following message: Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 |
|
| Thursday, May 10, 2012 - 3:22:15 AM - Jahirul Hassan | Read The Tip |
|
Solved through following code. sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
|
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |