Auto generate SQL Server restore script from backup files in a directory

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


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:

  1. The restored database will have the same name as the backed up database
  2. The restored database will be restored in the same location as the backed up database
  3. The files have the following naming format
    • dbName_YYYYMMDDHHMM.xxx
  4. File extensions are as follows
    • Full backup - BAK
    • Differential backup - DIF
    • Transaction log backup - TRN
  5. XP_CMDSHELL is enabled
  6. There are no missing transaction logs that may break the restore chain

So let's say we are creating our backups on the following schedule:

  • Full backups at midnight
  • Differential backups every 3 hours starting at 3:15am
  • Log backups every 30 minutes starting at 1am

At 9am we would have the following backup files created for September 10, 2008 for the "Customer" database following the rules above.

  • Customer_200809100000.BAK
  • Customer_200809100100.TRN
  • Customer_200809100130.TRN
  • Customer_200809100200.TRN
  • Customer_200809100230.TRN
  • Customer_200809100300.TRN
  • Customer_200809100315.DIF
  • Customer_200809100330.TRN
  • Customer_200809100400.TRN
  • Customer_200809100430.TRN
  • Customer_200809100500.TRN
  • Customer_200809100530.TRN
  • Customer_200809100600.TRN
  • Customer_200809100615.DIF
  • Customer_200809100630.TRN
  • Customer_200809100700.TRN
  • Customer_200809100730.TRN
  • Customer_200809100800.TRN
  • Customer_200809100830.TRN
  • Customer_200809100900.TRN

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:

  • Customer_200809100000.BAK
  • Customer_200809100615.DIF
  • Customer_200809100630.TRN
  • Customer_200809100700.TRN
  • Customer_200809100730.TRN
  • Customer_200809100800.TRN
  • Customer_200809100830.TRN
  • Customer_200809100900.TRN

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; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 

-- 2 - Initialize variables 
SET @dbName = 'Customer' 
SET @backupPath = 'D:\SQLBackups\' 

-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
   AND backupFile LIKE @dbName + '%' 

SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE' 
PRINT @cmd 

-- 4 - Find latest diff backup 
SELECT @lastDiffBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.DIF'  
   AND backupFile LIKE @dbName + '%' 
   AND backupFile > @lastFullBackup 

-- check to make sure there is a diff backup 
IF @lastDiffBackup IS NOT NULL 
BEGIN 
   SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY' 
   PRINT @cmd 
   SET @lastFullBackup = @lastDiffBackup 
END 

-- 5 - check for log backups 
DECLARE backupFiles CURSOR FOR  
   SELECT backupFile  
   FROM @fileList 
   WHERE backupFile LIKE '%.TRN'  
   AND backupFile LIKE @dbName + '%' 
   AND backupFile > @lastFullBackup 

OPEN backupFiles  

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @backupFile  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''  
       + @backupPath + @backupFile + ''' WITH NORECOVERY' 
   PRINT @cmd 
   FETCH NEXT FROM backupFiles INTO @backupFile  
END 

CLOSE backupFiles  
DEALLOCATE backupFiles  

-- 6 - put database in a useable state 
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY' 
PRINT @cmd 

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.

messages

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
  • This is a pretty straight forward and simple approach.  As mentioned above it restores using the same name and also restores to the same file location.  Try making some modifications to restore it to another database name as well as restoring the files to a different location by incorporating the RESTORE FILELISTONLY command
  • This script will work on any server where the files exists and you can run a SQL Server query.  So you can copy the files from one server to another, run this script and then have your restore script ready to go.
  • Check out these other restore scripts:


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



Comments For This Article




Monday, September 9, 2024 - 2:12:59 PM - Greg Robidoux Back To Top (92492)
Hi Neeraj, see if you can use xp_dirtree instead.

Monday, September 9, 2024 - 2:35:36 AM - Neeraj Back To Top (92489)
Hi Greg,

I was used this script to restore db full backup to run automation job. While running this job I got the below error

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

Due to the security, we can't be able to enable xp_cmdshell. So, can we run this script without using xp_cmdshell. Please guide me to resolve this and we have some request to restore multiple databases on daily basis.

Thursday, September 5, 2024 - 12:39:10 PM - Neeraj Back To Top (92486)
Thanks Greg for the immediate response. Will do the same as you suggested.

Thursday, September 5, 2024 - 9:52:27 AM - Greg Robidoux Back To Top (92484)
Sure you can do that. Just need to change the cursor to loop over multiple databases and just look for the full backup files.


Thursday, September 5, 2024 - 8:54:49 AM - Neeraj Back To Top (92483)
Hi Grig, Thanks for the script. Can we use this script for multiple databases to restore from backup location on regular basis. I have to restore only Fullbackup and we have to schedule it as Automated job to run every day.

Friday, June 21, 2024 - 9:51:49 AM - Greg Robidoux Back To Top (92334)
Thanks Gordy.

Friday, June 21, 2024 - 6:09:36 AM - Gordy Back To Top (92331)
I couldn't quite get this to work for me for various reasons, but it got me off to a good start. 1. Tweaked it to use Hallengren-style FULL, DIFF and LOG folders. 2 Changed the way it outputs the restore commands. 3. Changed it a little to filter out NULL entries from @fileList

Copying it here in case someone else finds it useful.

USE Master;
GO
SET NOCOUNT ON

-- Declare your variables
DECLARE @dbName sysname;
DECLARE @backupPath NVARCHAR(500);
DECLARE @cmd NVARCHAR(500);
DECLARE @fileList TABLE (backupFile NVARCHAR(255));
DECLARE @lastFullBackup NVARCHAR(500);
DECLARE @lastDiffBackup NVARCHAR(500);
DECLARE @backupFile NVARCHAR(500);
DECLARE @restoreCommands TABLE (cmd NVARCHAR(MAX));

-- Set values required
-- Pay attention to the \ character at the end of @backupPath
SET @dbName = 'database-name';
SET @backupPath = '\\server\folder\';

-- Get FULL backup filelist
SET @cmd = 'DIR /b "' + @backupPath + 'FULL\"';
INSERT INTO @fileList (backupFile)
EXEC master.sys.xp_cmdshell @cmd;

-- Get DIFF backup filelist
SET @cmd = 'DIR /b "' + @backupPath + 'DIFF\"';
INSERT INTO @fileList (backupFile)
EXEC master.sys.xp_cmdshell @cmd;

-- Get LOG backup filelist
SET @cmd = 'DIR /b "' + @backupPath + 'LOG\"';
INSERT INTO @fileList (backupFile)
EXEC master.sys.xp_cmdshell @cmd;

-- Filter out NULL entries
DELETE FROM @fileList WHERE backupFile IS NULL;

-- Find latest FULL backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.bak'
AND backupFile LIKE '%_FULL_%'
AND backupFile LIKE '%' + @dbName + '%';

-- Build the FULL backup restore command
IF @lastFullBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
+ @backupPath + 'FULL\' + @lastFullBackup + ''' WITH NORECOVERY, REPLACE';
INSERT INTO @restoreCommands (cmd) VALUES (@cmd);
END

-- Find latest DIFF backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.bak'
AND backupFile LIKE '%' + @dbName + '%'
AND backupFile LIKE '%DIFF%';

-- Build the DIFF backup restore command if there is a DIFF backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
+ @backupPath + 'DIFF\' + @lastDiffBackup + ''' WITH NORECOVERY';
INSERT INTO @restoreCommands (cmd) VALUES (@cmd);
END

-- Check for log backups more recent than the DIFF
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE '%' + @dbName + '%'
AND (
( @lastDiffBackup IS NOT NULL AND RIGHT(LEFT(backupFile, LEN(backupFile) - 4), 15) > RIGHT(LEFT(@lastDiffBackup, LEN(@lastDiffBackup) - 4), 15) )
OR
( @lastDiffBackup IS NULL AND RIGHT(LEFT(backupFile, LEN(backupFile) - 4), 15) > RIGHT(LEFT(@lastFullBackup, LEN(@lastFullBackup) - 4), 15) )
);

OPEN backupFiles;

-- Build the LOG restore commands
FETCH NEXT FROM backupFiles INTO @backupFile;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
+ @backupPath + 'LOG\' + @backupFile + ''' WITH NORECOVERY';
INSERT INTO @restoreCommands (cmd) VALUES (@cmd);
FETCH NEXT FROM backupFiles INTO @backupFile;
END

CLOSE backupFiles;
DEALLOCATE backupFiles;

-- Now put the database in a working state
-- WITH RECOVERY
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY';
INSERT INTO @restoreCommands (cmd) VALUES (@cmd);

-- Display all of the restore commands for use
SELECT cmd FROM @restoreCommands;

Wednesday, September 21, 2022 - 11:09:59 AM - Prem Back To Top (90507)
I have full backups spread across files . How to accommodate and build restore script for database backups with multiple files.

Monday, October 26, 2020 - 9:39:33 AM - Greg Robidoux Back To Top (86695)
Hi Sokratis, can you post the scripts you are using so I have a better idea of how you are doing things.

Thanks

Sunday, October 25, 2020 - 1:11:47 PM - sokratis Back To Top (86693)
Hello and thank very much you for producing and sharing this script.

I am just testing it on my test server. If I run the baks and trns on the GUI I get the correct result.
When I run the script I still do not get the correct result.
I have taken the backups of the Northwind database (full recovery model) plus two log backups. I then edited the customers table at one row.
Tried to run the script partially to the point of the log backups with NORECOVERY but the state of the database is still in working state.
I have also tried first of course the full script but I do not get the previous state before editing.

I am somewhere wrong but I cannot figure it out.
Thanks
Sokratis

Thursday, April 9, 2020 - 11:41:24 AM - Greg Robidoux Back To Top (85314)

Hi David,

Looks like the path should include the ending "\".

-- 2 - Initialize variables
SET @dbName = 'Customer'
SET @backupPath = 'D:\SQLBackups\'

Does this still cause an issue?

Thanks
Greg


Thursday, April 9, 2020 - 11:02:31 AM - David Back To Top (85311)

Hi

Great script but +'\'+ missing from the path as it puts the path straight to the file name without the slash

Dave


Thursday, August 8, 2019 - 10:22:34 AM - Louis Duhon Back To Top (82002)

Thank you for this! Formed the basis of a restore tool we needed to develop in order to restore a vendor's db in our warehouse.


Tuesday, July 23, 2019 - 9:50:16 AM - Greg Robidoux Back To Top (81841)

Hi Mathieu,

you could try to add an ORDER BY to this part of the code.

DECLARE backupFiles CURSOR FOR 
   SELECT backupFile 
   FROM @fileList
   WHERE backupFile LIKE '%.TRN' 
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup
   ORDER BY backupFile  


Tuesday, July 23, 2019 - 3:34:42 AM - Mathieu Back To Top (81838)

Hi Greg !

Thank you very much for this wonderfull script! I'm using it successfully since more than a year now and I'm now experiencing a strange issue with a new repository for the backup. I was previously using a shared folder of our file server as repository for our SQL backup and I wanted to move it to our new StoreOnce appliance with CIFS share. It works great, but for an obscure reason it doesn't restore properly the logs files. Instead of restore the older -> newer it process the newer -> older. I'm completely stuck with this one ! No idea what could cause this behaviour! Maybe you have an idea what could cause this issue?

Thanks in advance

Best regards

Mathieu


Tuesday, May 8, 2018 - 8:38:29 AM - Matt Back To Top (75895)

Hi,

this script is almost working perfectly. I just have an issue because, when I start the restore, the dif backup is not already created.. So it won't be take at the end of the restor.. Below is my schedule for example:

Midnight : Full Backup
Every hours sinc 1:45am : Transaction Logs Backup
@ 6:15am : Differential Backup

The restore is scheduled to start @2am and will finish aproximatically @8:30am... And for now it only takes the transaction logs from 1:45am until 4:45am but not the rest :(

Do you have any idea how to improve that ?

Thanks in advance

Regards

Matt

 


Tuesday, March 13, 2018 - 11:45:13 PM - sandy Back To Top (75407)

 

 How to restore multiple database single .bak files. any script and query


Tuesday, March 13, 2018 - 11:42:08 PM - sandy Back To Top (75406)

 how to restore multiple database single .bak files( how to restore multiple databases at once)

 


Friday, March 2, 2018 - 11:46:57 AM - Yang Back To Top (75336)

 Hi Greg,

 

I think I have found the reason, they compressed the transaction files, I need to extract the file first, and then restore them. Thank you

 

 


Thursday, March 1, 2018 - 9:21:52 PM - Yang Back To Top (75332)

HI Greg,

I am using the following script to restore a full backup+ transaction backs.

These backup files are created from the same sql server 

USE [master]

RESTORE DATABASE [miadmfggp_live] FROM
DISK = N'C:\yardi_backup_v2\tmp\\extracted.bak0',
DISK = N'C:\yardi_backup_v2\tmp\\extracted.bak1',
DISK = N'C:\yardi_backup_v2\tmp\\extracted.bak2',
DISK = N'C:\yardi_backup_v2\tmp\\extracted.bak3',
DISK = N'C:\yardi_backup_v2\tmp\\extracted.bak4'
WITH  FILE = 1,NORECOVERY,  REPLACE
GO
RESTORE LOG [miadmfggp_live] FROM
DISK = N'C:\yardi_tran\\miadmfggp_live_TLog_TKDB1A11_201803010100.Lts.trn '
WITH  NORECOVERY
 RESTORE LOG [miadmfggp_live] FROM
DISK = N'C:\yardi_tran\\miadmfggp_live_TLog_TKDB1A11_201803010130.Lts.trn '
WITH  NORECOVERY
 RESTORE LOG [miadmfggp_live] FROM
DISK = N'C:\yardi_tran\\miadmfggp_live_TLog_TKDB1A11_201803010200.Lts.trn' 
WITH  NORECOVERY
RESTORE DATABASE miadmfggp_live
WITH RECOVERY
 
However, the full backup success, while the trans failed.
Processed 12083856 pages for database 'miadmfggp_live', file 'voy600822' on file 1.
Processed 2679 pages for database 'miadmfggp_live', file 'voy600822_log' on file 1.
RESTORE DATABASE successfully processed 12086535 pages in 1168.501 seconds (80.809 MB/sec).
Msg 3241, Level 16, State 0, Line 10
The media family on device 'C:\yardi_tran\\miadmfggp_live_TLog_TKDB1A11_201803010100.Lts.trn' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 10
RESTORE LOG is terminating abnormally.
 
Any suggestions?
 
Thanks
 
Yang

 


Thursday, March 1, 2018 - 3:37:05 PM - Greg Robidoux Back To Top (75330)

Hi Yang,

Take a look at this page:https://www.mssqltips.com/sqlservertutorial/105/how-to-get-the-contents-of-a-sql-server-backup-file/

and these values

SoftwareVersionMajor
SoftwareVersionMinor
SoftwareVersionBuild

These will tell what version of SQL Server was used to create the backup.

Then you can use this: https://www.mssqltips.com/sqlservertutorial/105/how-to-get-the-contents-of-a-sql-server-backup-file/

to check the version of SQL Server.


Thursday, March 1, 2018 - 2:52:00 PM - Yang Back To Top (75329)

 Hi Greg,

Is there any way to identify what version of SQL Server created the Trn files?

When I try to restore Full backup and a set of Transaction backups, I got the following error messages:

The media family on device 'C:\yardi_tran\\miadmfggp_live_TLog_TKDB1A11_201803010100.Lts.trn ' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 5
RESTORE LOG is terminating abnormally.

While the Full back restore successfully.

Processed 12083856 pages for database 'miadmfggp_live', file 'voy600822' on file 1.
Processed 2679 pages for database 'miadmfggp_live', file 'voy600822_log' on file 1.
RESTORE DATABASE successfully processed 12086535 pages in 1168.337 seconds (80.820 MB/sec).

 

Is that possible the Bak and Trn created from different SQL Servers?

 


Wednesday, February 28, 2018 - 9:35:23 AM - Greg Robidoux Back To Top (75315)

Hi Yang,

you would need to restore the full backup and the 48 transaction log backups.

-Greg


Tuesday, February 27, 2018 - 5:45:55 PM - Yang Back To Top (75308)

 Hello Greg,

If had a full backup restore on Sunday Midnight, and on Monday, we will receive 48 Tran log files.

My question is : On Monday midnight, is that OK to only restore the 48 Tran log files in order to Syncronize?

 

 


Wednesday, February 21, 2018 - 8:17:43 PM - Greg Robidoux Back To Top (75268)

Hi Yang,

Yes you have to start with a FULL backup for the restore process.

-Greg


Wednesday, February 21, 2018 - 8:00:48 PM - Yang Back To Top (75267)

 Hello Greg,

Thanks for your quick response. I would like to confirm if a Full backup store is a MUST HAVE step before the differential backup restore.

My situation is my local SQL SERVER database is read only, no INSERT,DELETE or UPDATE actions. In this situation, do I still need to have a full backup restore ?

 

Thank you

 

 

 


Wednesday, February 21, 2018 - 5:54:06 PM - Greg Robidoux Back To Top (75266)

Hi Yang,

when you are using differential backups and you want to get to the latest point in time with your restores, you just need to restore the last differential and then any transaction log backups that occured after that.

-Greg


Wednesday, February 21, 2018 - 5:49:45 PM - Yang Back To Top (75265)

 Hi, This is a extremely helpful example.

I still have a question about this, when we restore the differential backup, in this example, do we need to restore Customer_200809100315.DIF file as well?

 


Monday, October 30, 2017 - 2:05:21 PM - Greg Robidoux Back To Top (68975)

Hi Seshatheri,

It looks like all of the backups end with a .BAK.  Can you adjust the backup scripts to use different file extensions like BAK, DIF, TRN.

Also, the script above expects the files to all be in the same folder.  You would have to rewrite the above to handle the way the backup files are created.

Also, the above script is not expecting the server name.  You could change the database parameter you use to be "Servername_DBName" instead of just "DBNAME".

Sorry I don't have an easy fix for the way the files are stored.

-Greg


Thursday, October 19, 2017 - 4:16:02 PM - Seshatheri Back To Top (68551)

 Hi Greg,

 I am using Ola Hallengren backup script it is saving the backup files of full diff and tlog in the below hierarchy

D:\Backup\Servername\DB_Name\FULL\Servername_DBName_FULL_20171019_122739.bak

D:\Backup\Servername\DB_Name\DIFF\Servername_DBName_DIFF_20171019_124321.bak

D:\Backup\Servername\DB_Name\LOG\Servername_DBName_LOG_20171019_125631.bak

When i am trying to use your acript i provided the dbname and full backup location path like 'D:\Backup\Servername\DB_Name\FULL\'

But it showing output as RESTORE DATABASE [DB_Name] WITH RECOVERY only.

can you suggest what is the problem.

 


Thursday, July 13, 2017 - 10:19:49 AM - Greg Robidoux Back To Top (59316)

Hi Etienne,

the script out prints out the commands.

If you want to change the script to run the command add the following after each of the PRINT statements:

EXEC (@cmd)

 

-Greg


Thursday, July 13, 2017 - 7:26:16 AM - Etienne Muller Back To Top (59313)

Hi

 

When I try restoring over network path I only see print message nothing happens.  Busy testing the script to restore nightly. 

 

RESTORE DATABASE [TEST2012] FROM DISK = '\\Servername\TEST2012_backup_2017_07_15_124437_6598769.bak' WITH NORECOVERY, REPLACE

RESTORE DATABASE [TEST2012] WITH RECOVERY

Thank you


Friday, April 21, 2017 - 10:32:19 AM - Viktor Back To Top (55081)

 Great script, Greg!

A little update for Ola Hallengren backup files to show only .trn files older then last DIFF backup (to compare only numeric parts of file names):

DECLARE backupFiles CURSOR FOR  

  SELECT backupFile  

   FROM @fileList 

   WHERE backupFile LIKE '%.TRN'  

   --AND backupFile LIKE @dbName + '%' 

   AND RIGHT(LEFT(backupFile,LEN(backupFile)-4),15) > RIGHT(LEFT(@lastDiffBackup,LEN(@lastDiffBackup)-4),15)

 


Thursday, February 9, 2017 - 5:29:10 PM - Greg Robidoux Back To Top (46142)

Hi AJ,

This could be done, but it would require adding a few steps to the process.  First after identifying the full backup file you would need to do a RESTORE FILELISTONLY to get a list of the files in the backup, so you can find the logical names of the files. 

Then you would need to change this statement to include the MOVE option and add the logical name and the new physical file location:

SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = ''' 
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE, MOVE xxx'

You should only need to do this for the FULL backup restore.  This will create the base for the other restores and the other files should restore to the new file location.


Thursday, February 9, 2017 - 4:49:55 PM - AJ Back To Top (46140)

Great script. I'm trying to get it to work with the "with move" option on  the data files toa new location to restore on a different server.  Any ideas?

 


Friday, January 20, 2017 - 11:19:51 AM - Ira Williams Back To Top (45445)

Hi Greg, 

Thanks for the quick response and suggestion on the multiple file paths.

 

I did make my backup extension .DIFF as that's just what I'm personally use to. Weirdly enough, it appears that Hallengren scripts output DIFF backups as .bak so I'm also working through that issue as to how to get it to output a .DIFF. For now, I've just manually changed the file extensions for testing purposes since I'm just working on a test system.

 


Friday, January 20, 2017 - 9:11:54 AM - Greg Robidoux Back To Top (45442)

Hi Ira,

One simple change would be to do separate DIR calls for each directory as follows:

-- get Fulls
SET @backupPath = 'D:\SQLBackups\Fulls\'

SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- get Diffs
SET @backupPath = 'D:\SQLBackups\Difs\'

SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- get Logs
SET @backupPath = 'D:\SQLBackups\Logs\'

SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

 

Is file exentsion for your DIF backups DIF or DIFF? 


Thursday, January 19, 2017 - 5:51:30 PM - Ira Williams Back To Top (45428)

 

I modified the code some due to the way my file names and extensions are. The script seems to be ignoring my DIFF backups though. Also, If I keep all my backups in separate directories (FULL, DIFF, LOG) would I have to move them to the same directory or is there a way to modify the script for that?

USE Master; 

GO  

SET NOCOUNT ON 

 

-- 1 - Variable declaration 

DECLARE @dbName sysname 

DECLARE @backupPath NVARCHAR(500) 

DECLARE @cmd NVARCHAR(500) 

DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 

DECLARE @lastFullBackup NVARCHAR(500) 

DECLARE @lastDiffBackup NVARCHAR(500) 

DECLARE @backupFile NVARCHAR(500) 

 

-- 2 - Initialize variables 

SET @dbName = 'copia' 

SET @backupPath = 'H:\Copia\Backups\INWS0744\Copia\FULL\' 

 

-- 3 - get list of files 

SET @cmd = 'DIR /b "' + @backupPath + '"'

 

INSERT INTO @fileList(backupFile) 

EXEC master.sys.xp_cmdshell @cmd 

 

-- 4 - Find latest full backup 

SELECT @lastFullBackup = MAX(backupFile)  

FROM @fileList  

WHERE backupFile LIKE '%.BAK'  

   AND backupFile LIKE '%' + @dbName + '%' 

 

SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  

       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE' 

PRINT @cmd 

 

-- 4 - Find latest diff backup 

SELECT @lastDiffBackup = MAX(backupFile)  

FROM @fileList  

WHERE backupFile LIKE '%.DIFF'  

   AND backupFile LIKE '%' + @dbName + '%' 

   AND backupFile > @lastFullBackup 

 

-- check to make sure there is a diff backup 

IF @lastDiffBackup IS NOT NULL 

BEGIN 

   SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  

       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY' 

   PRINT @cmd 

   SET @lastFullBackup = @lastDiffBackup 

END 

 

-- 5 - check for log backups 

DECLARE backupFiles CURSOR FOR  

   SELECT backupFile  

   FROM @fileList 

   WHERE backupFile LIKE '%.TRN'  

   AND backupFile LIKE '%' + @dbName + '%'

   AND backupFile > @lastFullBackup

 

OPEN backupFiles  

 

-- Loop through all the files for the database  

FETCH NEXT FROM backupFiles INTO @backupFile  

 

WHILE @@FETCH_STATUS = 0  

BEGIN  

   SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''  

       + @backupPath + @backupFile + ''' WITH NORECOVERY' 

   PRINT @cmd 

   FETCH NEXT FROM backupFiles INTO @backupFile  

END 

 

CLOSE backupFiles  

DEALLOCATE backupFiles  

 

-- 6 - put database in a useable state 

SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY' 

PRINT @cmd


Tuesday, August 30, 2016 - 4:04:34 PM - Greg Robidoux Back To Top (43227)

Hi Mike,

I used the timestamp in the filename, because this is how Maintenance Plan backups name the file and I figured most people followed this naming convention rule.

It would be possible to read more from the file system to get the dates of the files.  This would require a few more changes in the script to pull in this data and then parse the data correctly.

Anyway this is done, you see how important having consistent rules are to make sure you are getting the correct files.

-Greg


Tuesday, August 30, 2016 - 3:32:50 PM - Mike Back To Top (43226)

Greg,

 

         I understand you have the prerequisite for the file name with a timestamp on it for your script.  Why not use the OS file timestamp? Is it possible?

I mean, if one Tlog file is named a.trn, the second one is named as b.trn.  It will be better for the script to tell b is after a because of their file os timestamp. In practice, different companies may have different name convention for their backup files.  I understan it will make the script more complicated, ie more of your development time.   I also did some test, if my backup file use your file extention rule but not use your file name rule, then it will totally ignore the .dif differential backup, only go with Full and Tlog backup.   If I have 4 Tlogs, for instance, Tlog1.trn, Tlog2.trn, Tlog3.trn, Tlog4.trn, if I change the Tlog2.trn to Tlog99.trn, then the script will generate the wrong restoring order.  Thanks a lot for your attention.   Overall this is still a great script to use.


Tuesday, August 30, 2016 - 3:11:19 PM - Mike Back To Top (43225)

Hi Greg,

 

           I saw your example includes the statement for Diff restore, however when I did the experiement on my test server AdventureWorks2014 DB, it seems to completely ignore the Diff backup which is with .dif as required.  It only show statements with Full and Tlog restore.  I tested a few times, it is still the case.  Not sure where I did wrong or the script may not work as expected.  Thanks again for this great script.

 


Thursday, May 26, 2016 - 10:51:57 PM - Yoga Back To Top (41573)

Hi Greg, please ignore my last comment before it.

i have modified your script to be this below script, it work very well for me, but the weakness is the restore log will restore all the log file in directory, it would take more longer time to restore log, the question is, how to make the restore log is only restore the log which didnt restored before in this database?

Thank You

 

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @standbydir NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'yogalogmanual'
SET @backupPath = 'K:\shared240\logmanual\'
SET @standbydir = 'K:\shared240\logmanual\yogarollback_01.bak'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.bak'
   AND backupFile LIKE @dbName + '%'

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = N'''
       + @backupPath + @lastFullBackup + ''' WITH FILE = 1,  STANDBY = N''' + @standbydir + ''''
EXEC (@cmd)


-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.trn'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = N'''
       + @backupPath + @backupFile + ''' WITH FILE = 1, STANDBY= ''' + @standbydir + ''''
   EXEC (@cmd)
   FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles
 


Thursday, May 26, 2016 - 9:47:31 PM - Yoga Back To Top (41572)

Hi Greg,

1. in directory there are 1 full backup, 2 log file, i've deleted myDB on sqlserver, and run your script --> work great, 1 full backup and 2 log file have been restored.

2. i've added 1 log file in there directory, so i have 1 full backup, 3 log file. i made restore full backup script to be command, so it supposed to restore just log file. but when i ran it, the result is this below.

Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 32000000010600001, which is too early to apply to the database. A more recent log backup that includes LSN 32000000012600001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'yogamanual', file 'yogamanual' on file 1.
Processed 2 pages for database 'yogamanual', file 'yogamanual_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.057 seconds (0.171 MB/sec).
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 32000000016400049, which is too recent to apply to the database. An earlier log backup that includes LSN 32000000012600001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

there are something wrong?

 

Thank you.


Thursday, May 26, 2016 - 9:40:33 AM - Greg Robidoux Back To Top (41569)

Hi Yoga,

What error do you get?


Thursday, May 26, 2016 - 8:22:58 AM - Yoga Back To Top (41568)

Hi Greg,

Thanks for your response, i have use () and now work great.

but the question is, when i have restore full backup DB, and restore log1 and log 2,

when i backed up log3, and i want to restore it using above script, it goes error.

is it supposed to happen or there is some mistake that i've done?

honestly, i really need expert help like you, to make automatic restore log.

Thank you Greg.


Thursday, May 26, 2016 - 7:20:40 AM - Greg Robidoux Back To Top (41567)

Hi Yoga,

you need to put parentheses around the @cmd like the following

EXEC (@cmd)

 


Thursday, May 26, 2016 - 3:01:28 AM - Yoga Back To Top (41564)

 Hi Greg,

 

i have try your script, works great with output PRINT,

but how to execute that sql command RESTORE?

i have tried with change PRINT with EXEC, as like EXEC @cmd

but it showed me error like this below

Msg 2812, Level 16, State 62, Line 64
Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK = 'D:\shared241\yogamanual-0954.trn' WITH NORECOVERY'.

am i wrong? what i supposed to do to make this sql command works?

 

Thank you.

Yoga


Thursday, March 17, 2016 - 8:06:44 AM - Greg White Back To Top (40969)

Just released my initial version to work with Ola's scripts. Thanks for the Base code Greg.

Features:

  • Restores include file groups
  • speficy new data / log paths
  • include transaction logs
  • writes to Ola's command log table --> great for restore time reports
  • option to run a dbcc checkdb following restore

still loads to do, your welcome to contribute:
https://github.com/GregWhiteDBA/MSSQLAutoRestore

 

 


Thursday, March 3, 2016 - 9:59:44 AM - Greg Robidoux Back To Top (40853)

Hi Al,

Thanks for your input. That's true the script does not check to see if all of the backup files exist or check the order of the LSNs. So if a transaction log file is missing or if the naming convention of the files does not sort the files in the correct order, the restore could fail.

Maybe a good enhancement would be to get a list of all of the files, then do a RESTORE HEADERONLY on each file and then assemble the restore script based on the LSNs.

-Greg


Thursday, March 3, 2016 - 9:33:52 AM - al Back To Top (40851)

I think, this script can be handy if you are not in full-recovery and you do not need the transaction log... in fact, this script doesn't check the LSN's therefore, it can lead to a failure when restoring the transaction logs.  

 


Thursday, October 15, 2015 - 4:35:47 PM - Joel Barrios Back To Top (38911)

Just a slight variation of the super helpful script above, which basically would allow you to test your full, differential and t-log backups on weekly basis by simply restoring your detabase to another database with a different name.

 

USE Master;
GO
SET NOCOUNT ON

-- DECLARE YOUR VARIABLES --

DECLARE @DBName sysname                            -- ORIGINAL DATABASE NAME --
DECLARE @RestoredDBName sysname                        -- NAME FOR THE RESTORED DATABASE --
DECLARE @BackupPath NVARCHAR(500)                    -- LOCATION OF THE BACKUP FILES --
DECLARE @TSQL NVARCHAR(1000)
DECLARE @FileList TABLE (BackupFile NVARCHAR(255))
DECLARE @LastFullBackup NVARCHAR(500)
DECLARE @LastDifferentialBackup NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)

-- INITIALITE YOUR VARIABLES --
SET @DBName = 'dbAdmissions'
SET @RestoredDBName =@DBName + '_Restored'
SET @BackupPath = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\' +@DBName + '\'

-- GET THE LIST OF THE FOLDER FILES --
SET @TSQL = 'DIR /b "' + @BackupPath + '"'

INSERT INTO @FileList(BackupFile)
EXEC master.sys.xp_cmdshell @TSQL

SET @LastFullBackup = (SELECT TOP 1 BackupFile FROM @FileList
WHERE BackupFile LIKE '%.BAK' AND BackupFile LIKE @DBName + '%'
ORDER BY BackupFile DESC)

-- BUILD THE FULL BACKUP RESTORE T-SQL --
SET @TSQL = 'RESTORE DATABASE ' +@RestoredDBName + ' FROM DISK = '''
       + @BackupPath + @LastFullBackup + ''' WITH  NORECOVERY, MOVE N''' +@DBName + ''' TO N''' +
        @BackupPath +@RestoredDBName + '.mdf'', MOVE N''' + @DBName + '_log'' TO N''' +
        @BackupPath +@RestoredDBName + '.ldf'''
PRINT @TSQL

-- FIND THE LATEST DIFFERENTIAL BACKUP --
SELECT @LastDifferentialBackup = MAX(BackupFile)
FROM @FileList
WHERE BackupFile LIKE '%.DIF' AND BackupFile LIKE @DBName + '%' AND BackupFile > @LastFullBackup

-- IF THE VARIABLE ABOVE IS NOT NULL, THEN BUILD THE DIFFERENTIAL BACKUP RESTORE T-SQL --
IF @LastDifferentialBackup IS NOT NULL
BEGIN
   SET @TSQL = 'RESTORE DATABASE ' +@RestoredDBName + ' FROM DISK = '''
       + @BackupPath + @LastDifferentialBackup + ''' WITH NORECOVERY'
   PRINT @TSQL
   SET @LastFullBackup = @LastDifferentialBackup
END

-- CHECK FOR THE EXISTING T-LOG BACKUPS --
DECLARE BackupFilesCursor CURSOR FOR
   SELECT BackupFile
   FROM @FileList
   WHERE BackupFile LIKE '%.TRN' AND BackupFile LIKE @DBName + '%' AND BackupFile > @LastFullBackup

OPEN BackupFilesCursor

-- LOOP THROUGH ALL THE FILENAMES PREVIOUSLY STORED IN THE DATABASE AND BUILD THE T-SQL FOR EACH---
FETCH NEXT FROM BackupFilesCursor INTO @BackupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @TSQL = 'RESTORE LOG ' +@RestoredDBName + ' FROM DISK = '''
       + @BackupPath + @BackupFile + ''' WITH NORECOVERY'
   PRINT @TSQL
   FETCH NEXT FROM BackupFilesCursor INTO @BackupFile
END

CLOSE BackupFilesCursor
DEALLOCATE BackupFilesCursor

-- PUT THE DATABASE IN A USABLE STATE --
SET @TSQL = 'RESTORE DATABASE ' +@RestoredDBName + ' WITH RECOVERY'
PRINT @TSQL


Friday, April 10, 2015 - 4:43:11 PM - Josh Clark Back To Top (36893)

THIS was a HUGE help! Worked like a charm, thank you SO much!


Wednesday, March 11, 2015 - 10:19:43 AM - Myong Back To Top (36487)

Thanks.  Works great.

Added double quotes for the directory list command to account for spaces in directory names.

 

SET @cmd = 'DIR /b "' + @backupPath + '"'

 

Tuesday, January 27, 2015 - 6:11:23 PM - Tim Abell Back To Top (36079)

Thanks! Used to help create this https://gist.github.com/timabell/d43b21f889bd9dada355


Tuesday, January 27, 2015 - 2:02:58 PM - Bill Hughes Back To Top (36073)

Good afternoon,

I am trying to run this script and pull a backup from a mapped drive. However when the script completes, I only get the last @cmd printed. I don't get the RESTORE DATABASE FROM DISK print out. Is it because it is a mapped drive? If I point it to the local C: drive it works fine.


Wednesday, January 21, 2015 - 2:33:21 PM - Daniel Braga Back To Top (36014)

Hi Greg.

Thank you very much!! I used your script with success!!!!

regards!

 

Daniel Braga, from São Paulo - Brazil.


Wednesday, December 17, 2014 - 11:05:43 AM - Ed Back To Top (35641)

Thanks Greg,

I am going to automated as scheduled job with your script and Automated FTP.   The only line I added to your code is:  

ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE.     Thanks!


Wednesday, December 17, 2014 - 10:06:25 AM - Greg Robidoux Back To Top (35638)

Hi Ed,

Basically log shipping is just doing backups, moving the backups and restoring the backups on another server.  There are several ways you can do this.  Ideally you would want to automate the process.

-Greg


Wednesday, December 17, 2014 - 8:27:33 AM - Ed Back To Top (35635)

Greg,   this script is a perfect solution for maintaining a warm backup, providing a "close to" high availability database.   Because my server is located on a different server in AWS the Prod is in Azure logshipping feature in SQL got just too complicated.   With your script and simply moving the files over to AWS how does this differ from log shipping.   I feel that I am getting the same thing.   Thanks it works perfectly!!!!


Thursday, December 4, 2014 - 5:40:45 PM - Greg Robidoux Back To Top (35516)

Hi Ray,

you will have to incorporate variables for different drives as well as add the MOVE option to the restore commands.

It shouldn't be that hard to do, but there will be several areas you will need to make this change.

I'll see if I can put something together, but not sure when I can get to it.

-Greg


Thursday, December 4, 2014 - 1:25:41 PM - Ray Back To Top (35511)

I really like this script! However I ran into a problem with the restore.  I think the problem has to do with the restore file path.  I am restoring the SQL BAK file to a different SQL server which has a different file stucture.  The old SQL server has the MDF & LDF files located in the G drive, the target SQL server has the MDF file located in the G drive and the LDF located in the I drive.

My goal is to use this script to automate the process of copying the SQL BAK files from our NAS location to the target server.  How do I modify your script to restore the MDF and LDF files to the new location so that I can automate this process?


Sunday, September 21, 2014 - 2:47:42 AM - Kartar Rana Back To Top (34643)

Great script. Did save me a lot of time when i had to break mirroring for a maintenance activity and resume with restoring log backups for 24hrs


Thursday, June 5, 2014 - 1:45:57 PM - Olga Back To Top (32100)

If you don't want to enable xp_cmdshell, use xp_dirtree instead.

The declaration for the table variable will require two extra fields

DECLARE @fileList TABLE (backupFile NVARCHAR(255), depth smallint, [file] smallint)  

And the query to collect the file list

INSERT @fileList
  EXEC master.sys.xp_dirtree @backupPath, 0, 1

The rest of the code remains the same


Monday, March 24, 2014 - 5:59:24 PM - Quintin Back To Top (29857)

This doesn't seem to work if your databases starts with "!" (exclamation point).


Wednesday, November 27, 2013 - 2:47:04 PM - Somalaraju Back To Top (27630)

Thanks Alot for such a detailed explanation.


Wednesday, November 13, 2013 - 11:30:57 AM - CJ Morgan Back To Top (27486)

Thanks Greg for the explanation which makes total sense.  Must have missed that part in the article...reading too fast for the brain to absorb. :-)


Thursday, November 7, 2013 - 4:58:44 PM - Jason Carter Back To Top (27443)

I've modified a copy of this to work with the output of Ola Hallengren's backup scripts.

Can be found here:  http://jason-carter.net/professional/restore-script-from-backup-directory-modified.html


Thanks for the solid base to work with Greg!

 


Wednesday, October 30, 2013 - 1:19:50 PM - Greg Robidoux Back To Top (27340)

Hi CJ,

 

The code was designed this way to generate the scripts and not actually run the restore commands.  This way you can review the code to make sure there are no issues before you begin the actual restore.

You can change this line of code if you want to actually have this run the restore as well.

You can add the EXEC (@cmd) line into the code if you want to this to run automatically:

WHILE @@FETCH_STATUS = 0 
BEGIN 
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = ''' 
       + @backupPath + @backupFile + ''' WITH NORECOVERY'
   --PRINT @cmd
   EXEC (@cmd)
   FETCH NEXT FROM backupFiles INTO @backupFile 
END


Wednesday, October 30, 2013 - 12:01:40 PM - CJ Morgan Back To Top (27337)

So I am running this on SQL 2012 Enterprise RTM and I don't get an error but the database doesn't restore.

I'm restoring AdventureWorks.  I have a full backup, t-log, dif, t-log all with the correct extensions in a folder called C:\Backups\AdventureWorks.

The only message I get is the commands listed but they don't actually run.  I can copy/paste these into another query window and they run without error.  Just wondering why it won't execute within the script.


RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Backups\AdventureWorksAdventureWorks_2013_10_30_081351_5912121.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Backups\AdventureWorksAdventureWorks_2013_10_30_081634_4794993.dif' WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK = 'C:\Backups\AdventureWorksAdventureWorks_2013_10_30_081654_2654777.trn' WITH NORECOVERY
RESTORE DATABASE AdventureWorks WITH RECOVERY


Thursday, October 24, 2013 - 3:20:25 AM - KK Back To Top (27254)

This is really cool guys, it is useful to keep the deve/test/uat environments in sync with production data..

can some one please suggest a similar idea when I need to sync a UAT database with a backup from production where SQL replication is setup in UAT(I don't want to break replication every time by restoring backups)

Please suggest any links that involve intelligent data sync.

Thanks,

KK


Wednesday, September 25, 2013 - 11:37:35 PM - Balaji Back To Top (26955)

Thanks Greg, Could you please provide the scripts for the same.


Wednesday, September 25, 2013 - 4:27:19 PM - Greg Robidoux Back To Top (26949)

@Balaji - you could turn this into a Stored Procedure and pass in database name as a parameter this way you can call this for as many databases as needed.


Wednesday, September 25, 2013 - 10:19:58 AM - Balaji Back To Top (26936)

Hi Greg,

The above script is explained for only one database, but I need to automate for multiple databases. How to proceed.

--Balaji.


Wednesday, September 18, 2013 - 10:18:47 AM - Allen McGuire Back To Top (26844)

I prefer to leave this sort of thing up to the SQLSafes and LiteSpeeds - with SQLSafe I simply chose my database to restore, choose what to restore from (perhaps a production database on an entirely different server), a point-in-time, and click "Go".  I can also create auto-restore policies - they work like a charm and save me a lot of time (time=$$=cost justification).

For the instances I don't use SQLSafe (some development environments), this is handy - thanks for your hard work!


Thursday, September 12, 2013 - 7:09:12 AM - Greg Robidoux Back To Top (26758)

Glenn - one simple way to do what you need is to create a new variable @LogBackupPath and set this value to the location for your log files.  And then change this part of the code:

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @backupFile  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''  + @backupPath + @backupFile + ''' WITH NORECOVERY'
  PRINT @cmd

  FETCH NEXT FROM backupFiles INTO @backupFile  
END 

To this

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @backupFile  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''  + @LogBackupPath + @backupFile + ''' WITH NORECOVERY' 
  PRINT @cmd

  FETCH NEXT FROM backupFiles INTO @backupFile  
END 

 

This is the easy way.  You could also change to code to read the system backup tables to get the path for each backup file.


Wednesday, September 11, 2013 - 6:14:08 PM - Glenn Back To Top (26747)

I like this, but I have 4 transaction log  backups every hour in a different location than my full backkup. Is there an easy include the TLogs in the script. BTW thanks to @brento for pointing his out.


Monday, July 1, 2013 - 7:59:12 PM - Greg Robidoux Back To Top (25665)

@dba.sql29 - can you post what you are seeing and what you should be seeing so we can figure out if a change is needed for the script.

Thanks


Monday, July 1, 2013 - 6:52:03 PM - dba.sql29 Back To Top (25664)

Hi ,

 

Thank you very much for the script but when I am just using the full backup part its not working and just showing in the message name of the backup fine can you please eleabrate a little bit more when need to be changed or what are thing we have to look or tweek in the script?

I am using a network path instead of local drive 

 

thank you very much for your help

 


Wednesday, January 30, 2013 - 4:54:40 PM - Tara Back To Top (21806)

This was really helpful for me, I was looking for a way to automating restoring a database from the backup files we have on NAS.  The one problem I ran into with this script was that it didn't actually look at the time the file was created. With some fairly large databases, occasionally a transaction log backup job will run during the same time our full backup job is running.  Using the file names, the script picks up that TRN file that's been created while the BAK is running.  But ,really that log is for the previous full backup.  So I expanded on the idea and instead of just the basic name of the files in the folder I included the date/time the file was created.  Then I expaned the I parsed out the date time and the name to a two column table.  That way when I picked the most recent BAK I could choose it by created date. Then I choose TRN files created after the BAK was created.  Just helps when you might have some funky things going on between the creation time and the naming of the files.  Happy to share not pretty but works.

DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 

DECLARE @cmd as varchar(1000)

Declare @backuppath as varchar(1000)

DECLARE @lastFullBackup NVARCHAR(500) 

DECLARE @backupFile NVARCHAR(500) 

DECLARE @dbName sysname 

DECLARE @DataName         varchar (255),

@LogName          varchar (255),

@LogicalName      varchar(255), 

@PhysicalName     varchar(255),

@PhysicalFileData varchar(255),

@PhysicalFileLog varchar(255),

@cmd2 as varchar(1000)  

 

set nocount on

 

SET @backupPath = '\\192.168.0.2\Storage\SQLBackups\dbxx\'

SET @cmd = 'DIR ' + @backupPath + ' | FIND "/"'

Set @dbName = 'dbxx'

 

INSERT INTO @fileList(backupFile) 

EXEC master.sys.xp_cmdshell @cmd 

 

select * into #tmp

from @fileList

 

delete #tmp where backupFile like '%dir%'

 

select cast(left(backupFile,CHARINDEX('M',backupfile)) as datetime)as created,

substring(backupfile,CHARINDEX('TMW',backupFile),100) as filename

into #tmp2

from #tmp

 

select @lastFullBackup =  filename from #tmp2 where created in(

select MAX(created) from #tmp2

where RIGHT(filename,3) = 'bak')

 

 

create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

                                    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

                                    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID  varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

 

SELECT @CMD = 'RESTORE FILELISTONLY FROM disk = ''' + @backupPath + '\' + @lastFullBackup + '''' 

insert #filelist exec ( @cmd )

 

 

 

SELECT @DataName =  LOGICALNAME FROM #filelist WHERE TYPE = 'D'

SELECT @LOGName =  LOGICALNAME FROM #filelist WHERE TYPE = 'L'

 

select @PhysicalFileData = reverse(substring(reverse(rtrim(PhysicalName)),1,patindex('%\%',reverse(rtrim(PhysicalName)))-1 )) 

from #filelist where type = 'D'

 

select @PhysicalFileLog = reverse(substring(reverse(rtrim(PhysicalName)),1,patindex('%\%',reverse(rtrim(PhysicalName)))-1 )) 

from #filelist where type = 'D'

 

SET @CMD = 'RESTORE DATABASE ' + 'dbxx' + ' FROM DISK = '''  

       + @backupPath + @lastFullBackup + ''' WITH FILE = 1,'

       + ' MOVE ''' + @DATAName + ''' TO ''' +'D:\SQLDATA\' + @physicalfiledata +'''  , '

       + ' MOVE ''' + @LogName + ''' TO ''' +'D:\SQLLOGS\' + @physicalfileLOG +''',' 

       + ' REPLACE, STATS = 10, NORECOVERY'

       

PRINT @cmd 

 

DECLARE backupFiles CURSOR FOR  

 

SELECT filename  

FROM #tmp2

where created > (

select created from #tmp2

where [filename] = @lastFullBackup)

order by created

 

OPEN backupFiles  

 

 

-- Loop through all the files for the database  

FETCH NEXT FROM backupFiles INTO @backupFile  

 

WHILE @@FETCH_STATUS = 0  

BEGIN  

   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''  

       + @backupPath + @backupFile + ''' WITH NORECOVERY' 

   PRINT @cmd 

   FETCH NEXT FROM backupFiles INTO @backupFile  

END 

 

CLOSE backupFiles  

DEALLOCATE backupFiles  

 

 

---- 6 - put database in a useable state 

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH NORECOVERY' 

PRINT @cmd 

 

 

drop table #tmp

drop table #tmp2

drop table #filelist

 

Monday, December 24, 2012 - 2:55:46 PM - Paul brewer Back To Top (21127)

http://paul.dynalias.com/SQL/SiteAssets/Lists/Posts/AllPosts/sp_RestoreScriptGenie.txt

 


Tuesday, December 11, 2012 - 2:38:04 PM - farhan Back To Top (20885)

Dear author,

I am using your script it runs fine show message

RESTORE DATABASE XYZ WITH RECOVERY

 

but unable to restore the backup for directory my backup file name below in the same directory  'D:\SQLBackups\'

 

Backups_20121211140724.bak

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'test'
SET @backupPath = 'D:\SQLBackups\'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup

-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd


Wednesday, November 7, 2012 - 9:52:33 AM - Greg Robidoux Back To Top (20244)

@Joerg - you could replace the PRINT @cmd statements with EXEC (@cmd) and this would execute the statements instead of just showing the command.


Wednesday, November 7, 2012 - 3:05:06 AM - Joerg Back To Top (20241)

Thanks for the script it works like charm.

I get what I need but is it possible to execute the created scripts instead of sending the commands to the message window. This would completely automate the database import into our test system every night.


Joerg


Tuesday, September 18, 2012 - 2:59:11 AM - Rob Back To Top (19545)

THANKS..  At 2AM this morning this worked like a dream.  Saved me manually select over 30 TRN files and restoring one at a time with the GUI :-)

 


Monday, September 3, 2012 - 6:25:51 AM - Greg Robidoux Back To Top (19372)

@Naveen - what version of SQL Server are you using?  I just tried this code and this works fine:

DECLARE @fileList TABLE ([dir] varchar(1000))
INSERT INTO @fileList(dir)
EXEC master.sys.xp_cmdshell 'dir c:'
SELECT * FROM @fileList


Sunday, September 2, 2012 - 1:43:00 AM - Naveen Back To Top (19367)

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

Error

EXECUTE cannot be used as a source when inserting into a table variable.

 

Could you please tell me why ?


Monday, August 20, 2012 - 9:09:06 AM - Jerry L. Cutshaw Back To Top (19125)

I've been looking for a robust restore script like this that will will work with the backup scripts created by Ola Hallengren http://ola.hallengren.com/.  Has anyone found a way to make this work?


Thursday, May 10, 2012 - 3:22:15 AM - Jahirul Hassan Back To Top (17395)

Solved through following code.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

 

 

 

 

 

 


Thursday, May 10, 2012 - 2:41:52 AM - Jahirul Hassan Back To Top (17394)

I run the script, It shows the following message:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
 
RESTORE DATABASE [PromotionalExpenseDB] WITH RECOVERY


Thursday, December 1, 2011 - 3:19:08 PM - Ankit Shah Back To Top (15266)

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, December 1, 2011 - 3:00:28 PM - Scott C Back To Top (15265)

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 1, 2011 - 2:03:52 PM - Ankit Shah Back To Top (15263)

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)
60                                 RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\ADW1.trn' WITH NORECOVERY
61                                 RESTORE LOG AdventureWorks FROM DISK = 'C:\Temp\ADW1.trn' WITH NORECOVERY
999999999                    RESTORE DATABASE AdventureWorks WITH RECOVERY


Thursday, December 1, 2011 - 12:53:08 PM - Scott C Back To Top (15262)

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 1, 2011 - 12:32:48 PM - Ankit Shah Back To Top (15261)

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


Wednesday, November 30, 2011 - 4:53:40 PM - Scott C Back To Top (15255)

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.


Wednesday, November 30, 2011 - 4:47:51 PM - Scott C Back To Top (15254)

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.
The backup files must still be in their original locations.
The backup filenames should be complete UNC paths.  Relative paths, mapped drives, or relying on the default backup folder setting will probably cause problems.
The folders containing the backup files must be readable by both SQL Server service accounts.
The destination folders for the restored files may be hardcoded, but the script includes code to get the DefaultDataDir and DefaultLogDir settings from the registry.  Obviously if these have not been set correctly then this section should be commented out.

/*  Restore a database backup from a different server
    Assumes both servers are at least SQL 2005.
    Use latest full and diff backups, and all subsequent log backups.
    Handles relocating all database files to correct local folders.  All data files and
        full-text files go to one folder, log files to another.
    Will overwrite an existing database of the same name.
    Does not handle complex backup scenarios such as multi-file striped backups,
        partial backups, or file/filegroup backups.
    Adjusts the database compatibility level when restoring to a later version.
    Assumes all relevant backup files are still online in their original locations.
    Assumes the backup filenames are valid and accessible from both servers.
    Assumes full backup filenames are found in msdb.dbo.backupmediafamily, not
        temporary backup device names created by some backup tools.
    The backup files may be spread around in different folders, if the above
        assumptions are true.
    Possible extension: use "EXEC sys.xp_fileexist" to check that all required
        files are available before starting the first RESTORE.
*/
:SETVAR SOURCE_SERVER SomeServer
:SETVAR DBNAME SomeDatabase
-- Create a temporary linked server named SourceServer to get backup history
-- Assumes the current user can login to the other server and read msdb
IF EXISTS(SELECT NULL FROM sys.servers WHERE is_linked = 1 AND name = 'SourceServer')
    EXEC sys.sp_dropserver @server = N'SourceServer', @droplogins = 'droplogins' ;
EXEC master.dbo.sp_addlinkedserver @server = N'SourceServer', @srvproduct=N'SQL_Server', @provider=N'SQLNCLI', @datasrc=N'$(SOURCE_SERVER)' ;
-- Assumes login will authenticate to the remote server (if Kerberos is configured correctly),
--  otherwise a SQL login will have to be provided in @rmtuser and @rmtpassword
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SourceServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL ;
EXEC master.dbo.sp_serveroption @server=N'SourceServer', @optname=N'collation compatible', @optvalue=N'false' ;
EXEC master.dbo.sp_serveroption @server=N'SourceServer', @optname=N'data access', @optvalue=N'true' ;
EXEC master.dbo.sp_serveroption @server=N'SourceServer', @optname=N'rpc', @optvalue=N'false' ;
EXEC master.dbo.sp_serveroption @server=N'SourceServer', @optname=N'rpc out', @optvalue=N'false' ;
GO
-- Decide where to put the restored database
DECLARE @DefaultDataDir VARCHAR(1000), @DefaultLogDir VARCHAR(1000)
-------------------------------------------------------------------------------------------------------
-- The destination data and log folders can be set manually
SET @DefaultDataDir = 'D:\SqlData' ;
SET @DefaultLogDir = 'E:\SqlLogs' ;
-------------------------------------------------------------------------------------------------------
-- Or the default locations can be obtained from the registry
-- Assumes these instance properties have been set correctly
DECLARE @ServerName SYSNAME,
    @RegRootPath VARCHAR(250),
    @InstanceKeysPath VARCHAR(250),
    @InstanceKeyPath VARCHAR(250),
    @HKLM VARCHAR(20);
SET @HKLM = 'HKEY_LOCAL_MACHINE' ;       
SET @RegRootPath = 'SOFTWARE\Microsoft\Microsoft SQL Server\' ;
SET @InstanceKeysPath = @RegRootPath + 'Instance Names\SQL' ;
-- get the instance name. If default, use MSSQLSERVER
SET @ServerName = ISNULL(CAST(SERVERPROPERTY('InstanceName') AS VARCHAR), 'MSSQLSERVER') ;
-- get the path for this instance
EXECUTE master..xp_regread @HKLM, @InstanceKeysPath, @ServerName, @InstanceKeyPath OUTPUT ;
SET @InstanceKeyPath = @RegRootPath + @InstanceKeyPath + '\MSSQLServer'
-- read the directory locations
EXECUTE master..xp_regread @HKLM, @InstanceKeyPath, 'DefaultData', @DefaultDataDir OUTPUT ;
EXECUTE master..xp_regread @HKLM, @InstanceKeyPath, 'DefaultLog', @DefaultLogDir OUTPUT ;
-------------------------------------------------------------------------------------------------------
/* If a separate folder is desired for a database with multiple data files, or to avoid filename conflicts:
SET @DefaultDataDir = @DefaultDataDir + '\$(DBNAME)' ;
-- Create a subfolder for the data files (no error occurs if it already exists)
EXEC sys.xp_create_subdir @DefaultDataDir ;
*/
DECLARE @cmd NVARCHAR(MAX) ;
DECLARE @LastBackup DATETIME ;
-- Initial restore of full backup, including MOVE clauses
SELECT TOP 1 @LastBackup = bs.backup_start_date,
        @cmd = N'RESTORE DATABASE [$(DBNAME)] FROM DISK=N''' + mf.physical_device_name
            + ''' WITH FILE=' + LTRIM(bs.position) + ', NORECOVERY, REPLACE, STATS=10'
        + ( SELECT    ', MOVE ''' + name + ''' TO '''
                    + CASE type WHEN 1 THEN @DefaultLogDir ELSE @DefaultDataDir END
                    + RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name))) + ''''
            FROM [SourceServer].master.sys.master_files
            WHERE database_id = (SELECT database_id FROM [SourceServer].master.sys.databases WHERE name = N'$(DBNAME)')
            FOR XML PATH(''))
FROM    [SourceServer].msdb.dbo.backupmediafamily AS mf
INNER JOIN [SourceServer].msdb.dbo.backupset AS bs ON mf.media_set_id = bs.media_set_id
WHERE   bs.database_name = N'$(DBNAME)' AND bs.type = 'D'
ORDER BY bs.backup_start_date DESC ;
-- Differential restore, if present (otherwise @cmd and @LastBackup are unchanged)
SELECT TOP 1 @LastBackup = bs.backup_start_date,
        @cmd = @cmd + N'; RESTORE DATABASE [$(DBNAME)] FROM DISK=N''' + mf.physical_device_name
            + ''' WITH FILE=' + LTRIM(bs.position) + ', NORECOVERY, STATS=10'
FROM    [SourceServer].msdb.dbo.backupmediafamily AS mf
INNER JOIN [SourceServer].msdb.dbo.backupset AS bs ON mf.media_set_id = bs.media_set_id
WHERE   bs.database_name = N'$(DBNAME)' AND bs.type = 'I' AND bs.backup_start_date > @LastBackup
ORDER BY bs.backup_start_date DESC ;
-- Log restores, if present
SELECT    @cmd = @cmd + N'; RESTORE LOG [$(DBNAME)] FROM DISK=N''' + mf.physical_device_name
            + ''' WITH FILE=' + LTRIM(bs.position) + ', NORECOVERY, STATS=10'
FROM    [SourceServer].msdb.dbo.backupmediafamily AS mf
INNER JOIN [SourceServer].msdb.dbo.backupset AS bs ON mf.media_set_id = bs.media_set_id
WHERE   bs.database_name = N'$(DBNAME)' AND bs.type = 'L' AND bs.backup_start_date > @LastBackup
ORDER BY bs.backup_start_date ;
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,
--  and if this server is a later version.
SELECT @cmd = @cmd + N'; ALTER DATABASE [$(DBNAME)] SET COMPATIBILITY_LEVEL = ' + LTRIM(ThisServerLevel)
FROM (
    SELECT    SourceServerLevel = MAX(CASE WHEN db.name = 'master' THEN compatibility_level END),
            DatabaseLevel = MAX(CASE WHEN db.name = '$(DBNAME)' THEN compatibility_level END)
    FROM SourceServer.master.sys.databases db
    WHERE name IN ('master', '$(DBNAME)') ) there,
(
    SELECT    ThisServerLevel = compatibility_level
    FROM sys.databases
    WHERE name = 'master') here
WHERE DatabaseLevel = SourceServerLevel AND SourceServerLevel < ThisServerLevel ;
PRINT REPLACE(@cmd, ';', CHAR(10)) ;
-- Uncomment the following EXEC to perform the restore
-- EXEC (@cmd) ;
GO
-- Drop the SourceServer linked server
IF EXISTS(SELECT NULL FROM sys.servers WHERE is_linked = 1 AND name = 'SourceServer')
    EXEC sys.sp_dropserver @server = N'SourceServer', @droplogins = 'droplogins' ;

Monday, November 28, 2011 - 5:37:47 PM - Ankit Shah Back To Top (15230)

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 .


Monday, November 28, 2011 - 5:19:06 PM - Greg Robidoux Back To Top (15228)

Yes this should still work because it gets the last FULL backup and last DIFFERENTIAL backup.

 


Monday, November 28, 2011 - 4:39:54 PM - Ankit Shah Back To Top (15227)

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?


Tuesday, May 17, 2011 - 7:48:35 AM - Greg Robidoux Back To Top (13850)

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.


Tuesday, May 17, 2011 - 7:33:20 AM - tommyketchup Back To Top (13848)

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:23:27 AM - Greg Robidoux Back To Top (13845)

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 - 6:39:36 AM - tommyketchup Back To Top (13844)

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 - 6:20:19 AM - tommyketchup Back To Top (13843)

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
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161005.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161100.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161155.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161250.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161345.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161440.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161535.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161630.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161725.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161820.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105161915.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105162010.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105162105.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105162200.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105162255.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105162350.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170000.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170055.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170150.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170245.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170340.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170435.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170530.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170625.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170720.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170815.trn' WITH NORECOVERY
RESTORE LOG Binary 5_01 FROM DISK = 'K:\Binary 5_01_backup_201105170910.trn' WITH NORECOVERY
RESTORE DATABASE Binary 5_01 WITH RECOVERY

 

When I try to run this, I get loads of errors which carry on like this :-


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '5'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '5'.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '5'.
Msg 319, Level 15, State 1, Line 3

 


Tuesday, February 16, 2010 - 5:51:15 PM - avadhanam Back To Top (4910)

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, October 28, 2008 - 3:08:29 PM - jkli Back To Top (2104)

Thanks.  I will be accessing these scripts through c#.  I think it will work.

 


Tuesday, October 28, 2008 - 2:16:48 PM - grobido Back To Top (2103)

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.


Monday, September 29, 2008 - 8:44:50 AM - jkli Back To Top (1886)

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, September 16, 2008 - 1:18:52 PM - grobido Back To Top (1827)

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.


Tuesday, September 16, 2008 - 10:46:48 AM - ChrisAVWood Back To Top (1825)

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 

 















get free sql tips
agree to terms