What is copy database wizard?
Copy Database Wizard is a new feature from SQL Server 2005 onwards. You can make use of this feature to copy move databases between different instances of SQL Server. It can be used for the below purposes
- 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.
Note: The Server from which you are running CDW should be patched with minimum SQL Server SP2 (better update with latest SP) for Copy Database Wizard (CDW) to work properly
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.
How to Use it?
- Open SSMS in source or destination server which is running SQL Server 2005
- Right click on any of the database and then click on Tasks from the select Copy database wizard as shown below
- Once opened you can see the welcome screen as below
- Click on next to proceed with wizard, In this screen you need to provide the source server name and the credentials
- After this click on next and in this screen provide the destination server(which should be SQL Server 2005) and its credentials
- Once source & destination server details given, you need to select the way by which you are going to copy move the database.
- Detach Attach Faster methods, requires db to be offline. Users will be disconnected and physical files of the db will be copied to the destination server
- SMO Slower method, db will be in online state. This will create the db in the destination server with the same name and copy all the datas from source
- Once the movement method chosen you can select the databases from the source server and you need to specify whether its move or copy.
- Copy Will copy the db to the destination server and the database will be online in both the servers
- Move In this method the wizard automatically deletes the source database after moving the database to destination
- In the next page you need to provide the new db name and the path where CDW should place the physical files in the destination server
- The next page is the good feature in CDW, here you can select the logins, objects, jobs & SP which is related to the database you are trying to copy making our job simpler. Here Im just copy the logins alone.
- In the next page you need to provide the package name and the log file for this process, so that incase of failure you can review it.
- In the next window you can select the package to execute immediately or else you can schedule the same to run after some time after EOD
- You can review the full summary before it proceeds as shown in the below screenshot
- Clicking on finish will create a job with the name mentioned in Configure the package page. If you have selected to run immediately the job will be scheduled to run in the near time or else it will be scheduled as given in the page.
- Note: Once the db copy movement is done you can delete the job or else it will be stayed in your job list.
- Once it succeeded the db will be moved copied to the destination server. You can query sys.databases catalog view to check the same.
- You cannot move system databases
- Selecting move option will delete the source db once it moves the db to destination server
- If you use SMO method to move full text catalogs then you need to repopulate it
- SQL Server Agent should be running or else it will fail in job creation step
- You cant move encrypted objects (like objects, certificates etc) using CDW
Copy Database Wizard is a great asset starting from SQL Server 2005 to move copy the databases from source to destination server. This will make our work simpler.