October 7, 2004 at 7:44 am
Got a really quick question for all you Guru's.
I'm in the process of a rather long data migration from 5 SQL Databases into one. After the migration is complete I am wanting to move the database to another SQL Server on the network.
I was wanting everyones opinion as to what is the best/easiest/cleanest way to go about this.
Thanks in advance for all the help
October 7, 2004 at 8:04 am
I prefer using DTS. It is pretty easy to use and has been proven to be faster that bulk copy for large amount of data. you can use DTS to copy from 5 databases to 1 database.
Regarding moving the database to another sql server on the network, the easiest way is to detach the database and copy the mdf and ldf files to the destination server and attach them backup. Please remember that your database won't be availabe during the duration of the copy. If you want the database to be available on the source server, take a backup of the database and restore it on the destination server.
October 7, 2004 at 9:25 am
If these are all on one server, I think DTS or straight SQL might work well. It really depends on how complicated the transforms are. If it's straight table-table movement, either works well.
As far as the move to another server. The advice above is what I'd give as well.
October 7, 2004 at 10:54 am
Please, be advised on the following if you will use the Copy Database Wizard. Books Online say that you need to be in the sysadmin role on both servers, that it is Online operation and that it uses DTS.
It is not a complete story.
We tried it several times a couple of years ago using the SA account and it failed for the following reason during the following operation:
detaching a database
copying the files (that is where it fails, sure)
re-attaching the source database.
We were really surprised. It was indeed a DTS job, but not the one that just gets a schema and data, but the one that detaches, copies and attaches. So the bottom line:
1. It was not an ONLINE operation. Do not do it on your Production
2. You will need to be not only in SQL Sysadmins, but it should be a domain account with the rights on both servers to at least copy files.
Maybe this behavior was changed with the lates service packs (SP3), I did not test.
Yelena
Regards,Yelena Varsha
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply