Moving a Database

Wednesday Aug 30th 2000 by Krishnan Kaniappan

You can move databases from one location to another location in many ways but the most often ways used are...

You can move databases from one location to another location in many ways but the most often ways used are:

  • Using sp_detach_db and sp_attach_db
  • Using Backup and Restore Procedures
  • Using data transformation services.

Before SQL Server 7.0, moving the database was quite tricky. SQL Server 7.0 has simplified the task of moving databases without using the database backup and restore with the inclusion of sp_detach_db and sp_attach_db.

As far as I'm concerned the easiest and fastest way of moving databases is using sp_detach_db and sp_attach_db. Especially when data and log files are present on the destination system.

There might be some problems when you move databases to different servers as the link between master and user database is broken because the login information is stored in the master database (syslogins table) and the user information is stored in the user database (sysusers table). To fix this you can use sp_change_users_login procedure to change relationships between login and user in the current database or you can delete the orphaned users by directly modifying sysusers table in the current user database and then recreating the logins and users.

SP_detach_db detaches a database from SQL Server and leaves the files intact. Here's the syntax:

Sp_detach_db [@dbname=] 'dbname'[,[@skipchecks=]'skipchecks']

Where dbname is the databse name and skipchecks, when set to true performs an update statistics on all of the tables in the database before detaching the files.The following example detaches the database 'kris' and performs an update statistics on the tables in the database:

sp_detach_db kris,true

But ensure that the data and log files of the database are not in use before detaching the database or if you want you can even bring the database offline by using sp_dboption procedure. The following example brings the database 'kris' offline:

sp_dboption 'kris','offline','true'

Once you have detached a database, you can copy the files to another server, ftp them to a remote location, or move them around on your local system or different servers and reattach them.

To reattach the database you can use sp_attach_db procedure which has the following syntax:

sp_attach_db [@dbname=] 'dbname',[@filename1=] 'filename1'[[,...@filename16=] 'filename16']

Where dbname is the name of the database to be attached to the Server. The name need not have to be the same name as it was prior to detaching. The first filename should be a primary file which includes all of the database system tables.The following example rettaches the databse 'kris'

sp_attach_db krish,'c:\mssql7\data\kris.mdf','c:\mssql7\data\kris.ldf'

There might be situations in some organization where you need to archive the monthly database. To accomplish these detach the files and then move it another server. If you need to retrieve the information stored in the archived files then just reattach the database which I think is the easiest and the fastest way of doing it as the files already exist on the server.

If you want to make any suggestions regarding this article you can e-mail me at the following address:

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved