What is the general recommendation for sql server crash recovery from mdf/ldf vs backups for least data loss?
emmalee last edited by
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.
- Sql server crashes at 04:40
- I have full backups from 1am and subsequent hourly log backups starting from 00:45.
- I have data and log files intact from the original machine (assuming only the OS or the OS went through an unrecoverable crash)
- 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:
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.
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.
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?
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.