For an instructor lead, in-depth look at learning SQL click below.
One of the most important tasks for any Database Administrator (DBA) involves ensuring the integrity of database backups. This step can make the difference between a fast recovery and total data loss in the event of a disaster. This blog post will explore a few SQL Server techniques for verifying the integrity of database backups.
SQL Server’s Built-In Backup Verification
SQL Server has a built-in command to BACKUP and RESTORE databases. Let’s start with a simple example to take backups and use the VERIFYONLY operation:
|
1 2 3 4 5 6 7 8 9 |
-- Backup a database BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB.bak'; -- Verify a database backup RESTORE VERIFYONLY FROM DISK = 'C:\Backups\MyDB.bak'; |
VERFIYONLY is a built-in functionality that allows SQL Server to quickly check the backup’s structure integrity. This does not read all the data, so it does not guarantee that a restore will be successful.
Restoring for Full Verification
If more thorough check is needed then, Database Administrators often run a RESTORE DATABASE command with the NORECOVERY option. This ensures that the backup can be restored, but it stops before it allows the database to be used:
|
1 2 3 4 5 6 |
-- Restore a database as a test RESTORE DATABASE MyDB_test FROM DISK = 'C:\Backups\MyDB.bak' WITH NORECOVERY; |
Note that this option requires disk space to hold the restored data, and since the restoration procedure can not be tested too frequently due to its resource-intensive nature, it should be scheduled for downtimes or non-peak hours.
Checksum Verification
Another option SQL Server offers is generating a checksum before writing the backup pages to the disk. Later on, this checksum is checked while reading the data from the disk providing an additional layer of security.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Backup a database with checksum BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB.bak' WITH CHECKSUM; -- Verify a database backup with checksum RESTORE VERIFYONLY FROM DISK = 'C:\Backups\MyDB.bak' WITH CHECKSUM; |
Relying solely on the SQL Server’s built-in functionality may not be enough in more demanding, mission-critical environments. Database Administrators often use third-party solutions or write custom scripts to verify the integrity of the backups more thoroughly.
Conclusion
Ensuring the integrity of SQL Server backups is a critical task that should never be neglected. In this post, we discussed utilizing SQL Server’s built-in commands and discussed the importance of more thorough checks, restoring a test database, and using checksums. Remember that reliable backups are essential for successful disaster recovery strategies.