Problem generating restore scripts for multiple ola backups 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
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!