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.
| Author |
Topic |
|
matt_calhoon
Posting Yak Master
235 Posts |
 Posted - 2007-10-05 : 01:48:47
|
| Hi there,what is the best method in moving SQL 2000 databases (300+ databases) from one server to another SQL 2000 database server? 1. Stop the SQL Server, copy the .MDF and .LDF log files for each database, move these files to the new database server, Start this database server. (is this possible?)2. Detach database and attach on target server? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-05 : 11:08:56
|
If you can have IDENTICAL drives / folders then:Install SQL Server to IDENTICAL Service Pack.Choose same default drive locations etc.Stop SQL Server Service on BOTH machinesCopy ALL MDF & LDF files from Old to New machine (identical Drive / directory structure)Re-start SQL Server on New machine.I wouldn't do (2). If you have to go that route I would do RESTORE of latest FULL backup using NORECOVERY, and when ready disallow all access to the old server, take DIFFERENTIAL backups 9which should be quick) and restore those onto the New server using RECOVERY option.You'll still have Logins, Jobs, and a bunch of other things to worry about I expect  See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Move%20database,Moving%20to%20a%20new%20sql%20server,Fix%20Orphaned%20UsersKristen |
 |
|
|
matt_calhoon
Posting Yak Master
235 Posts |
Posted - 2007-10-05 : 20:21:37
|
| Hi Kirsten, thank you for your post.Im looking to image the sql server and then restore this image on another server, so everything will be identical. I previously did a bulk detach/attach a few years ago when I had to move 200+ databases to a new environment which worked quite well (except for the manual creation of logins and remapping etc).So in your opinion I should be ok with stopping sql and then copying the mdf,ldf files over to the previously restored image? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-05 : 23:56:15
|
| With image, should ensure both servers use same device driver. Need rename sql server if host name is different. If you copy system db files along with user db files, don't have to manually create sql logins and so. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-06 : 05:18:43
|
| "So in your opinion I should be ok with stopping sql and then copying the mdf,ldf files over to the previously restored image?"yes, so long as nothing else has changed (e.g. service pack).You need to copy system databases too.Kristen |
|
|
|
|
|
|
|
|
Moving databases