The Importance of Validating Backups

Question

The Importance of Validating Backups

Viewing 15 posts - 46 through 60 (of 65 total)

You must be logged in to reply to this topic. Login to reply

Rich Mechaber SSChampion Points: 11130 More actions · Answer 1

Michael Meierruth (9/28/2012)
rmechaber (9/28/2012)
Did you see my post on page 3 of the comments?
Rich
Yes, it's 200 lines of code and comments (I like those!)
But I'm comparing 1 line of my code (a single DBCC CHECKDATABSE) with 8 lines of my trivial code trying to trap an error.
Why are they getting different results in the Messages window?
Where am I going wrong?

Sorry Michael, I didn't read your post closely enough to realize that you were getting different results back when checking the same database. I have no idea, and I'll look forward to Paul Randal's reply.

I just tried running your code block against a Broken database I downloaded from Paul's website and compared the output from a direct run [DBCC CHECKDB('broken')] with your code block (also checking DB 'broken'). I saved the output from both runs to text files and ran them through a text comparison utility; the direct run had the following lines in the output that were missing from the TRY..CATCH attempt:

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 29493257 and -4.

Rich

P.S. Add'l: I am wondering if this is an artifact of the TRY..CATCH block itself. I don't use it enough myself to know if this is related, but you might want to look here and here.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 2

It's nothing to do with DBCC - it's the try/catch eating the errors being printed. No idea how to use that correctly - it has some weird behavior.

Nothing changes the behavior of DBCC, but you're changing the way the DBCC output is consumed.

All I said was after running DBCC, the value of @@error will be non-zero if errors were found.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Rich Mechaber SSChampion Points: 11130 More actions · Answer 3

Paul Randal (9/28/2012)
It's nothing to do with DBCC - it's the try/catch eating the errors being printed. No idea how to use that correctly - it has some weird behavior.
Nothing changes the behavior of DBCC, but you're changing the way the DBCC output is consumed.
All I said was after running DBCC, the value of @@error will be non-zero if errors were found.

Thanks for confirming my suspicions Paul. As I said, I don't use it often enough to understand why that happens, but it looks like TRY..CATCH has trouble handling multiple errors in one go.

Michael: I ran my SP that I had posted against a broken DB, and b/c there is no TRY..CATCH involved, the Level 16 errors do get dumped to the output table, which means you could then query that output table. The only reason I included the final TRY..CATCH in the SP is to handle the rare possibility that DBCC CHECKDB itself fails b/c the DB errors are so severe.

Rich

Paul Brewer SSCrazy Points: 2955 More actions · Answer 4

Another significant problem with maintenance plans, well documented, is that the 'Verify Backups' check box doesn't translate to backups WITH CHECKSUM. It actuall runs a restore verify only immediately after the backup, I don't understand why they don't use WITH CHECKSUM (when possible) if this option is enabled.

If you assume some responsibility for mature production database servers with maintenance plans that have been running for years, its difficult to make the case to management that they should all be replaced with T-SQL jobs so they can use CHECKSUM.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 5

Paul Randal (9/28/2012)
It's nothing to do with DBCC - it's the try/catch eating the errors being printed. No idea how to use that correctly - it has some weird behavior.
Nothing changes the behavior of DBCC, but you're changing the way the DBCC output is consumed.
All I said was after running DBCC, the value of @@error will be non-zero if errors were found.

Paul,

OK, I confirm what you say about @@error after 'dbcc checkdb'.

So now I can catch all 3 situations:

dbcc checkdb

backup database with checksum

restore verifyonly with checksum

and if something goes wrong I get an email - heaven forbid.:w00t:

One thing that still leaves me a bit puzzled is that sometimes modifying a single byte somewhere in the database (MDF file) checkdb does not detect the database to be 'corrupt' (nor does backup with checksum). Maybe it's because the page is 'inactive', so I do a shrinkfile but I can still get this phenomena to occur. If every page (8192 bytes) in the file has a checksum then modifying a single byte at random should corrupt the database. I can only assume that not all pages get checked because they are 'not important' in some deep down technical sense.

Regarding 'restore verifyonly', there seems to be no difference between having 'with checksum' and not having it. I can corrupt a backup and I get the same error message with 'with checksum' and without it. But I'm happy doing it with 'with checksum'.

I will now stop corrupting things...:-)

Paul Randal One Orange Chip Points: 29544 More actions · Answer 6

You must be corrupting a page that is not allocated - you need to figure out which page you're corrupting and then check it's allocation status using DBCC PAGE.

For the backup, if you corrupt the backup header then restore verifyonly will catch that, with or without checksum.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

SQL PG Newbie Points: 9 More actions · Answer 7

Newbee ;

Paul this is a very good article but one common trend here is educating the masses that require these backups .

Would it be possible to providfe me with names of vitualisation tools which will aid in validating backs as well as restores.

Neha05 Default port Points: 1494 More actions · Answer 8

Neha05

Default port

Points: 1494

December 25, 2012 at 8:37 am

#1571433

Nice article.

Paul Brewer SSCrazy Points: 2955 More actions · Answer 9

I've developed a stored procedure to automate the generation of restore scripts. http://paul.dynalias.com/SQL/_layouts/15/start.aspx#/

The procedure is actually a single SQL query, you can specify a STOPAT point and it works out the FULL, DIFF, LOG files needed and generates the necessary script. It includes a CHECKDB at the end, ignores WITH COPY_ONLY , allows overrides to restore file locations using WITH MOVE and so on. It's better than anything else available free I can find.

Paul Brewer SSCrazy Points: 2955 More actions · Answer 10

This is a link to a T-SQL and a PowerShell script that can be used to automate SQL Restores. [/url]

Slevin Mr or Mrs. 500 Points: 574 More actions · Answer 11

Hi Paul,

Great article! I immediately checked my backups and realized that I was already doing this because WITH CHECKSUM is the default for compressed backups (http://technet.microsoft.com/en-us/library/ms186865.aspx). I just wanted to point that out as many people create their backups using the maintenance plans depending on the environment.

-Derek Stanley

Thomas Abraham SSChampion Points: 10761 More actions · Answer 12

Thanks for posting this Paul. It has motivated me to revise my backup/restore plans. I now restore the production backup to my test DB immediately after backup, and use CHECKSUM as well.

I'm not a reluctant DBA. But, as I wear many hats, I don't get to focus as much time on administration as I wish to. Articles like this keep me pointed in the right direction.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

markaurit Valued Member Points: 52 More actions · Answer 13

One of the best way to validate backups - restore them onto the servers your developers are using as often as makes sense in your environment, preferably daily.

jayperi 68050 Grasshopper Points: 23 More actions · Answer 14

Thanks Sir, saving us from data loss, which is life for us, and who ever depend on us. such an article, I always admires... happy holidays.

Shahin Mr or Mrs. 500 Points: 538 More actions · Answer 15

Hi Paul,

Thanks for the article,

Just one question,

How can I open the database in a backup without having to restore it?

Shahin