Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Recover Database problem

Posted on 2016-08-29
4
Medium Priority
?
67 Views
Last Modified: 2016-09-09
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.
0
Comment
Question by:jamesmetcalf74
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 1000 total points
ID: 41775102
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
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41775911
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
 

Author Comment

by:jamesmetcalf74
ID: 41776635
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41777636
That means SQL Server service is stopped.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question