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.