The Task

Move our SCOM DB from a Windows 2003 server running SQL 2005 to a Windows 2008 R2 server running SQL 2008.

The Plan

SQL Server has a copy Database functionality. The Copy Database Wizard provides a convenient way to transfer, move or copy, one or more databases and their objects from an SQL Server 2000 or SQL Server 2005 instance to an instance of SQL Server 2005 or higher.

You can use the Copy Database Wizard to perform the following tasks:

• Transfer a database when the database is still available to users by using the SQL Server Management Objects (SMO) method.
• Transfer a database by the faster detach-and-attach method with the database unavailable during the transfer.
• Transfer databases between different instances of SQL Server 2005.
• Upgrade databases from SQL Server 2000 to SQL Server 2005.

Requirements

• The destination server must be running SQL Server 2005 Service Pack 2 or a later version. The computer on which the Copy Database Wizard runs may be the source or destination server, or a separate computer. This computer must also be running SQL Server 2005 Service Pack 2 or a later version to use all the features of the wizard.
• To use the Copy Database Wizard, you must be a member of the sysadmin fixed server role on the source and destination servers. To transfer databases by using the detach-and-attach method, you must have file system access to the file-system share that contains the source database files

Considerations

Instructions

• Open SQL Server Management Studio.
• In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.

• Click Next

• Select the Database you want and choose the authentication

• Select a destination server. You may need to browse for other servers. E.g I want to copy a database from my server dacvsq001 to dacvsql002

• If you get an error saying “Index was outside the bounds of the array” you may need to install a higher version of SQL Management Studio on the source server
• You can select to transfer while the DB is offline or online

• Next select the database you want to copy or move

• Here you can change the name of the database and also select the location of the database and logs to copy or move

• Next you can select additional objects to copy

• Specify a file share containing the source database files

•  Configure the package

• Run immediately or schedule the job

• Check the details you have configured and click Finish