2

Is there a way to start a SQL 2000 instance without loading one specific database? We've got a database that is crashing the server at a certain point during the db recovery process, and we're going to try to recover from backup. But we can't start the restore when the instance keeps restarting itself.

(If you're curious, a huge table re-indexing that went bad. It was cancelled, and tried to roll back, but for some reason the SQL service restarted itself during the rollback. Now we're in a situation where the service restarts, it tries to recover the db, then somewhere in the middle it gets stuck and restarts the service again.)

Any help or ideas are appreciated.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
BradC
  • 2,200
  • 4
  • 25
  • 35

1 Answers1

6
  1. stop sql server
  2. rename data & log files of the db with the issues
  3. start sql server

The db will be marked 'suspect' when you start, but at least it wont be trying to rollback. From there you can drop the database & do a restore

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • sounds like that should work. Will give it a try. – BradC Aug 16 '09 at 18:06
  • That worked, thanks. Database didn't appear as "suspect" in SSMS, though. Just missing the [+] in front, and obviously not accessable. – BradC Aug 17 '09 at 00:26