About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Monday, February 27, 2012

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.

1 comment: