Sunday, October 21, 2007
When SQL Server backups can't be restored
We always think that having a backup is more than enough to keep us afloat during a disaster. I always say that any backup will be useless unless it is tested. I came across one case where a SQL Server 2000 backup for the master database cannot be restored because it was generated using a different patch set for SQL Server 2000. The backups were generated before applying SQL Server 2000 hotfix 2191. After the patches were applied, a restore was attempted but failed. An error was encountered which specifies that the backup was generated by an earlier version of SQL Server. A best practice approach is to generate backups before and after a patch will be applied. This makes sure that you have valid backups in case something happened. It may be a bit costly as far as disk space is concerned but it sure beats not having a valid backup. It may even save you from spending sleepless nights trying to rollback to the patchset which generated the backups.