Today’s script is one that I used earlier this week. On Thursday I migrated a server from SQL Server 200 to SQL Server 2008 R2. Now I love migrations, I don’t think I’m weird but I buzz off the addrenaline rush when working under pressure and I get a great deal of satisfaction when a migration is complete. As part of the migration I backed up the databases ready to be restored to the new server. As there where a number of databases to backup and of course I don’t use the GUI where I can help it the easiest way for me to generate the backup script prior to the migration was using the below script. The Query results I then save to a script file ready for use.
/*
-----------------------------------------------------------------
String Manipulation (Results To Text Ctrl+T)
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Change database context
USE master;
GO
-- Create backup database script
SELECT
'BACKUP DATABASE ' + [name] + ' TO DISK = ''D:\Migration\Backup\' + [name] + '_migration_20120301.bak''' + ';'
FROM
sysdatabases;
GO
Enjoy!
Chris
