Q:

Restore All Databases Script

I am migrating SQL Server DBs to a new instance.
I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog.
Does anyone know of a sample script to do this?
Thanks!

A:

I, personally, use sp_restorecriptgenie by Paul Brewer. Here's the link to the SCC article that has it. Under 'The Procedure'
http://www.sqlservercentral.com/articles/Restore+database/95839/
Basically, once you build the stored procedure, just run exec sp_restorescriptgenie and the script will go out and get all backups for every DB (even transaction logs) and give you a script to generate them. Also generates the DBCC CHECKDB commands to check the DBs after restoring.
I actually just used this script about an hour ago so I can do a test restore and verify my backups.

