x

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, 2012 at 04:23 PM in Default

mituljain9 gravatar image

mituljain9
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, 2012 at 04:32 PM

JohnM gravatar image

JohnM
6.8k 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, 2012 at 04:40 PM ThomasRushton ♦

Thanks!

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

Apr 08, 2012 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, 2012 at 04:38 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 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, 2012 at 06:16 PM

mituljain9 gravatar image

mituljain9
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, 2012 at 06:17 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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, 2012 at 08:08 PM

inder gravatar image

inder
211 17 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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x46
x18

asked: Apr 08, 2012 at 04:23 PM

Seen: 1092 times

Last Updated: Jul 13, 2012 at 08:02 AM