Scripts for Backup and Restore

Someone posed this question:  How do you generate scripts for backing up all the databases on ServerA and restoring them on ServerB?

BACKING UP

Generating the backup script is easy:

SELECT 'BACKUP DATABASE [' + Name + '] TO DISK = ''D:\Backup\' + name + '.bak'''

FROM sys.databases

ORDER BY name

Or, refer to a post from December 2011 which does this in a slightly fancier fashion via a stored proc that gives options for putting the date or date/time in the backup filename.  The stored proc is called BackupDB.

Note the proc BackupDB has a couple of dependencies, which can be easily installed:

        udfAddBackslashIfNec        

        DoesPathExist

        

RESTORING

Restoring is trickier, but doable.   I recommend taking the time to write the script – it will help you for years to come.

The RESTORE DATABASE script is shown in this stored procedure, RestoreScriptGenerateFromBak.