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

avatar image

mituljain9
30 4 4 7

(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

avatar image

JohnM
12.4k 3 7 14

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

avatar image

ThomasRushton ♦♦
39.8k 20 49 52

(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

avatar image

mituljain9
30 4 4 7

(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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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

avatar image

inder
211 18 19 25

(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.

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:

x58
x25

asked: Apr 08, 2012 at 04:23 PM

Seen: 1329 times

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

Copyright 2016 Redgate Software. Privacy Policy