Cannot start MSSQLServer service after restore from backup

[bopritchard]

after doing a db restore i'm unable to start the sqlserver service...last few lines of the errorlog is reporting:

2003-05-19 15:18:12.09 spid1 Starting up database 'model'.
2003-05-19 15:18:12.09 spid1 Opening file d:\MSSQL7\DATA\model.mdf.
2003-05-19 15:18:12.10 spid1 Opening file d:\MSSQL7\DATA\modellog.ldf.
2003-05-19 15:18:12.12 spid1 Bypassing recovery for database 'model' because it is marked IN LOAD.
2003-05-19 15:18:12.14 spid1 Database 'model' cannot be opened. It is in the middle of a restore.

 

[MaffewW]

Hi bopritchard, pls can you provide some more info. Unless I'm mistaken you shouldnt be able to read the logs if the sql server service wont start (are the logs you are reading the ones under the management tab in enterprise manager).

Do you get any error message on the screen when trying to start the sql server service via the sql server service manager or control panel?

Which database was it that you restored? It sounds like possibly (if it is the model database which is causing the problem) you did the restore with the NO RECOVERY option specified, or if you did it through enterprise manager then didnt have the 'Leave database operational. No additional transaction logs can be restored' option checked under the options tab on the restore database screen.


Brighton, UK

 

[bopritchard]

the logs i'm reading are the files in the logs folder in the mysql directory...doesn't return an error when attempting to start...just says "starting" for a few seconds...then returns to "stopped"...

i restored through my backup exec interface...i was attempting to restore all of my databases...and you are right...the "NO RECOVERY" option was set...to tell you the truth...i don't know what that setting means...have i really hosed it up?

how can i get it back up?

vers 7 by the way

 

[SQLBill]

No Recovery means that the database to await other files to be restored. But in your case there aren't any other files so it's going to keep waiting and waiting and......

I don't know if it's in the FAQs, but check there and do a search of this forum. This issue has come up several times.

All you need to do is stop the MODEL restoration and re-run the restore using WITH RECOVERY. I can't think off the top of my head how to stop the current restore.

-SQLBill

 

[SQLBill]

I did a search in the MS SQL Server: Programming forum (this one Administration is a new one) and found a suggestion (no one posted whether it worked or not). thread183-451448 suggested to just run:

restore database model with recovery

You can give that a try and see what happens. If it works it's because right now the model database is awaiting further files and wants the last one to say 'with recovery'. So issuing that command should stop the load. WARNING: I have not tested this.

-SQLBill

 

[MaffewW]

Bopritchard, you probably havent really hosed it up, try what SQLBill suggested and start the model restore again. I tested this yesterday and it let me simply start the restore again, I didnt need to stop the current restore, just restored a new Model DB over the top specifying the WITH RECOVERY option (or 'Leave database operational. No additional transaction logs can be restored' option if doing a restore with enterprise manager as specified in my first post)

good luck, let us know how you get on

Matt

Brighton, UK