DBA > Articles

Copying Databases from Server to Server

By: Rahul Sharma
To read more DBA articles, visit http://dba.fyicenter.com/article/

There are a number of ways you can copy databases from server to server.

Method 1-- The fastest way to achieve this is by detaching the database from source server and then attaching the data and the log files to the destination server.

T-SQL:
Detach the database on the source server (Place the name of the database):

Use Master
GO
Exec sp_detach_db 'database_name', 'true'
GO

Sp_detach_db detaches the database from the source server (there are two parameters:

      • @dbname, which is the name of the database
      • @skipchecks, which is for updating statistics)

A value of 'TRUE' for the second parameter (@skipchecks) of this stored PROC makes sure that UPDATE STATISTICS is not run before detaching the database from the server since updating stats will take some time.

Copy over the the data and the log files from the Data directory of the source server to the data directory on destination server.(Make sure that you do not have the database on the destination server already....if so, drop them).

Attach the data and the log files to your destination server. Execute the following on the destination server:

Use Master
GO
PRINT 'Attaching Database'
EXEC sp_attach_db @dbname = 'database_name',
@filename1 = 'c:\mssql7\data\database_name.mdf', --This is the path to your data file
@filename2 = 'c:\mssql7\data\database_name_log.ldf' --This is the path to your log file

This will attach the database to your destination server. But this will not copy over the logins
from server1 to server 2. You can use this script in order to achieve that:

/* Setup a linked server (using sp_addlinkedserver and sp_addlinkedsrvlogin) called SOURCESERVER from which the standard logins need to be transferred. You can call it whatever you want andmodify the linked server name also. You also need to use sp_serveroption to enable data access for the linked server.*/

declare @login sysname , @password sysname
declare sourcelogins cursor for
select name , password
from sourceserver.master.dbo.syslogins
where isntname = 0 and charindex( 'repl_' , name ) = 0 and
charindex( 'distributor' , name ) = 0 and name != 'sa'
open sourcelogins
while ( @@fetch_status = 0)
begin
fetch sourcelogins into @login , @password
exec sp_addlogin @login , @password , @encryptopt = 'skip_encryption'
end
close sourcelogins
deallocate sourcelogins
go

When detaching and attaching databases, I have run into another problem in addition to the orphaned users issue described above. I have found the statistics to be out of date and that poor performance occurs unless you run sp_updatestats on the newly attached database. So, I would recommend updating statistics after you have attached the database. Alternatively, when you detach the database, make sure that the second parameter is set to FALSE; this will run UPDATESTATS before detaching the database. That way, you don't need to run it later on the destination server.

This method also works well if you have to transfer the databases to a disc and then attach them to a destination server that is not on the network. Also, it's a very fast way of copying databases from server to server. Note: Do remember to attach the database back again to the source server once you have copied over the data and the log files from the source server to the destination server.

Also, for attaching the databases, you should always get the files after detaching them using the sp_detach_db procedure.

Method 2 -- Use good old DTS to achieve the result.

DTS works well for moving databases from server to server. You can use the DTS Import Export wizard (SQL 7.0 and SQL 2000), which can be used to copy over the schema, the objects (e.g., stored procedures, views and triggers), data, and the logins as well. Or you can use the DTS designer and create a transfer database task, then transfer logins task (available only in SQL Server 2000). You can also use the copy database wizard to achieve this (only in SQL Server 2000). DTS is a fairly powerful tool, and if you have not used it before, you are missing a lot of functionality and ease with which you can achieve complex results.

In case you are looking for a good reference on DTS, I would suggest reading Professional SQL Server 2000 DTS by Mark Chaffin, Brian Knight and Todd Robinson.

And an excellent article to get you started with DTS is "Data Transformation Services (DTS) in Microsoft SQL Server 2000" by Diane Larsen.

Method 3 -- Create the schema and pump in the data using bcp/bulk insert.

Create the schema on the destination server if you already have the script for schema creation and then use bcp or bulk insert to pump in the data. Both are non-logged operations so these are pretty fast. The major difference between bcp and BULK INSERT is that BULK INSERT cannot export data whereas BCP can.

You can use BCP to export data to a flat file and then import data into the database from the flat file using either bcp or BULK INSERT. Even though BULK INSERT is a fast way to pump in data, it has a big limitation and that is that the source data must align perfectly to the destination table.

Method 4 -- Backup and restore (the traditional way).

Make a back up copy of the database and then restore it on the destination server.

For taking a back-up you can either write your scripts in T-SQL or write a simple code snippet in VBScript/VB using SQL-DMO.

Example:

Dim oServer 
Dim oDatabase 
Dim oBackup 
Dim sBAKFilePath 

'change this to where ever you want to place your backup files, no trailing 
'backslash, we add it below 

sBAKFilePath = "C:\BackUp" 

'we need a backup object in addition to the sqlserver one 

Set oServer = CreateObject("SQLDmo.SqlServer") 
Set oBackup = CreateObject("SQLDmo.Backup") 
oServer.LoginSecure = True 
oServer.Connect "(local)" 

'this will do a full backup of every database except TempDB to a file 


For Each oDatabase In oServer.Databases 
    If UCase(oDatabase.Name) <> "TEMPDB" Then 
        oBackup.Database = oDatabase.Name 
        'remove any previous backup - same as using T-SQL with init 
        oBackup.Initialize = True 
        'dynamically create the name of the backup file 
        oBackup.Files = sBAKFilePath & "\" & oDatabase.Name & ".bak" 
        'set the action property as needed 
        '0 = Full backup 
        '1 = Differential 
        '2 = Specified files only 
        '3 = Log backup 
        oBackup.Action = 0 
        oBackup.SQLBackup oServer 
    End If 
Next 

'clean up 
Set oBackup = Nothing 
oServer.DisConnect 
Set oServer = Nothing
MsgBox "Database BackUps Done"

And then you can restore it using the restore command (See BOL for further options on this).

Method 5 -- Use distributed queries.

You first need to create the schema at the destination server by using your schema creation scripts. Then you can create a linked server and write insert statements to insert data from the source to the destination server using the openrowset and openquery functions for querying linked servers. You need to make sure that you have disabled the foreign key and check constraints before you start pumping in data and then to re-enable them when it's done. This method is the slowest of all the ones mentioned in this article.

I have covered the details of setting up linked servers and querying them in a previous article. There are some gotchas that you need to be aware of when setting up linked servers for Oracle and other RDBMS. I will cover those in a future article.


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/