About Me

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. 517 Lafarge Ave. Louisville, CO 80027 7667 Dyer Road Louisville, CO 80027

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?

Generating the backup script is easy:

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

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:
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: