0

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

2
  • 2
    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? Commented May 24, 2016 at 20:19
  • @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 Commented May 24, 2016 at 20:52

3 Answers 3

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

6
  • 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. Commented May 25, 2016 at 19:52
  • Thanks for pointing that out, have updated it to not use CONCAT anymore, should work with 2008 R2 now.
    – Lawrage
    Commented May 26, 2016 at 0:28
  • 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. Commented May 26, 2016 at 15:36
  • My bad there, changing it removed the commas breaking the restore code. Its all fixed and good to run now.
    – Lawrage
    Commented May 27, 2016 at 7:44
  • 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? Commented Jun 2, 2016 at 14:15
5

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!

1
  • 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\' Commented May 24, 2016 at 21:20
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.

1
  • 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. Commented May 24, 2016 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.