Backup and Restore Plan Strategy Matters!

The estimated reading time for this post is 2 minutes

Backup and restore plan! The operation that is getting fade and fade among SQL Server DBAs due to other backup technologies such as SAN replication, VM replication and VM HADR. Each SQL Server database needs its own backup and restore plan strategy depending on the business value. Most of DBAs and Infra-Administrators are looking at database backup plan as an optional and mostly unneeded due to mentioned new technologies, as SQL Server DBA always keep in mind that database is not such as file system, it is transactional and super sensitive towards I/O and storage sub-system. In some cases ‘Deferred Transactions’ can cause database corruption.

Why backup and restore plan strategy is needed?

Beside mentioned backup technologies, database backup and restore plan strategy is important to meet RTO and RPO and to make sure that database is recoverable and keep the business on track after any unexpected corruption or disaster.

Based on statistics:

  • 90% of businesses losing data from a disaster are forced to shutdown within two years.
  • The survival rate for companies without backup and restore / disaster recovery is less than 10%.
  • Only 44% of businesses successfully recovered information after a recent data recovery event.
  • 53% of claimants never recoup the losses incurred by a disaster.

Backup and restore plan considerations.

During planning of backup and restore strategy, keep in mind that result plan should meet agreed RTO and RPO.

Recovery Time Objective (RTO): How long would you like to spend time on database recovery after a corruption.

Recovery Point Objective (RPO): At which point would you like to stop recovery operation after a corruption.

RTORPO

Design the Backup and Restore Plan.

The optimal plan depends on a variety of factors, which the answer of following questions can lead us to design optimal plan:-

  1. How may hours a day do applications have to access the database?
  2. How frequently are changes and updates likely to occur?
  3. Are changes likely to occur in only small part of the database or in large part of database?
  4. How much disk space will a full database backup require?
  5. How do you prefer to restore the database?
  6. Does your application access to a part of database?
  7. Do you need to stop recovery operation on specific time or transaction?

Based on answers of the given questions, you need to decide wisely which type of recovery model is needed and what kind of SQL Server database backup needs to be performed.

SQL Server native database backup benefits.

SQL Server native database backup protects your business from any potential catastrophic data loss, keep in mind with a valid database backup, you can recover your data from many failures such as:-

  • Media failure
  • User errors, for example: dropping a table accidentally
  • Hardware failure, for example: a damaged disk drive or permanent loss of a server
  • Natural disasters

Take a note that SQL Server native backup solution is the only way to reliably protect your data.

Author: Hamid Jabarpour Fard

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz