• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

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.
  • 2
2 Solutions
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/
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.
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
That means SQL Server service is stopped.
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now