[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


restore database when sql server won't start

Posted on 2011-10-15
Medium Priority
Last Modified: 2012-05-12
I have a single instance of SQL server 2008 running on Server 2008 R2.  The SQL program files and most of the datafiles are on the main server box;  a few data files from a single partitioned table were on an external storage device that failed.

I have a complete backup of the database.  But SQL server won't start, so I can't use SQL Server Management Studio to do the restore.

It seems that all I really need to do is to use the complete backup (1Terabyte) to get the 100 GB of datafiles restored to the external storage device.

Is this the most efficient way of getting restored?  How do I do this?

Question by:dakota5
  • 4
  • 3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36973400
>But SQL server won't start
you just need to solve that?!

please post the contents of the ERRORLOG file from the sql server log folder

Author Comment

ID: 36973825
OK.  Will do this on Monday 10/17/11

Author Comment

ID: 36982459
After creating the directory structure for the database files stored on the repaired external storage device, sql server now starts.
The database which had some of its datafiles on the repaired external storage device does not start.  When I right click on the name of the database in SSMS, my only options are to take the database offline, or to detach it.
I do not have the option to restore it.  (I have a complete backup online).

The other databases running on this instance of MS SQL are functioning normally-- only this one database had datafiles on the external storage device that malfunctioned.

How do I restore the entire database;  and I'd like to run restore in a verify only mode, first, just to make sure all the directory structures are in place.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 36983198
As angelIII has indicated the answer to your question is in the ERRORLOG.

Author Comment

ID: 36983710
The error log states the names of the datafiles that are missing from the restored external storage device.

But how do I selectively restore datafile1.ndf from the complete 1 TB backup without restoring the entire backup?  Or must I restore the entire 1TB database?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36984314
>But how do I selectively restore datafile1.ndf from the complete 1 TB backup without restoring the entire backup?  Or must I restore the entire 1TB database?

how did you "backup"?

Author Comment

ID: 36988794
Backup was performed in SSMS.
Right click database | tasks | backup |

Full backup type, backup database (not files or filegroups)
Database has Simple recovery model.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36988815
indeed, you need to restore the full db, you cannot just restore a single datafile.

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

834 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