December 15, 2003 at 8:10 am
I recently setup a new dev box and installed SQL Server 2000. I thought I would have to run sp_attach_db for each database on my old machine to get everything up and running again.
However, I tried copying the contents of \Data from my old box to the same directory on the new one. When I started up Enterprise Manager, all of my databases with data, SPs, DTS packages, etc were available just as they were on the old machine.
So it doesn't appear that I need to run sp_attach_db, but I'm wondering if I'm missing anything. Was this a safe way to move everything to the new box?
December 15, 2003 at 9:40 am
Assuming SQL Server wasn't running on the machine you copied from (otherwise you'd probably get sharing violations) and assuming you stored all you databases in this \Data directory (including master, msdb etc) then this method is ok.
Frequently people need to copy from servers that are running and don't want to copy the master database or msdb but only some user created database. sp_detach and sp_attach are then required. Also frequently people don't store all there databases in the same folder (much less the same disk drive, Master be be on a local drive the data and the log stored on separate SAN drives which means copying a folder certainly isn't enough. In your case its sounds like things worked.
Edited by - fhanlon on 12/15/2003 09:40:27 AM
Francis
December 15, 2003 at 10:26 am
Does your new server have exactly the same name as your new server? If so, you should be OK. If not, check @@servername. It's probably coming up with your old server's name.
December 15, 2003 at 10:27 am
sp_attach is one of the two best ways, depending on whether you can tolerate downtime. Backup/restore is the other.
Don't use the wizard, IMHO it doesn't work well.
the other thing you might need is the logins. MS has a procedure to help script these out on the old server and add them to the new server.
http://support.microsoft.com/default.aspx?kbid=246133
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply