Problem generating restore scripts for multiple ola backups

Question

I am using Ola for maintenance solution. I have 300 databases I want restore those databases using scripts rather than going through one by one. I prepare the following script to generate restore script.

SELECT 
 'RESTORE DATABASE [' + name + ']' +'
 FROM DISK = ''C:\Backup3\PC1800\'+name+'\FULL\PC1800_'+name+'_FULL_20160504_162534.bak''' + ' 
 WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
 GO' 
FROM sys.databases

It generates restore scripts for all databases .

For example for database AdventureWorks2008

RESTORE DATABASE [AdventureWorks2008]FROM DISK = 
 'C:\Backup3\PC1800\AdventureWorks2008\FULL\PC1800_AdventureWorks2008_FULL_20160504_162534.bak' 
 WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10 
GO

Problem:

The problem it is generating _162534 for all databases. But the second part of the backup file name is different from database to database.

Is there a way I can change the script so it will generate the right number from the backup file not 162534 for all databases?

Exp : PC1800_AdventureWorks2008R2_FULL_20160504_162538

• Aaron Bertrand: Did you look in the backup tables in msdb? Where did you get 162534 that you hard-coded into your statement? Where did you verify that it should be 162538 for a different database?
• SQL_DBA_Bini: @AaronBertrand No I didn't look in the backup tables. The number is generated when the Adventureworks2008 backed up. Yes it was a different database AdventureWorks2008R2

Answer 1

I have had this problem for some time now and finally got fed up and started writing my own script to make restoring to Dev or DR work that little bit easier.

Point the script at your backup folder, it will read all the files and print to the screen the full restore scripts for each database, it works with Olas backup structure as well.

Here is the link to the article and .sql file Lawrage Blog Link

• SQL_DBA_Bini: I am using SQL Server 2008R2 ..when I run your script it gave me the following error. Msg 195, Level 15, State 10, Line 546 'CONCAT' is not a recognized built-in function name.
• Lawrage: Thanks for pointing that out, have updated it to not use CONCAT anymore, should work with 2008 R2 now.
• SQL_DBA_Bini: when I ran the printed to the screen restore scripts...it is giving me the following error :Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'MOVE'. Msg 132, Level 15, State 1, Line 1 The label 'C' has already been declared. Label names must be unique within a query batch or stored procedure.
• Lawrage: My bad there, changing it removed the commas breaking the restore code. Its all fixed and good to run now.
• SQL_DBA_Bini: I keep getting the following error Msg 2714, Level 16, State 6, Line 3 There is already an object named 'tblBackupHeader' in the database. Do you know why?

Answer 2

I posted this in the comments section but I feel this should give you an answer that is simpler and cleaner.

https://paulbrewer.wordpress.com/sp_restoregene/

Simply install the script and run sp_Restoregene. You can pass it @database and @stopat too.

This script will script all the restore scripts automagically for you. Including FULLs, DIFFs and LOGs (in order).

This script has saved my bacon more times than I can count!

• SQL_DBA_Bini: I am restoring form old backups which is located on different directory (D:\Backups) than the current backups( E:\Backups). When I pass the old directory ,@FromFileFullUNC = 'D:\Backups\' in exec Sp_RestoreGene..still generates restore script from current backups not from 'D:\Backups\'

Answer 3

I think this query may help you. I took it from this link.

SELECT physical_device_name,
 backup_start_date,
 backup_finish_date,
 backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'YourDB'
ORDER BY backup_finish_date DESC

If you incorporate that information into your script that creates the backups to prepopulate the restore script, it should make the restore process smoother.

• Steve Mangiameli: This is an excellent starting point, but all of your backups will be shown. You may need to pare this down to just the most recent full/tlog backups.