I am going to show you why you should be using checksum options on your backups (and restores).
I AM ACTUALLY NOT GOING TO WRITE THE CODE TO FORCE THIS CORRUPTION. I am starting to feel bad spreading/writing about the commands involved.
Anyways I did what was necessary for this demo and running a SELECT we now have:
SELECT * FROM testtable
SQL Server detected a logical consistency-based I/O error:
incorrect checksum (expected: 0xe41c795b; actual: 0xe41c3c5b).
It occurred during a read of page (1:55) in database ID 10 at offset 0x0000000006e000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBMaint2008.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Let’s create some backups without checksums.
BACKUP DATABASE [DBMaint2008] TO DISK = 'C:\sqlserver\DBMaint2008pre.bak'
It works- BACKUP DATABASE successfully processed 186 pages in 0.140 seconds (10.351 MB/sec).
--The backup set on file 1 is valid RESTORE VERIFYONLY FROM DISK = 'C:\sqlserver\DBMaint2008pre.bak'
We did a RESTORE of the DATABASE successfully – processed 186 pages in 0.232 seconds (6.246 MB/sec).
RESTORE DATABASE [DBMaint2008DR] FROM DISK = N'C:\sqlserver\DBMaint2008pre.bak' WITH FILE = 1, MOVE N'DBMaint2008' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBMaint2008DR.mdf', MOVE N'DBMaint2008_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBMaint2008DR_log.LDF', NOUNLOAD, STATS = 5 GO
Are things really ok on this newly recovered database?
DBCC CHECKDB ('DBMaint2008DR')
I dont think so – CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DBMaint2008DR’.
At least let’s have a checksum in place for protection from this kind of thing.
BACKUP DATABASE [DBMaint2008] TO DISK = 'C:\sqlserver\DBMaint2008POST.bak' WITH CHECKSUM, STATS = 10 GO
12 percent processed.
21 percent processed.
Msg 3043, Level 16, State 1, Line 14
BACKUP ‘DBMaint2008’ detected an error on page (1:55) in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBMaint2008.mdf’.
Msg 3013, Level 16, State 1, Line 14
BACKUP DATABASE is terminating abnormally.
Can you imagine if you did neither Checksum or consistency checks? I don’t think I want to imagine such an environment.