June 20, 2003 at 6:57 am
I know I'm probably missing something obvious, but...
We are moving our SQL Server 2000 to a new set of hard drives. I can detach the user databases, but the master, msdb, and tempdb can not be detached. I can put them into single-user mode via OSQL.
Issue: we are going to copy the files over from one array to the other. My understanding is that you can only copy the .mdf and .ldf files if they are detached. Is this true? If so, how do I detach the master, msdb, and tempdb databases or does putting them in single-user mode do the same thing?
-SQLBill
June 20, 2003 at 7:04 am
Steve Jones has several articles on how to do this:
TempDB:
http://www.sqlservercentral.com/columnists/sjones/atemporarymove.asp
MSDB:
http://www.sqlservercentral.com/columnists/sjones/movingmsdb.asp
Master:
http://www.sqlservercentral.com/columnists/sjones/moveyourmaster.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 20, 2003 at 7:21 am
Thanks Brian but that's not quite what I'm looking for. His directions are for moving them to another drive or drive partition. We are moving them to a whole new array which will have the same drive letters/partitions. I'm beginning to think that all I need to do is stop the services and the files can be copied. But I'm not positive about it.
-SQLBill
June 20, 2003 at 4:07 pm
Yesterday I move master, model and msdb from one directory to another (in a demo database) and it works fine. I used document 224071 from support.microsoft.com to do it.
Two days ago I ask to the forum if I can do something similar, but between two servers, and I think that I shouln't have problems, but this task is a little different than yours becouse there are two servers involved.
Yesterday I also participate in the implementation of Co-Stand-By-Server of Legato and they copy/paste from one array of one server to other array of other server all the databases (including system databases) and we switch the operation from one server to the other without problems.
I know that is easy for me to say (it is not my installation), but you only have to shutdown, change the array, copy the database files and startup, almost 100% sure.
Looking it in another way, you are worried about SQL have some information about the hardware or something like disks ids..., I don't think so.
Good luck.
June 21, 2003 at 7:10 am
The big thing here is that we are NOT moving SQL to new directories, new drives letters, etc. We are just moving to a new set of hard drives that are being built the exact same way the original ones are set up. So, I'm thinking stopping services and just copying the data over will work. But I wanted to have that 'warm and fuzzy' feeling that it was going to work.
Well, it happens today. I'll let everyone know what we did and how it worked.
-SQLBill
June 22, 2003 at 9:14 pm
I have done this part if directory structure is same install sql server to a new machine stop sql server and copy all the data as it is. Then reboot it will automatically pick up the files.
Best of luck..
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
June 23, 2003 at 4:17 am
Correct. As long as the drive letter/path are the same, it works.
Andy
June 23, 2003 at 8:19 am
Here's the update (after working 36 hours of overtime). SQL Server and my data came up just fine. If you are moving SQL Server databases to a new set of hard drives and nothing else is changing (drive letters stay the same), then all you need to do is stop the services and copy everything over. I didn't even have to reload SQL Server, just copied all the files over to the hard drives.
We did have major hardware issues, but that wasn't caused by SQL Server.
Thanks for all the inputs.
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply