Problem moving system databases in SQL Server 2005

Question

Problem moving system databases in SQL Server 2005

TF-245998

Mr or Mrs. 500

Points: 587
More actions
February 2, 2006 at 4:55 pm

#168057

Has anyone successfully detached, moved, attached system databases in SQL Server 2005? I get the following after adding the -T3608 flag, restarting SQL and executing sp_detach_db 'msdb' (same error if database is model):
Location: dbmgr.cpp:6728
Expression: !FindDB(dbid)
SPID: 58
Process ID: 4720
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details
The errorlog shows also Error: 17066, Severity: 16, State: 1 and a stack dump.
I thought at first it might be due to uninstalling & reinstalling several times on this box, it's 64-bit, we're testing 64-bit SQL. Started with SQL 2000, upgraded to 2005 32-bit, then unistalled/reinstalled to SQL 2005 64-bit. But trying the detach on another server that's SQL 2005 32-bit got the same error. KB article 224071 seems to have been updated for SQL 2005, but looks like the only change is on how to manipulate the startup parameters. Books Online says you can move system database files using the ALTER statement, but SET OFFLINE for msdb errors with 5058.
I don't like that it's treating it like a named instance with the folders named MSSQL.1\MSSQL\Data etc. We like to follow naming conventions here, so thought I'd try moving the database files.
I've tried doing the program uninstall a couple of times to try to force it to not be a named instance, but no luck. Probably registry related, but can't find a KB article on manually removing SQL 2005 similar to 290991. Would rather not hack the registry without directions.
February 6, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ben-343625 SSC Rookie Points: 39 More actions · Answer 1

I'm having the same problem, moving the system databases. I have got 2000 and 2005 on the same machine as different named instances, sould this be my problem?

TF-245998 Mr or Mrs. 500 Points: 587 More actions · Answer 2

Couldn't say for sure, but I doubt it. I notice that MS article 224071 (How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server) has been updated since I was having this problem. By following the new & improved documentation, I was able to successfully move my system databases.

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 3

Thanks for the final post.....this solves the problem of Microsoft hard coding the location of the Master/Model/tempDB in the program files directory on my C drive. While I could change the directory in the install, it was an all or nothing process....either the binarys were in the wrong directory or my database system files were.

This works great!

Eric

olegusan Valued Member Points: 53 More actions · Answer 4

It looks like it does matter where you put your trace flag (-T3608).

If you put the parameter at the beginning of the startup parateters line, sp_detach_db does NOT work.

If you put it at the end - it works.

Cheers,

Oleg.

davidrudd SSC Enthusiast Points: 128 More actions · Answer 5

Oh ya. Position is the trick. I have a standard Default instance, 32-bit installation. There was some inconsistency. I could detach the msdb but not the model. Also, for some reason it worked fine on the first new SQL2005 server, but failed on the second just for model. In both cases I was using the command line to start the service with -T3608 instead of editing the SQL Server Configuration Manager Advanced-tab server properties like such:

>sqlservr -c -f -T3608

When I changed it to:

>sqlservr -c -f -dD:\MSSQL.1\MSSQL\DATA\master.mdf -eD:\MSSQL.1\MSSQL\LOG\ERRORLOG -lD:\MSSQL.1\MSSQL\DATA\mastlog.ldf -T3608

model was able to detach. Go figure.

W0099527 Grasshopper Points: 23 More actions · Answer 6

Hey there,

I'm not sure if this will help you at all but... I had sql server 2000 SP4 and sql server 2004 SP2 on a windows server 2003 R2 machine and had the same problem moving the system databases over to 2005. The other databases I just restored in 2005 with no problems but for the system databases (master, msdb, model) I had to run the generate script wizard (by right clicking on them in 2000) and then run them in sql server 2005 (because I couldn't login in single user mode). Seemed to work for me, good luck!

check this out as well...

http://sugeshkr.blogspot.com/2008/07/moving-system-databases-sql-server-2005.html

kingmanjunath SSC-Addicted Points: 409 More actions · Answer 7

hi,

if you are moving or attaching sytem dbs, along with you need to move the resource db to the master db location.

Goodluck

KingManjunath