-2

I'm learning steps to recover from sql server OS or OS disk crash. Assuming the OS or OS disk is corrupted and unrecoverable.

I know that a HA solution like fci or ag is better suited to prevent such issues, but assume there is no fci/ag.

Assume:

  1. Sql server crashes at 04:40
  2. I have full backups from 1am and subsequent hourly log backups starting from 00:45.
  3. I have data and log files intact from the original machine (assuming only the OS or the OS went through an unrecoverable crash)
  4. Tail log backup cannot be taken because the OS has crashed and so the server is inaccessible

I have 3 options for recovery after installing the OS and sql server software:

  1. Shutdown sql services, Restore master, msdb, ssisdb, and for user databases do the following: Apply full backup from 1am and subsequent 3 log backups which will get me upto 03:45. Therefore I loose almost 1 hours of data.

  2. Shutdown sql services, restore master, msdb, ssisdb, copy the mdf/ldf files from the previous disk, move them into the new server, attach db. Therefore I don't loose any data.

  3. Shutdown sql services, replace (not restore, not attach) system dbs master, msdb, ssisdb files; for user dbs - copy the mdf/ldf files from the previous disk, move them into the new server, attach db. Therefore I don't loose any data.

What is the recommendation of experienced database professionals on the above approaches?

Improve this question

1 Answer 1

Reset to default
1

It depends on the actual issue. If your disks are corrupt, then option two and three are risky and may not even be possible. If it's truly an isolated incident that only affected the OS and minimizing data loss is a priority then option number two is a possibility.

But you generally just want to have good backups that are taken at a frequency that's tolerable to the amount of data loss within your RPO (Recovery Point Objective). Transaction Log backups can be taken at a much higher frequency than every hour. A lot of people take them every 15, 5, or even 1 minute, depending on their RPO. (I personally take them every 5 minutes.)

I also prefer to store the database backups on a share off of the SQL Server instance's machine, so that they're still accessible should drives become inaccessible due to whatever issue is occuring on the instance's box.

Having proper backups, that are kept decoupled from the server they're taken on, and are routinely checked for corruption is the best way to ensure utmost resiliency of your databases.

Improve this answer
11
  • In your 1st paragraph, which option number do you mean is possibility?
    – variable
    May 8 at 1:03
  • @variable Sorry I meant option number two.
    – J.D.
    May 8 at 2:39
  • In option 3 the intention is to make sql server think the previous master is the new master without doing a restore or attach. Is this option out of scope?
    – variable
    May 8 at 4:50
  • @variable In option 3, you say "attach db". I don't believe it's possible to formally attach system databases. Yes, you can just replace the MDF and LDF files of those databases while the instance is offline and it may work, but that's a really unsupported way of managing the system databases. The recommended approach is using a backup restore.
    – J.D.
    May 8 at 12:24
  • Ok thanks but I didn't say attach for option 3 (for system dbs). I said replace.
    – variable
    May 8 at 12:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.