Today’s script is also one I used in my migration on Wednesday. It again uses string manipulation to generate a script, this time the restore database script. Now granted this quick script wouldn't work if there are any secondary data files and is reliant on the logical file names and file locations etc etc.
The point of me using this script is because I am very anal when it comes to standards, I like to make sure drive letters and paths are consistent as well as naming conventions for all databases and objects. Adhering to standards makes your life as a DBA much easier especially when it comes to tasks like migrations.
/*
-----------------------------------------------------------------
The Importance of Being Idle (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 restore database script
SELECT
'RESTORE DATABASE ' + [name] + ' FROM DISK = ''D:\Migration\Backup\' + [name] + '_migration_20120301.bak''' +
' WITH REPLACE, MOVE ''' + [name] + '_Data''' + ' TO ''D:\Data\' + [name] + '_Data.mdf'',' + ' MOVE ''' + [name] + '_Log''' + ' TO ''L:\Log\' + [name] + '_Log.ldf'';'
FROM
sysdatabases;
GO
Enjoy!
Chris
