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!
