Solved

Recover Database problem

Posted on 2016-08-29
4
44 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
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 250 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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 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 45

Expert Comment

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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Ranking Based On Value 3 29
Complex SQL 10 34
SQL Server 2012 Row Selection 2 30
How toselect unique values 3 10
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now