Are you sure you can restore your database with the help of backups with minimal loss? Do you restore your data regularly? Probably many SQL Server users don’t even think of it until it strikes them. So ask yourself yet another question – What should I do about my backup to make sure I can restore it with minimal loss should a disaster occur? To put it shortly – You need to check the health of your SQL Server database regularly. Read on to find out how it’s done.
SQL Server Database Health-Check
It’s common knowledge that the best way of keeping your database safe and sound is to make scheduled backups regularly. Of course, it is necessary to work out the best backup plan that will cover all your needs. But before that you have to consider RPO (Recovery Point Objective) and RTO (Recovery Time Objective).
Recovery Point Objective (RPO)
In other words – how much data can you afford to lose? For example, your database backup plan is Full backup every 24 hours, differential every four hours and transaction log every 15 minutes, in such case if failure occurs the time for which data will be lost will not exceed 15 minutes. To be confident in your backup plan your database should be in the full recovery mode and your transaction log backups should occur every “<=n” minutes. (“n” – is the maximum amount of data loss that you can sustain).
Recovery Time Objective (RTO)
RTO – if database disaster occurs, how much time are you able to spend restoring a database to its working condition? This will determine whether you just keep your backups on a drive ready for access or actively keep a warm standby using log shipping.
Restore Your Backups
Suppose you are familiar with RPO and RTO, but beside this, you need to do regular SQL Server database backup health-check. Testing your database backups is an extremely important point which should not be ignored. This implies that from time to time you need to restore your database with the help of backups to another server or another database. Restoring database periodically is useful not only for practice but also to check the validity of backups.
Please note, if you haven’t yet tried restoring your database backup it means that you don’t have a backup!
RESTORE VERIFYONLY and CHECKSUM options
As an additional SQL Server database backup health-check, you can make all backups with CHECKSUM option. The checksum is a value calculated from the data page bytes. Equal data pages have equal checksums. Use the following T-SQL Command to perform a backup with CHECKSUM:
BACKUP DATABASE Adventureworks TO DISK = 'Adventureworks.bak' WITH CHECKSUM;
You can verify a backup with the VERIFYONLY option. It does not restore the backup, it only verifies it:
RESTORE VERIFYONLY FROM DISK = 'Adventureworks.bak'
Conclusion
So, your backup plan and RESTORE VERIFYONLY & CHECKSUM options are just additional tools that can help you to perform SQL Server database health-check, but the best way to make sure you will be able to restore your database with minimal loss is to restore all necessary backups to another server or database.
