How to move one database /data to another server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to move one database /data to another server

Author

Topic

sqLover
Starting Member

10 Posts

Posted - 2004-02-02 : 10:43:13

Hi,
I have a database(myDB) running on my PC.
I want to move this database to the Server
in the network(LAN). I want this new database
to have all the tables, views, procs etc which
I created for myDB in the PC.
How do I do this ?
Can I copy and paste some required files
and get it done ?
Can I use the DTS for this ?
Any other ways like import/export ?
Any responce will be appreciated
Thanks
Prasanth

Its aiways nice to be important
It's more important to be nice always

nic
Posting Yak Master

209 Posts

Posted - 2004-02-02 : 10:55:21

There are a few ways to do it. The way I transfer dbs between servers is first to script out the entire database (in Enterprise Manager) making sure you script all constraints, views, etc. I then run that script on the new server. This will give you all your table structures etc. Once that is done, I use the Import/Export utility to tranfer all the data.

Nic

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-02-02 : 11:51:21

You can alson use sp_detach_db and sp_attach_db to do it.

Kleber
Brazil

sqLover
Starting Member

10 Posts

Posted - 2004-02-02 : 13:19:22

Will You please explain the syntax
and the arguments to be passed for the
sp_attach_db and sp_detach_db
I successfully detached the database on my PC
But dont know how to attach on the Server
Thanks
Prasanth

Its aiways nice to be important
It's more important to be nice always

MuadDBA

628 Posts

Posted - 2004-02-02 : 13:20:38

sp_detach_db and sp_attach_db are the preferred methods. You can also shut down your SQL server, move the .MDF and .LDF files associated with the database over to the new server and attach them there, or restore the database from a backup to the new server.

If you have DTS packages, etc you will need to move them to the new server either by transferrni the MSDB database (not preferrable) or by saving them as files and opening them on the new server (preferred).

Remember to use sp_change_users_login (see BOL for more info) to fix the orphaned users in your database and associate them with a SQL server login.

MuadDBA

628 Posts

Posted - 2004-02-02 : 13:23:23

once youhave moved the files to the location on the new server, just run:

sp_attach_db 'filename1','filename2','filename3'

where filename1, 2, and 3 are the names of your actual files, with the full path to them (ie 'c:\mssql\data\mydb.mdf')

sqLover
Starting Member

10 Posts

Posted - 2004-02-02 : 16:07:28

Greate Guys
"sp_attach_db" even trsnsferred the data with
the database !!!!!
A small syntax change actually
sp_attach_db 'dbName','file1','file2';
where dbName is the name of the new database.
Thanks
I appreciate it

Its aiways nice to be important
It's more important to be nice always

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources