April 13, 2006 at 12:46 pm
Hi experts,
I just made a mistake that I used DTS package to copy a database from SQL Server 7.0 to SQL Server 2000. It actually copied over bunch of the logins from SS 7.0 also!
What's the right way and fastest way to copy a database from a different server? the database I need to copy has more than 1000 tables with huge amount of data.
ps. source database has users all the time, and it can't be offline.
Please suggest. Thank you.
April 13, 2006 at 1:29 pm
It depends on the facotrs of what you need to move. If just the data and is large most common is take a backup and restore to the new server. You can restore a 7 backup to 2000 but not the other way around so keep that in mind.
April 13, 2006 at 1:35 pm
If you are dealing with a large database that has constant pressure, I would not recommend using DTS as it will add overhead to your database and could impact performance. I assume that you have a backup/recovery strategy? I would restore your database to the new server. Is this something that needs done often?
April 13, 2006 at 1:40 pm
What about detach and attach the database? Is it required to bring the database offline for detach?
I thought detach and attach would be faster than backup/restore method. The database is about 31GB, I am trying to copy this database as soon as I can.
April 13, 2006 at 1:42 pm
yes the database will go offline, and I wouldn't do it that way because if you attach it will upgrade to SQL 2000 on the new server and you may leave yourself without a recovey option to SQL 7.
As for size do a file backup not tape and send across. But you mostly likely will need to offlien the database if you want to have total recovery without losing any transactions. Otherwise I would do like so.
Perform a full backup
Restore to SQL 2000 with leave inactive with ability to restore additional backups
Set 7 database to admin only and keep the users out
Do a transaction log backup
Restore TL backup to SQL 2000 point user application to new database and you are done.
You might have to sync your user accounts thou with sp_change_users_login.
I also would try a dry run without preventing the users access to the 7 DB and moving the tool to verify if you have everything in order.
April 14, 2006 at 7:04 am
Why not use DTS, but unselect the options for logins?
Scott
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply