Database Recovery

My Sql Server instance is down and I am not able to connect to it through Management Studio. Users are telling that they are not able to connect to their database. Now, I need to bring the database up and recover to the point in time. I am taking full backup every night and transaction log backup every hour. How should I approch now?
more ▼

asked Apr 08 '12 at 04:23 PM in Default

mituljain9 gravatar image

30 4 4 6

(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

Is the instance back up? This is your first and foremost issue (if not) because you won't be able to do anything until that is resolved. Any errors in the event viewer? What have you tried already? If you try to restart the service what happens?

At some point, you will also need to determine when the server went down as that will tell you when you'll need to restore to.

Also, have you verified that your backups are valid? Have you restored them somewhere else?

more ▼

answered Apr 08 '12 at 04:32 PM

JohnM gravatar image

5.9k 1 3 7

Reminds me of "Don't worry about a backup plan - worry about a recovery plan." Because that'll dictate your backup plan for you...

Good point about testing the backups by restoring them elsewhere - there's no point in having untested backups, or backups that can't be recovered.
Apr 08 '12 at 04:40 PM ThomasRushton ♦


I like the quote. Hadn't heard it in that way before but I'll definitely remember it!

Apr 08 '12 at 04:45 PM JohnM
(comments are locked)
10|1200 characters needed characters left

First step: Can the instance be brought back up? Is it something as simple as someone switching off the server, or has something gone more seriously wrong?

If you can't bring the server back online (within a reasonable timeframe), then you need to recover your backups to a new server/instance. First restore the full backup, then all the transaction log backups in the order that they were taken. If you can bring the old server up for long enough, try generating a tail-log backup - http://msdn.microsoft.com/en-us/library/ms179314.aspx - and restore that on your new server to bring everything back to the most recently committed transactional state.
more ▼

answered Apr 08 '12 at 04:38 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 14 20 44

(comments are locked)
10|1200 characters needed characters left
no the instance is not up yet, when I try to connect the instance, I got the error: that network drives are inaccesible. And this might be due to the datafiles residing over there..... What can be done?? Also, the backups are good and tested on other server......
more ▼

answered Apr 08 '12 at 06:16 PM

mituljain9 gravatar image

30 4 4 6

(comments are locked)
10|1200 characters needed characters left
If the instance is not running then you need to locate it in the Services on the server and start it
more ▼

answered Apr 08 '12 at 06:17 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left
  1. I think the first thing to check is service ? Is SQL Server Service running ?
  2. If service is running,try to connect to master database ?
  3. If service does not start,restore master database from last backup ? http://msdn.microsoft.com/en-us/library/ms190679.aspx
  4. Restart the service in multiuser environment and restore the user databases.
more ▼

answered Apr 09 '12 at 08:08 PM

inder gravatar image

211 16 19 22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 08 '12 at 04:23 PM

Seen: 926 times

Last Updated: Jul 13 '12 at 08:02 AM