Do I need backup of my database?

Monday, August 1, 2011 |

If you are working as a DBA or you have responsibility to maintain your SQL Server database up & running, take my words, you have NO OPTION of full database backup.

Recently I came to know that one person is not taking backup just because he has RAID and Mirroring setup so he is least worried about backup. Believe me, no matter what redundant hardware / software and high availability setup you have, you MUST take backup regularly because even good and up-to-date disaster recovery system could get failed. So, you never know, when and how do you need your latest backup file.

Let us discuss why database backup is really that much important.

Suppose you have high availability solutions like RAID or server clustering but what if you hardware gets corrupt and may be complete disk array get corrupt? You have to build up your database from backup only.

Sometime, catastrophic events or natural disasters like flooding, earth quake etc. could damage your hardware or may be complete data center and if you have no backup at other location, you have no place to go for.

May be sometime Security issues comes to the picture and somebody intentionally or unintentionally damage you data which could be affected adversely and in that situation you might look for the latest database backup to restore that data.

Sometime your OS or even your SQL Server gets corrupted and you might need to restore data from backup itself.

In short, I would HIGHLY recommend database backup as “Prevention is always better than cure”. So, keep strong backup policies with full database backup, differential database backup and transaction log backup, depends on your need and business policies.

BTW, I am not against the high availability concepts like mirroring, clustering, replication or log shipping. You should implement those too as per your business needs but also MUST keep strong backup policies.

Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
Ask me any SQL Server related question at my “ASK Profile


Mark Willium said...

Hello Ritesh,

I am totally agree with you. SQL server database gets corrupt due to several reasons like natural disaster (flood), human errors, power failure, virus attack and many more. Backup is the best way to recover SQL server database from any types of corruption. In case, you don't have backup of your database then you should try any trusted SQL recovery software. Third party SQL recovery software successfully repairs and recovers corrupt SQL database in most of the cases.