Recover Database problem

jamesmetcalf74
jamesmetcalf74 used Ask the Experts™
on
I am practicing for some scenarios.  Lets say my model database gets corrupted.   I have renamed the model database model0 for example.
I tried starting the sql database in single user mode from the cmd prompt and it did not work.  

1)  How to restore the model database from a backup if you don't have the ability to connect to the database through management studio.

i guess that leads me to a couple of questions.

What system databases are required to be optimal for sql server to start in single user mode.  I realize i can delete tempdb and it will just recreate it.  
i am unsure about model and msdb system databases.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
Gail Shaw sa a pretty detailed doc here https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/ about how to do that.

Or you can start the sql service by bypassing the autorecovery like:


net start mssqlserver /f /m /t3608


then try to recover the model?
sqlcmd -e
restore database
model go

as described here: https://blogs.msdn.microsoft.com/karthick_pk/2010/11/25/the-database-model-is-marked-restoring-and-is-in-a-state-that-does-not-allow-recovery-to-be-run-could-not-create-tempdb-you-may-not-have-enough-disk-space-available-free-additional-disk-space-by-d/
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
The model database is the easiest one to recover since all databases are created from model.
You can copy any database that you have (tempdb for example) to the location where model should be and then rename the filenames to have the same names as model database files should have. Now you should be able to start the SQL Server instance.
Other option is to copy the model database files from another SQL Server instance.

Author

Commented:
thanks guys-
quick question... when i start sqlservr.exe from the command line with no switches.... I get a red stop symbol beside the sql instance in management studio.  Does this mean it is in single-user mode... i am used to seeing single-user mode in text beside the instance
experts-exchange.JPG
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
That means SQL Server service is stopped.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial