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:mssql7datakris.mdf’,’c:mssql7datakris.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: l_muthu@hotmail.com
