Methods to Safely Move a Database Between Two Servers in MS SQL Server 2008

Sep 27, 2012

0 2218

Moving your database from one server to another in SQL Server 2008 can be done using a variety of ways through SQL Server tools.

I. Detach the database from the server to which it is currently attached and then attach it to the new server. You can accomplish this using SQL Server Management Studio as follows:

Detaching a database using SQL Server Management Studio:

Open SQL Server Management Studio. In the Object Explorer, set connection to the SQL Server Database Engine instance and then expand the instance.

Click ‘Databases’ and choose the database you need to detach.

Right-click the selected database, click ‘Tasks’, and then ‘Detach’. The ‘Detach Database’ dialog box is displayed.

Check if the database you need to detach is displayed in the ‘Databases to Detach’ grid.

-Select the ‘Update Statistics’ check box if you want to update the current optimization statistics for your database.

Deselect the ‘Keep Full-Text Catalogs’ check box if you need to remove all full-text catalogs corresponding to your database.
The ‘Status’ column shows either of the two states-‘Ready’ or ‘Not Ready’.

If the column displays ‘Not Ready’, additional hyperlinked information about the database is provided in the ‘Message’ column. If the database is used for replication, the ‘Message’ column shows ‘Database Replicated’. If there are active connections to the database, the ‘Message’ column shows ‘ Active connections’. To remove all the active connections, you should select the ‘Drop Connections’ check box.

If you are ready to initiate the process, click ‘OK’.

Attaching a database using SQL Server Management Studio:

Launch SQL Server Management Studio. Open Object Explorer, connect to the SQL Server database Engine instance, and expand this instance.
Right-click ‘Databases’ and select ‘Attach’.
An ‘Attach Databases’ dialog box will be displayed. In this dialog, click ‘Add’. In the ‘Locate Database Files’ dialog box, locate and select the ‘.mdf’ file of the database you need to attach.
You can also give the database a different name while attaching it to the SQL Server instance. To do this, type in the desired name in the ‘Attach as’ column on the ‘Attach Databases’ dialog box.
You can also change the database’s owner by providing a different name in the Owner column on the dialog box.
After finishing with the above steps, click ‘OK’.

The entire process is performed offline and does not copy the database, rather transfers the file from one server to another.

II. Take a backup of your database on the old server and then restore this backup on the server to which you want to move your database. This is an online process that results in the creation of a new database on the destination server.

Different types of SQL Server backups:

Full/Database Backup:

Full database backup consists of everything within your database including the transaction log. This type of backup will exclude unallocated extents in the files.

Differential Backup:

Differential backup contains only the newly allocated or changed extents. They keep track of all the changes done since the last full database backup.

Transaction log Backup:

Transaction log backups contain all the changes made to your database. These changes are recorded serially. With these backups, you can easily restore your database to a desired point in time.

The foremost step in backing up and restoring SQL Server databases is to devise a backup and restore strategy, considering the available resources and time. A backup strategy will determine the type of backups and their frequency. If you choose to backup only the database, you will lose all the modifications done by transactions since the last full database backup. In case you plan to backup database with the transaction log, you will lose only the uncommitted changes.

III. If you intend to copy your database, you can do so using the Copy Database wizard in SQL Server Management Studio.

Follow the procedure given below to copy or move a database and its objects from one server to another using the Copy Database Wizard:

Open SQL Server Management Studio. In the Object Explorer, right the database you need to move, select ‘Tasks’, and click ‘Copy Database’.

Specify the source server on which the database to be copied resides.

Specify the destination server to which you need to copy your database.

Specify the database to be moved or copied.

Provide a different name to the target database in case name conflicts exists on the destination server.

Specify the other objects you need to copy or move to the target database, such as logins, shared objects, maintenance plans, etc.

Fix a desired schedule for the copy operation.

Provide a SQL Server Agent Proxy account that has permissions to access the Integration Services (SSIS) Package execution subsystem.

When databases are copied from one server to another, the Copy Database Wizard checks whether the database is online after completing the scheduled task.

About the Author: Jyoti Prakash is Sr. Technical writer who has written several article on How to recover SQL server data from corrupt mdf file, without backup, and from suspect mode.