SQL Server Performance Forum – Threads Archive

Moving system db files ??

Hi Folks, Just thinking stupid or may be interesting problem .. One of our system engineers during installation of sql server 2005 , set it up default locations for system database files, now he wanted to know if he move them to another drive. So I did some research as listed below 1. Stopped the sql server instance 2. Moved the master database mdf and ldf files from default location to new drive. 3. Started the service using the command line tool sqlservr.exe -s<instance name> -d&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />ath of master mdf file> -l&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />ath of master ldf file> 4. This brought up sql server fine. Limitation: ========== I can use this technique for MASTER database , but for moving and attaching the new location for other system databases is a challenge. Could anyone suggest anything ? Cheers [?] Sat

After researching or google on moving db, you could have find this link<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> http://support.microsoft.com/kb/224071</a>

And from BOL toohttp://msdn2.microsoft.com/en-us/ms345408.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

OOP’S MISSED PROFESSOR GOOGLE…next time will make sure to check it. Thx
Sat

Hi friends,
i guess im not duplicating this topic……i have the following scenario in SQL 2000, I tried in the enterprise manager,by adding the startup parameter as -c -m -T3608…….and then gave a service restart,the sql server started but not in single user mode,hence i was unable to detach the system dbs…. but when i tried the same in command promt by navigating to the path where sqlservr.exe resides…….it started in single user mode ….i gave, net start mssqlserver /c /m /T3608 (Note: -c -m -T3608 is not working properly) so i started with / option in cmd…….. pls refer the link, http://msdn2.microsoft.com/en-us/library/ms187598.aspx —for using / option also the below one http://msdn2.microsoft.com/en-us/library/ms190737.aspx —for using / option
http://support.microsoft.com/kb/224071 —it suggest to use – option did i did anything wrong???even if i tried with -c -m -T3608 in startup parameters its failing to start in single user mode……is cmd the ideal one???even i came across few people who say cmd is best one but y?….any ideas???pls advice Regards
Deepak
SQL DBA

Deepak
When you use GUI tools sometimes it may be problem then you will forget to close connection against that SQL instance and in that case it will create such problems. In my experience I rely upon CMD based statements rather than depending upon GUI, this way it is easy to know what we are executing and in-depth of the required process. BTW what was the error when trying to start in single-user mode?
Have you checked any other connections to that SQL instance or any PERFMON based job to collect the counters? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thanks for your prompt response Sathya……..when i gave the startup parameters in GUI as -c -m -T3608 the sql services are starting fine but wen i try to detach the msdb and model dbs its throwing an error stating unable to detach system dbs…..also i tried to verify if its in single user mode by locally connecting to the server and s i was able to connect,so it was not in single user mode………
Then i started from commamnd prompt and detached it successfully……….. Regards
Deepak
SQL DBA

Why do you need to detach the msdb…
MSdb can be restored as regular using database…make sure that you stop sql agent before restoring…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I believe you haven’t gone through thorougly on those KBA link above, if you are using SQL Express then you have to use

quote:
start /wait <CD or DVD Drive>setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

settings. Refer through the moving msdn & master database sections over there. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>