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.
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:-
- How may hours a day do applications have to access the database?
- How frequently are changes and updates likely to occur?
- Are changes likely to occur in only small part of the database or in large part of database?
- How much disk space will a full database backup require?
- How do you prefer to restore the database?
- Does your application access to a part of database?
- 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