Database Copying Wizardry

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool. Another possible usage is to create a database replica on a development machine, which is exactly what tuchfeld wanted to do in the "Copy Microsoft SQL Server 2008 DB..." question I recently participated in. As a result of this question, I wanted to share a quick how-to guide, so here we are and off we go...

To start, launch the Copy Database Wizard via Tasks | Copy Database...

Servers and Settings

1. Set Source Server, which can be an instance running SQL Server 2000 or higher; click Next.

Note: this screen defaults to Windows Authentication; however, please ensure to choose the appropriate Authentication method based on your configuration. Remember, per Microsoft's documentation, you MUST be a sysadmin on both servers.

2. Set Destination Server, which must be running SQL Server 2005 Service Pack 2 at a minimum; click Next. Note: the target server requires the SQL Server Agent feature, so SQL Express will not work. As of the writing of this Article, this includes SQL Server Code-Named "Denali" Express Edition, despite the installation with Advanced Services loading a SQL Server Agent Service.
Note: this screen also defaults to Windows Authentication; therefore, as before, please choose the Authentication method corresponding to your sysadmin credentials.

If SQL Server Agent is not started on destination server, you will get the prompt depicted by 'Figure 2b' as a reminder. Note: you can proceed without starting the Agent Service; however, the copy will FAIL as illustrated by 'Figure 10a' and 'Figure 10b'.
If you choose a SQL Express instance as a target, you will get the following message box.
3. Choose Transfer Method; click Next. Detach and Attach method is fastest, but SQL Server Management Objects (SMO) method is handy for copying databases that needs to remain usable. Note: Microsoft recommends Detach and Attach for 64-bit SQL Server 2005 systems.

Databases and Dependencies

4. Select Databases to copy or move, noting system databases (i.e., model, msdb and master) cannot be transferred using this tool; click Next.

5. Configure New Databases, assigning appropriate file locations and choosing how conflicts are handled; click Next.

6. Select Additional Objects, e.g., Logins, to transfer; click Next. Note: the master database itself may not be transferable, but shared stored procedures from master can be using this dialog.

Package and Plan

7. Name SSIS Package, which will be reflected in the created SQL Agent job; click Next. Optionally, you can set "Windows event log" or "Text file" logging and, for the latter, an "Error log file path" value.
8. Plan SSIS Package execution time, either immediate or scheduled; click Next.

If schedule is selected, then SQL job schedule dialog like 'Figure 8b' is available to set execution time for transfer.

Wrap-up and Wait

9. Verify Details; click Finish.

10. Examine Results.

If you clicked "Yes" through the SQL Server Agent ('Figure 2b') warning discussed earlier, the result is an error message box similar to 'Figure 10b'.

If the SQL Server Agent Service is started, then the first four checks should pass.

Subsequently, because the SSIS package is executed as a single job step, any errors during the process big or small will cause the job to fail, resulting in an error on step five similar to 'Figure 10d' depending on your logging option.
For purposes of this Article I set logging to the Windows Event Log; therefore, as stated, errors are found by going to Event Viewer on the destination server. Just remember, do not panic. As I said, not every error is fatal. For example you may experience Event ID 12550 -- error caused by the destination server trying to run "EXEC dbo.sp_changedbowner" that fails with message "Cannot find the principal '{source server login}', because it does not exist or you do not have permission." Though, the job indicates failure, you will find the database successfully copies in instances such as this.

Conclusion

Hopefully this Article was helpful for you in some fashion. As a recap, the Copy Database Wizard is a handy tool for System Administrators to move or copy databases and associated objects from one Microsoft SQL server instance to another, leveraging the SQL Server Management Object capabilities (optional), SQL Server Integration Services packages, and SQL Server Agent jobs. Like other tools in your SQL tool belt, I wish CDW serves you well.

Thank you very much for reading...

Best regards and happy coding,

Kevin C. Cross, Sr. (mwvisa1)

References

Related Q&A
https://www.experts-exchange.com/questions/27273894/Copy-Microsoft-SQL-Server-2008-DB-using-connectionString-values.html

Related SQL Server Documentation
http://msdn.microsoft.com/en-us/library/ms188664.aspx
http://msdn.microsoft.com/en-us/library/cc645993.aspx

Resources
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9969


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you found this article helpful, please click the Yes button after the question just below. This will give me (the author) a few points and might encourage me to write more articles.

If you didn't or otherwise feel the need to vote No, please first leave a comment to give me a chance to answer and perhaps to improve this article.

Thank you!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=