How to Check if SQL Server Backups are Reliable

backupchecksumrestoresql serversql-server-2016

I generally have to restore databases to different servers after important data imports or other important and delicate database operations.

I need to be sure that my backups up to that point in time or transaction mark are reliable.

I don't have the time and the disk space and to do a full restore followed by a dbcc checkdb to completely verify the backup files (including corruption).

The RESTORE VERIFYONLY will check for :

  1. backup set is present and header fields of pages

  2. Are volumes readable?

  3. checksum (if enabled , BACKUP by default does not use CHECKSUM)

  4. destination drive – space check.

I use a lot the following query:

--query by Jack Vamvas - any suspect database?
SELECT DB_NAME(database_id),[file_id],page_id,
CASE event_type
   WHEN 1 THEN '823 or 824 or Torn Page'
   WHEN 2 THEN 'Bad Checksum'
   WHEN 3 THEN 'Torn Page'
   WHEN 4 THEN 'Restored'
   WHEN 5 THEN 'Repaired (DBCC)'
   WHEN 7 THEN 'Deallocated (DBCC)'
END,
error_count,
last_update_date
FROM msdb..suspect_pages

Other than restore verify only with checksum, is there any other way to check if my backups are reliable?

Best Answer

The only way to check if backup is reliable is to restore it. DBCC Checkdb is not needed after successful restore if you just want to be sure that backup is reliable.

Related Question