March 7, 2002 at 5:49 pm
What is the best way to migrate a large database to a new server on the same network with the same sort order etc? Copying a disk backup to the new box and trying to restore it is difficult. Any suggestions would be most welcome.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
March 7, 2002 at 7:05 pm
Your options are dackup file or tape, copy the physical files themselves and use sp_attach_db on the other server, or Export to other server (which takes longer). Suggestion is if the file is large use WinZip or some zip program on the file (usually ends up near 18%-30% the original size using normal compression) then transfer the compressed file over then use whatever yuo used for compressing to uncompress and restore, the same works for the physical files themselves but to access the files with detaching you will need to stop the server long enough to make a compressed copy then restart the original server, if you goal requires uptime at all hours this may not be an option. Also if you are going to be movig the data over often you may want to consider replication and setting it up on a weekend for minimal network impact (unless your staffed 7 days a week).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 7, 2002 at 7:35 pm
Mostly agree with Antares. If you can do downtime, detach, copy, attach. If not, backup/restore
Steve Jones
March 8, 2002 at 4:20 am
If your difficuly with copying the backup is that it takes hours you might try the following. When I would copy backups from production to our development environment it used to take about 5 to 7 hours across a T1 line for a 2 GB file. I was initiating the copy remotley from my workstation. Our network engineer told me I should start the copy from the source or destination computer. He said the reason for the slowness was that Windows had to make a copy of the file on my local workstation before it could place the file at the destination I chose. Now, when I do copies like this I go to the destination coputer and initiate the copy from there. This same file usually only takes an hour now and all smaller files copy in less time.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 8, 2002 at 4:26 am
That is a very good point to add Robert, I have myself made that mistake many times.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 8, 2002 at 5:18 am
If you have Terminal Services installed you can do it without a trip to the server room.
Andy
March 8, 2002 at 9:50 am
That's really interesting. Didn't know that.
I have another suggestions which is what we used when migrating to a new server in another state.
1. We copy a backup to the new server (sloooowwwwly) a bit before we need to cut.
2. Meanwhile we run a differential on the old server and then copy that, restoring on the db.
3. Repeat with a t-log backup. Eventually you should be able to make a copy and a cut in a minimum amount of time.
Steve Jones
March 8, 2002 at 11:11 am
Steve - Im not convinced Robert is right btw, just that TS is handy for such things! His explanation makes sense, but I'd like to see some empirical evidence, Netmon maybe. Article Robert? Take a little time to set up right I think.
Andy
March 11, 2002 at 3:41 pm
The Problem with Attach detach is that the user accounts are not listed in the security selection on the new server. I have too many users to enter each one by hand. I'm gonna take another look at copying a disk backup to the new server and restoring it.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
March 11, 2002 at 5:51 pm
Same result as attach db in most cases unless you are restoring the master on the other server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 11, 2002 at 5:52 pm
Easy enough to handle. If you have SQL2K then you can use DTS to move the logins. Even without DTS you can bcp the data from/to sysxlogins in master, or look at using sp_addlogin to move them without using BCP. Might have a script here on the site, I know MS used to have one on MSDN that used sp_addlogin. If you do end up adding them manually they will have a different SID, you'll need to run sp_change_users_login to realign it with the one stored in the attached db's.
Andy
March 12, 2002 at 10:35 am
The database we are migrating is version 7 sp1.
Can I use DTS to copy the logins to the new server using that version?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
March 12, 2002 at 11:33 am
Not the same way you would in SQL2K, but I don't see why you couldnt just copy the data from sysxlogins using it.
Andy
March 12, 2002 at 5:39 pm
A microsoft support topic describes this procedure. Since both machines are in the same Domain, it may be feasible. The article number is Q246133
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
March 17, 2002 at 11:29 pm
Can't you just copy over the mdb and ldb files to the appropriate directory? I've done that in the past and haven't had any problems. Has anyone here had issues with that method?
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply