I am using SQL Server 2008 R2. What happens if a database backup (Full/Differential/Transaction Log) is in process and there is a sudden power outage or the system crashes? Does that corrupt the backup set?

If so, then what are the available options so that if and when a database restore needs to be done I don't end up with a corrupted backup set.

If not, then what will be the status of the backup that was in process?


Obviously the backup can't be completed if it was still in progress when electricity stopped flowing. That doesn't necessarily mean it is corrupt, but it is almost certainly going to be unusable.

In the unlucky (though unlikely) event that you lose power during a full backup, and the database itself was also corrupted as a result (this is not necessarily the case; often databases will recover from a power outage just fine), then you would have to resort to the previous full backup + any diff/log backups taken in the meantime. Similar if you lose power during a log backup, you would have to resort to the last successful log backup. So your recovery plan should account for this possibility (meaning you want your log backups to happen frequently enough that they cover your tolerance for data loss).

Once the database itself is recovered and confirmed to be fine, start the backup chain over by creating a fresh full backup.

BTW this is why people have redundant power supplies and/or UPS devices. This is certainly an avoidable problem at not a very high cost.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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