Sql-server – Problem generating restore scripts for multiple ola backups

ola-hallengrenrestoresql server

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

Best Answer

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

Related Question