How to Verify Database Backup file is not corrupted one in SQL Server? How to Validate Database Backup in SQL Serer?

How to Verify Database Backup file is not corrupted one in SQL Server? 
How to Validate Database Backup in SQL Serer?

You’re probably running regular database backups. Most people do. Some don’t, but that’s a different discussion. There’s an old saying “Your data is only as good as your last backup.” That’s very true. But, there’s a little known corollary to this: “Your backups are only as good as your last restore.” It’s great that you’re backing up your databases, but you need to do more. You need to test your backups.

The ultimate test for any backup is a restore to a server, but there are a few other things you can do as well. Let’s go over them in the order of their importance in validating that the backups you have are good.

CHECKSUM

The first thing you can do to ensure you have a good backup is to include CHECKSUM in the WITH clause of your backups, like this:

BACKUP DATABASE AdventureWorks2008R2TO DISK = 'c:\bu\MovieManagement.bak'WITH INIT, CHECKSUM;

 

The CHECKSUM is a mathematical construct of a fixed length that gets generated from the page of data. The math will always arrive at the same number for the same page of data. The CHECKSUM value gets written with the page during the backup process and it validates that the page is intact prior to writing it to the backup. This is a good way to validate the media as you do the backup. Then, you can use the CHECKSUMs later during a RESTORE VERIFYONLY operation (see below) to validate the backup media is intact.
This does come with additional cost. It adds overhead, so if you have a very large backup that currently takes a long time, it’ll take longer. But, if you have a very large backup that takes a long time, wouldn’t you want to know that the pages being written to disk are actually intact?

VERIFYONLY

Another way you can ensure that your backups are good is to use RESTORE VERIFYONLY like this:

RESTORE VERIFYONLYFROM DISK = 'c:\bu\MovieManagement.bak';

 

VERIFYONLY will process the backup and perform several checks. First, it can find and read the backup file. Believe it or not, that’s a good first step. Far too many people will assume that they can restore a file that is either incomplete or inaccessible. It also walks through the CHECKSUM information if you used CHECKSUM in the backup (see above). This will validate that the backup media is in place. That can be a costly operation if the backup file is very large, so I don’t know that I’d run this check from my production system if I could help it. Finally, VERIFYONLY checks some of the header information in the backup. It doesn’t check all the header information, so it’s still possible for a backup to pass VERIFYONLY but still not restore successfully to the server. Which brings up the best way to validate your backups, RESTORE.

RESTORE

Like I said at the beginning of this, the best way to know that your backup is intact is to run a RESTORE. It’s very much like taking off and nuking the site from orbit, it’s the only way to be sure. If you successfully run a RESTORE of a backup, then you know that backup is intact. Yes, other things might happen to the file later, but for a moment in time, you’ve validated that your backup and storage mechanisms are working. By the way, notice that all these checks are only concerned with the backup structure and the database structure. What if what you’re backing up is junk?

CHECKDB

You might think that running CHECKDB should be done first, prior to running a backup. And, in most circumstances, you’d be right. However, it is possible to find yourself in a situation where DBCC CHECKDB is extremely expensive. You might not have a big enough maintenance window to run the CHECKDB and get a backup completed. In that case, run the backup, it’s the more important operation. But, you can still validate your database. Because the backup is an exact copy of the database, if there are corruption issues (not found by the CHECKSUM) they’ll be backed up as well. Running a RESTORE and then running CHECKDB will enable you to ensure that your backup is intact and that the data in the backup is intact as well.

Summary

You know that you need to protect the information for your organization. You’re already running your backups. Now take the next step and ensure that those backups are good. Test them. Just remember, that the very best test is a complete restore.

let see sample




When you take backup command with checksum as see below

 After that you can check that Backupfile with checksum have valid or not

 then you can also view detail information as see below

Also if you had backup with checksum it also works both RESTORE HEADERONLY WITH CHECKSUM & RESTORE HEADERONLY without checksum as see below

 Please note the extra time taken for a restore verifyonly with checksum command.
 Another observation that may be noted is that a backup without a checksum and a backup with checksum  takes about the same time.

Also note that if you did nt use Backup Log with checksum then you cant able to use it on 
RESTORE WITH HEADERONLY checksum it will throw error 

After that when you mention in RESTORE WITH HEADONLY OPTION it will throw error as see below

 But if you are not use with checksum option then it will give result. Because you have to decide before specify you need to take backup with checksum then only you can use it on restore verifyonly option

These same strategy followed on backup  as see below
Database backup taken without specify checksum

if you did nt specify with checksum on restore it gives result as see below

    As If you specify with checksum it gives error see below

If you want to take backup on two locations you can specify as see below


BACKUP DATABASE TBK
TO DISK='E:/TBK_bk_Device1E.bak',Disk='F:/TBK_bk_Device2F.bak'

 it will save in E Drive and F Drive in local

 It saves same 92.5 MB size in both devices

 
 We can also see the mdf and ldf files using backup file as see below

Restore Filelistonly
FROM DISK='E:/TBK_bk_Device1E.bak'

We can also FIRST LSN,LAST LSN,Backp start date and finish date as see below


Restore headeronly
FROM DISK='E:/TBK_bk_Device1E.bak'

We have to verify Database backup is valid and it should be restorable or not using but you need to give all backup file as see below

Restore verifyonly
FROM DISK='E:/TBK_bk_Device1E.bak'-----------you should not give only 1 device location if you saved in 2 locations

So if you give 2 devices it works fine and tells you whether it is valid or not










MS SQL Server page (block) checking during backups

The corresponding term in MS SQL Server for datafile blocks is a page.  In SQL Server, most database administrators create the backup database task through maintenance plans. But one cannot rely on these maintenance plans for page (block) checksum and torn-page detection during backups. Even if the "Verify backup integrity"  option is enabled as part of the backup database task in a maintenance plan, it does nothing more than checking whether the backup media is readable without having to perform an actual restore. So it is superficial in nature.

Therefore, it is strongly recommended that MS SQL Server database administrators create separate T-SQL scripts or stored procedures to explicitly backup databases using the with checksum option. 

For example, a database called AdventureWorks can be backed up using the below command.

backup database AdventureWorks  to disk = 'D:\backups\AdventureWorks_01032015.bak' with NOINIT,CHECKSUM;

Additionally, in the same T-SQL script or stored procedure, verify backup integrity using the with checksum option. For example,

restore verifyonly from disk = 'D:\backups\AdventureWorks_01032015.bak' with CHECKSUM;

SQL Server will check every page in the backup file by reading and recomputing the page checksum and then compares it to the recorded page checksum in the backup file to ensure integrity.

There are three states the database can be in when its damaged:

1. ONLINE
   • If it’s one of the data files that is damaged and the damage is hit during a query or some other operation then the database will remain online and accessible.
2. RECOVERY PENDING
   • The database will be in this state if SQL Server knows that recovery needs to be run on the database but something is preventing recovery from starting. This is different from SUSPECT because there’s nothing to say that recovery is going to fail – it just hasn’t started yet.
   • An example of this is when the database wasn’t cleanly shut down (i.e. there was at least one uncommitted transaction active at the time the database was shut down) and the log file has been deleted.
3. SUSPECT
   • The database will be in this state if the transaction log is damaged and it prevents recovery or a transaction rollback from completing.
   • Some examples of this are:
     • When the database wasn’t cleanly shut down and recovery tries to read a corrupt data page or comes across a corrupt log record.
     • A regular transaction rolls back and tries to read a corrupt data page or comes across a corrupt log record.

You can check the state of a database in the sys.databases catalog view:

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'master';

or by using the DATABASEPROPERTYEX function:

SELECT DATABASEPROPERTYEX (N'master', N'STATUS');

several times over the last few weeks for an example corrupt database to play with, and for testing logic built around DBCC CHECKDB.

The attached WinZip file contains a backup of a simple 2005 database called ‘broken’ (I can do a 2000 one too if there’s enough demand). It has a simple table called ‘brokentable’ (c1 int, c2 varchar(7000)) with one row in it. The table has a single data page with page ID (1:143) that I’ve corrupted so the page checksum is corrupt.

This means you can try all sorts of cool things. Below I’ve listed a few things you can try out to see what would happen on your database if a checksum failed.

Restore

I didn’t do anything special when backing up this database so restoring works just fine, even though it contains a corrupt page. 

RESTORE DATABASE broken FROM DISK=‘c:\broken.bck’

WITH MOVE ‘broken’ TO ‘c:\broken.mdf’,

MOVE ‘broken_log’ TO ‘c:\broken_log.ldf’;

GO
Processed 160 pages for database ‘broken’, file ‘broken’ on file 1.

Processed 2 pages for database ‘broken’, file ‘broken_log’ on file 1.

RESTORE DATABASE successfully processed 162 pages in 0.314 seconds (4.203 MB/sec).

DBCC CHECKDB

CHECKDB throws us some nice errors:

DBCC CHECKDB (‘broken’) WITH NO_INFOMSGS, ALL_ERRORMSGS;

GO

Msg 8928, Level 16, State 1, Line 1

Object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data): Page (1:143) could not be processed. See other errors for details.

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data), page (1:143). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.

CHECKDB found 0 allocation errors and 2 consistency errors in table ‘brokentable’ (object ID 2073058421).

CHECKDB found 0 allocation errors and 2 consistency errors in database ‘broken’.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (broken).
Query errors

Any query that touches that page is going to fail with an 824 error:

SELECT * FROM broken..brokentable;

GO

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7232c940; actual: 0x720e4940). It occurred during a read of page (1:143) in database ID 8 at offset 0x0000000011e000 in file ‘c:\broken.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.
Backup with CHECKSUM

If you have page checksums turned on, you should always use the WITH CHECKSUM option on backups to validate the page checksums as they are read:

BACKUP DATABASE broken TO DISK=‘c:\broken2.bck’

WITH CHECKSUM;

GO

Msg 3043, Level 16, State 1, Line 1

BACKUP ‘broken’ detected an error on page (1:143) in file ‘c:\broken.mdf’.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.
But we can force it to backup. If this is the only copy of the database we have, and we’re being forced to run repair, for instance, then we want to make sure we have a backup to restore from. Even a backup that contains a corrupt database is better than no database at all. In this case, we need to use the CONTINUE_AFTER_ERROR option.

BACKUP DATABASE broken TO DISK=‘c:\broken2.bck’

WITH CHECKSUM, CONTINUE_AFTER_ERROR;

GO

Processed 160 pages for database ‘broken’, file ‘broken’ on file 1.

Processed 1 pages for database ‘broken’, file ‘broken_log’ on file 1.

BACKUP WITH CONTINUE_AFTER_ERROR successfully generated a backup of the damaged database. Refer to the SQL Server error log for information about the errors that were encountered.

BACKUP DATABASE successfully processed 161 pages in 2.025 seconds (0.651 MB/sec).
RESTORE VERIFYONLY

Now that we have a backup that has checksum information in it, let’s see how we can check whether the backup is valid:

RESTORE VERIFYONLY FROM DISK=‘c:\broken2.bck’;

GO

The backup set was written with damaged data by a BACKUP WITH CONTINUE_AFTER_ERROR.

Isn’t that cool? It tells us that the backup was already corrupt when it was written. Ok – let’s ask it to specifically check the checksums in the backup:

RESTORE VERIFYONLY FROM DISK=‘c:\broken2.bck’

WITH CHECKSUM;

GO

The backup set was written with damaged data by a BACKUP WITH CONTINUE_AFTER_ERROR.
Same thing. What about if we try to check the checksums on the initial backup?

RESTORE VERIFYONLY FROM DISK=‘c:\broken.bck’

WITH CHECKSUM;

GO

Msg 3187, Level 16, State 1, Line 1

RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.

Msg 3013, Level 16, State 1, Line 1

VERIFY DATABASE is terminating abnormally.
RESTORE

How about we try to overwrite the existing ‘broken’ database with the one from the second backup that has checksum information in it?

RESTORE DATABASE broken FROM DISK=‘c:\broken2.bck’

WITH REPLACE;

GO
Msg 3183, Level 16, State 1, Line 1

RESTORE detected an error on page (1:143) in database “broken” as read from the backup set.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

It won’t let us because the backup contains corrupt data. But we can force it if we need to with CONTINUE_AFTER_ERROR again.

RESTORE DATABASE broken FROM DISK=‘c:\broken2.bck’

WITH REPLACE, CONTINUE_AFTER_ERROR;

GO

Processed 160 pages for database ‘broken’, file ‘broken’ on file 1.

Processed 1 pages for database ‘broken’, file ‘broken_log’ on file 1.

The backup set was written with damaged data by a BACKUP WITH CONTINUE_AFTER_ERROR.

RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.

RESTORE DATABASE successfully processed 161 pages in 0.392 seconds (3.364 MB/sec).
Isn’t that cool? It works BUT it tells us that the backup set contained corrupt data and that the database was restored but could have corrupt data in.

Summary

Have a play about with this database to familiarize yourself with the kind of responses you’ll get from the various tools when a corruption exists, and how to work around it if need be. Let me know if you want to see any particular kinds of corruptions explored, or want a database with something specific corrupted in.

Ref:

http://blog.dbauniversity.com/2015/01/how-to-backup-sql-server-database-with-checksum.html

https://www.simple-talk.com/sql/backup-and-recovery/backup-verification-tips-for-database-backup-testing/