March 10, 2008 at 5:53 am
I am trying to move the system databases of a 2000 server to a new location, sounds
simple enough!
Got the instructions from http://support.microsoft.com/kb/224071
Sought out and got confirmation from a less 'wordy' source.
Both instruction sets agreed on a procedure (adding parameter -c -m -T3608 etc).
Tried it and keep getting the same message:-
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb and tempdb cannot be detached.
What am I missing!!
March 10, 2008 at 7:42 am
Please read this article:
http://www.sqlservercentral.com/articles/Administering/movingsystemdatabasesachecklist/1608/
Specially check this step:
After adding the parameter "-T3608" did you re-start the server?
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
March 10, 2008 at 8:22 am
Thanks Preethiviraj, yes I did those instructions as stated adding the new parameter, stopped and started THE SQL Server (I assume they don't mean stop and start the physical server!) but the command to detach the master dB just comes back saying "you can't do that!".
I'm now thinking of moving the system databses mdf/ldf files and remapping in Disk Management to fool the SQL Server into thinking the System Databases are in the same place!
David O'Brien
Frustrated administrator
March 10, 2008 at 8:37 am
did you grant all sqlservers service accounts the needed folder authorities for the new location ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 8:37 am
You use the trace file to move the other system databases, not master. Master cannot be moved while SQL Server is started.
If you read the instructions carefully, moving the master database requires changing the startup parameters for the service.
March 10, 2008 at 8:42 am
The first detach command simply wouldn't run!
March 10, 2008 at 8:45 am
I followed the instructions from Microsoft to the letter, instructions which agreed with sources other than Microsoft's
March 10, 2008 at 3:26 pm
Hi David,
Make sure you are using parameter -T3608 (not -c -m -T3608, mentioned in Microsoft document but not working).
You can not do it through Enterprise Manager, you need to do it using Query Analyzer. For more details follow below link.
http://www.myitforum.com/articles/18/view.asp?id=4077
I advise open the query analyzer using logon id 'sa' else you have to change ownership of databases.
Regards,
MS
March 11, 2008 at 8:30 am
You can do through Enterprise Manager too. I have done that.
BTW, while moving system databases, detach and attach one database at a time. (Or atleast do in the order of dbid)
System databases need to have the specific dbid (example: TempDB should be dbid = 2) and if you mixup it will give a lot ...lot of trouble. (once I did it and I thought I need to create the database server from scratch.)
Best of Luck
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
March 11, 2008 at 10:46 am
Thank you MS
That bit with the flag did it! Removing the -c(still don't know what this is for) and the -m(single user) WORKED!!!
System databases successfully moved:w00t:
Once more Microsoft mislead us with false information, not the first time I've discovered this. I guess being strapped for cash, and all, they need to encourage us to do their courses somehow?
David
March 12, 2008 at 12:54 pm
There are many reasons why you want to move the System Databases, and I agree with all of them.... However;
Rarely do they need restored, they are often much easier to re-create than to restore from backups.
And I have found that about 1 in 5 S(ervice)P(acks)/C(umulative)U(pdate)'s will fail to work properly if the System Databases are NOT in the installation directory. It's one of those things they always seem to forget about. So for most of my systems, I will just leave them on the installation directory.
Now my application databases I do spread across drives as needed, and are NEVER under the same tree as the Instance files.
April 11, 2009 at 11:07 pm
Yes, just to confirm, the above instructions work. Just use -T3608. Do not use -c -m. And you can use query analyzer to run the attach/detach script. But you must also restart the sql service after removing -T3608.
Microsoft are too busy inventing things that no one uses to properly support those that many do.
Goran
April 12, 2009 at 5:36 am
dobrien (3/11/2008)
-c(still don't know what this is for)
David, this parameter stops the service being updated (i.e. running/stopped/etc) in the services.msc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 21, 2011 at 1:31 pm
The trick is to enter -c, -m, and -T3608 as separate parameters. The http://support.microsoft.com/kb/224071 article makes it seem they should all be on the same line.
July 4, 2011 at 6:55 am
It is normally a bad idea to move the system databases (apart from tempdb and (maybe) msdb). There are no performance or integrity reasons for moving these DBs, and all you do in moving them is to add risk to your installation. The fact that you have had problems wit this move shows an example of an added risk.
Also, Microsoft have a track record of SP and CU installs, and upgrades-in-place from one version to another, failing if the system databases are not in the locations specified at SQL install time. This has not happened recently, but it is a brave person who assumes this will not happen again.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply