February 1, 2005 at 3:10 pm
Greetings, I installed SQL Server 2000 on Server A and a new development
database, DB1 was created on that server. I have now installed SQL Server
2000 on Server B and wish to move database DB1 to the installation on Server
B. I backed up DB1 on Server A to a file, moved the file to Server B and
am now trying to restore to the installation on Server B. So far I have been
unable to figure out how to make this happen. I would appreciate any help.
Can I do this without using DTS? Is there a tool equivalent to Oracle's
export/import? Thanks.
February 1, 2005 at 3:21 pm
You have many options to move databases.
1) Backup the database and restore it on the second server
2) Detach files using sp_detach_db database and copy the files to the destination server and run sp_attach_db and attach the databases.
3) You can use DTS to copy data or transfer database.
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
February 1, 2005 at 6:14 pm
If I detach the files and copy them I assume I then merely reattach the files to the
source database in order to retain the database at the source, is that correct? Thanks.
February 1, 2005 at 6:16 pm
nother question, if I back up the database on the source server is it necessary to
precreate the database on the destination server or will the restore also create the
database on the destination server? The installation on the source is new and I have
not yet created the database in question on the destination. Thanks.
February 1, 2005 at 8:30 pm
If you backup the database on the "old" server, then do a restore on the "new" server, it will create the database as part of the restore. You might want to check your SQL default settings for the DATA & LOG files first on the the "new" server to make sure they go to the location you want them to.
If you use detach/attach, on the "new" server you simply attach, and select the location for your data & log files.
I just went through this and chose to do a restore of my big databases, and detach/copy/attach of the smaller ones. Not a big deal really. I used XXCOPY to copy the files because it has a progress bar option so you can see how it's going
February 2, 2005 at 5:03 am
You can "re-initialize" the database from the restore command. If you wish to restore a backup file to a database that does not exist simply use the WITH MOVE commands.
RESTORE DATABASE doesnotexist FROM DISK='blah' WITH MOVE 'logical_data' to 'physical.mdf', MOVE 'logical_log' to 'physical.ldf'
Where the local parameters are the logical filenames of your source database, and the physical parameters are the physical files you wish to restore to. If you are unsure of your logical file names you can run a:
RESTORE FILELISTONLY FROM DISK='blah'
and it will tell you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply