If you are an SQL Server Consultant, there is never a single dull moment in your life. Quite often you are called in for fixing something, but then you always end up fixing something else! In a recent offshore project, I was tasked with tuning a high transaction OLTP server. During my work, I realized the importance of having a server that closely resembles the live database, allowing me to inspect all the settings and data. This would enable me to run queries and potentially make changes to the server settings without impacting the live environment.

However, during the process of restoring a database from their backup, we encountered a major issue. The Jr. DBA responsible for the restore was unable to restore the database due to errors. This was a cause for concern as their backup file had some issues. We attempted to restore from different backups, but the same issue persisted. The CTO, who was present at the location, became increasingly frustrated with the situation. It was then that he asked a crucial question – when was the last successful restore test performed? The answer was never. No successful restore tests had been conducted before.

This situation was stressful and alarming for everyone involved. It was clear that a lack of proper backup and restore testing had led to this predicament. To address the immediate issue, I suggested taking a backup of their database right away. The CTO wanted to observe the entire process to identify the root cause of the error. The error message we encountered during the restore attempt was:

Msg 3243, Level 16, State 1, Line 1
The media family on device 'D:\TestDB.bak' was created using Microsoft Tape Format version 1.22. SQL Server supports version 1.0.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

My first step was to take a backup of the database on another spare drive and attempt to restore it myself. As expected, the restore was successful. This indicated that there was nothing wrong with how the backup was created. The problem lay elsewhere. I inquired about their current backup process and storage method, and they provided me with a diagram.

Upon examining the diagram, I decided to test their FTP software. I uploaded the backup file to our organization’s FTP space and attempted to restore the database again. This time, it worked without any errors. This led me to suspect that there was an issue with their UNIX server. After discussing with their UNIX administrator, it was revealed that cron jobs were being used to compress and move the files to a larger drive. It became evident that this process was causing corruption in the .bak file, preventing it from being restored properly. The UNIX administrator disabled the cron job to prevent further corruption.

At this point, the CTO asked if there was a way to determine if an existing backup was free from corruption. I shared a command that can be run on the database file to check for any corruption:

RESTORE VERIFYONLY FROM DISK = N'D:\TestDB.bak'

This command allows you to verify the integrity of the database file and detect any corruption. It is a crucial step to ensure the reliability of your backups.

The question that arises from this experience is, “Do you practice this on your production server’s backup?” It is essential to regularly test your backup and restore processes to ensure data integrity. Performing restore tests on a separate environment that closely resembles your production server can help identify any issues before they become critical. By regularly verifying the integrity of your backups, you can have confidence in the recoverability of your data.

Remember, a proactive approach to backup and restore testing can save you from the stress, confusion, and potential data loss that can arise from relying on untested backups. Make it a priority to incorporate regular restore tests into your SQL Server maintenance routine.