Restore all databases at once

We are doing a major upgrade this weekend, so like any good DBA, I have planned for a full backup the night before and needed the ability to quickly restore if it goes sideways and needs to roll back.

The catch is that there are close to 300 identical databases involved.

This is easy enough to do if you know where info is stored in MSDB related to the last backup.

I’ll just leave the code I used right here for your amusement:

Use MSDB;
go
 
--generate the list
Select
    max(backup_start_date) as Backup_Date,
    database_name,
    Max(physical_device_name) as physical_device_name
Into
    #lastFullBackup
From
    msdb..backupset bs
    join msdb..backupmediafamily bmf
        on bs.media_set_id = bmf.media_set_id
Where 1=1
    and backup_start_date > getdate() -1
    and type = 'D'
Group By
    database_name
Order By
    database_name
 
--Create the restore commands
Select
'restore database ['+database_name+'] From disk = ''' +physical_device_name+''' With replace, recovery'
From
    #lastFullBackup
Where
    database_name like '%MyDB%[0-9]'  -- database1, database2, etc.
 
--Cleanup
Drop table #lastFullBackup

There are probably much easier or prettier ways to do this, but I’m only 2 cups of coffee in thus far, and one of them got cold…

If any of the above doesn’t make sense, please drop a comment. It works on my system and should be valid for SQL 2000 – 2016…the system tables referenced have not changed.

Have a bright, shiny day full of puppies or muscle cars….whichever you prefer!

Kevin3NF