May 18, 2007 at 8:33 am
Hi,
I am in the process of moving system databases to a different folder. I started the server in single user mode using sqlservr.exe -m. I tried detaching 'model', but it's still not allowing me. What am I doing wrong here?
Please help
Thanks
May 18, 2007 at 9:59 am
From Microsoft KB224071:
In SQL Server 2005 and in SQL Server 2000, you cannot detach system databases by using the sp_detach_db stored procedure. When you try to run the sp_detach_db 'model' statement, you receive the following error message:
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database.
May 18, 2007 at 12:48 pm
See: http://support.microsoft.com/kb/224071
James.
May 24, 2007 at 2:50 am
hello
my name is itay
i followed the microsoft KB of doing that. even when i start sql server with the corresponding parameters, i still cannot detach system databases.
i use domain administrator coount with full permissions to the SQL.
and went step by step according to the KB.
has anyone have an idea ?
thanks
itay
May 24, 2007 at 6:49 am
What, if any, error message do you get? Exactly how are you trying to move the databases, what command is failing. Post the actual command that is failing (include all parameters exactly as you tried it) and post the output message.
James.
May 24, 2007 at 7:10 am
itay,
how are you starting? from EM or command prompt? For some reason setting the startup parameters in EM didn't do anything. It works from command prompt
May 27, 2007 at 12:43 am
what i do is edit the startup parameters at the Enterprise Manager(-c -m -T3608). i close the SQL agent and restart the sql server.
after that i open query analyzer and the script :
use master
go
sp_detach_db 'model'
go
and i get the error :
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached
it cannot also be deatach from EM...
how do start it from the command prompt?
thanks all for your help
itay
May 27, 2007 at 10:50 am
From the command prompt type
sqlservr.exe -c -m -T3608
(You have to switch to the binn directory where sqlservr.exe resides, unless it's in your path. Normally it's in
C:\Program Files\Microsoft SQL Server\MSSQL\Binn)
And then detach using sp_detach_db in QA.
http://msdn2.microsoft.com/en-us/library/aa178018(SQL.80).aspx
June 3, 2007 at 1:36 am
oh god
it still doesn't work
when i start sql server from command line, it gets to the line when it says :
spid 51 analysis of database 'tempdb' is 100% complete <approximately 0 more seconds>
and then it stuck there and doesn't continue running
June 3, 2007 at 9:51 am
Maybe it's started. Did you try connecting to master from QA?
June 4, 2007 at 4:54 am
Follow the below steps
1) go to the enterprise manager and right click the properties of the sql server
2) add -T3608 in the startup parameters , stop sql server and then start it
3) make sure sqlserveragent is not started
4) open query analyzer
5) run the command
June 10, 2007 at 2:04 am
yes i did
June 21, 2007 at 2:05 am
it finally worked. i found out the problem. it worked only when i first moved the master DB, and then i started the sql server with the new path to master DB at the startup parameters and the -T3608 parameter before them.
the rest of DB's - model, msdb, tempdb - worked easy with no problems as described at microsoft KB
thanks all for your help !
itay
June 22, 2007 at 6:54 am
Hi,
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..........
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
but why is the discrepancy b/w the above 2 links,even if i tried with -c -m -T3608 in startup parameters its failing to start in single user mode......even some1 had mentioned earlier that cmd is teh ideal one....any ideas???pls advice
[font="Verdana"]- Deepak[/font]
June 24, 2007 at 12:59 am
if you need to move system DB's to another location (disk or folder..) , do what i did :
first, move master DB: stop sql server. make sure sqlagent is closed and will NOT start automatically. cut+paste master mdf+ldf to the new location. edit the registry key : [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
with the path to the new location of the master mdf+ldf files. (best thing to do: backup this key before applying any changes).
you can add the -T3608 parameter to the registry : "SQLArg3"="-T3608"
yes, it is not a mistake... i did not add the -c -m parameters (no matter what bill gates says...) this is the only way it worked for me.
start sqlserver. now you will be able to move all other system DB's. but you cannot detach/attach them from enterprise manager. use only SP's as described in http://support.microsoft.com/kb/224071
good luck.
itay
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply