Detach/Attaching Temp DB broke my SQL.

Question

Detach/Attaching Temp DB broke my SQL.

Chris Cathers

SSC Veteran

Points: 218
More actions
April 19, 2005 at 1:57 pm

#87293

I have been tasked with moving an installation of SQL 2000 from server 1, to its replacement, server 1a. Due to constraints, both technical and political, I elected to use backups for the Master Model and MSDB databases, and then use detach/attach for all the user databases. The disk configuration on Server1 is one 25Gb C partition. On server1a the Op sys is on a 18Gb C and data will be on a 350 Gb E.
So, after copying my system DB backups to the new server, I start the SQL process from a command prompt with Trace 3608. I am successful in restoring the Master DB to the E drive, but the installation is of course looking for the MSDB and Model on C. So, I create a copy of these DBs on the C drive, and SQL starts just fine. I detach/attach the MSDB and Model DBs, moving them to the E drive. This also works fine. Then, before I restart SQL with out the 3608 trace, I notice that SQL has also created a TempDB on the C drive. At this point I have a stroke of creative brilliance and decide to detach the TempDB and attach it on the E drive. That way everything will be on the large disk.

At this point things went south. After successfully moveing the DB - or so Query Analizer told me - I stopped and attempted to start my SQL service. This is when I discovered that I could no longer start my SQL service from a command prompt, or, from Enterprise Manager. I keep getting an error “SQL could not start all the temp databases.” And “SQL could not start DB ID [2].” And even “SQL could not start. Restart SQL with –f to fix this problem.” Which I tried of course, and it didn’t work.

So, My question is simple. Does anyone know how to fix this with out re-installing SQL?

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

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

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 1

If I were you, I would re-install sql server. Also when working with system database, there are special procedure on how to move them, you can not just detach and re-attatch. Good luck.

mom

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 2

Put the tempdb back to where it was, maybe also move model back, and try to start sql server again.

Tempdb is automatically re-created by sql server (by copying Model) everytime you start sql server.

The correct way to move your TempDB is to use the ALTER DATABASE tempdb MODIFY FILE command (see http://www.juliankuiters.id.au/article.php/moving-tempdb&nbsp to tell Sql server where you want tempdb, then restart your server.

Julian Kuiters
juliankuiters.id.au

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

Moving the system databases about is easy if you understand the issues involved. To put a master database from one server to another all the paths for the databases must exist, e.g. if your original master and msdb were on e and f drives then your new locations must be identical otherwise it will not start.

I don't think moving system databases from one server to another is supported.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Franco Carusi SSC Enthusiast Points: 155 More actions · Answer 4

Hi

Have a look at Microsoft Article 224071 (Moving SQL Server databases to a new location with Detach/Attach)

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 5

Christoffer Hedgate has a good article on moving system databases as well.

http://www.sqlservercentral.com/columnists/chedgate/movingsystemdatabasesachecklist.asp

Julian Kuiters
juliankuiters.id.au

EdVassie SSC Guru Points: 60472 More actions · Answer 6

Doing a detach & attach of Tempdb will seriously upset SQL Server. The underlying problem is that tempdb must have a dbid of 2, but SQL prevents any attached database from having a dbid lower than that of model (which is 3).

I played with this situation on a C&B box and got out of it as follows:

1) Start SQL with the -T3608 flag, to prevent recovery actions taking place except on master

2) Enable update of system tables

3) Manually change the dbid of tempdb to 2 in master..sysdatabases

4) Remove the -T3608 flag, disable update of system tables, and restart SQL. If all is well, it should now work.

I would not recommend this procedure for a production SQL Server instance, but it can get you out of a hole until you can re-install SQL.

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

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 7

Just out of curiosity - How did you manage to return tempdb to sysdatabases table? By creating tempdb as a user db with logical filenames tempdev and templog?

EdVassie SSC Guru Points: 60472 More actions · Answer 8

Yes, I re-attached it as a user DB, but using the tempdb standard filenames. I could then edit sysdatabases to set the dbid to 2, and SQL seemed to be happy.

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