How to check if backups are reliable?

2 votes

1 answer

470 views

sql-server backup sql-server-2016 restore checksum

                          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?

Asked by Marcello Miorelli (17274 rep)

Jul 16, 2019, 11:11 AM
Last activity: Apr 22, 2021, 02:40 PM

How to check if backups are reliable?

Related Questions