SQL Server provides a way to verify a database backup using the T-SQL command - RESTORE. This verification ensures that all backup sets are readable, checksum verification (if backup is checksum protected) and checking for sufficient space on the destination drives.

The following script uses the VERIFYONLY option of the RESTORE T-SQL command, which performs the following checks (according to Books On Line):

• That the backup set is complete and all volumes are readable
• Some header fields of database pages, such as the page ID (as if it were about to write the data)
• Checksum (if present on the media)
• Checking for sufficient space on destination devices

Please NOTE: VERIFYONLY is your first line of defence. It does not perform detailed verification (like logical corruption), which needs to be performed separately.

USE MASTER
-- Add a new backup device
-- Ensure that the SQL Server can read from the physical location where the backup is placed
--                    TYPE      NAME    	   PHYSICAL LOCATION
EXEC SP_ADDUMPDEVICE 'disk','networkdrive','\\VPCW2K8\Database Backup\Test.bak'

-- Execute the Restore operation in VERIFY ONLY mode
-- Provide the actual paths where you plan to restore the database.
-- This is because VERIFYONLY also checks for available space
RESTORE
VERIFYONLY
FROM  networkdrive
WITH
MOVE N'TESTDB_DATA'    TO N'E:\TestDB\TestDB_Data.mdf',  
MOVE N'TESTDB_INDEXES' TO N'E:\TestDB\TestDB_Idx.mdf',  
MOVE N'TESTDB_LOG'     TO N'E:\TestDB\TestDB_LOG.ldf'

-- DROP THE DEVICE
--                   Name         , Physical File (OPTIONAL - if present, the file is deleted)
EXEC SP_DROPDEVICE 'networkdrive'