Recover Database problem

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.
jamesmetcalf74Asked:
Who is Participating?
 
lcohanDatabase AnalystCommented:
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/
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
jamesmetcalf74Author 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That means SQL Server service is stopped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.