Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Importance of Validating Backups


The Importance of Validating Backups

Author
Message
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
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
SQL PG
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 58
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
Neha05
SSC-Addicted
SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)

Group: General Forum Members
Points: 466 Visits: 60
Nice article.
Paul Brewer
Paul Brewer
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 1293
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
Paul Brewer
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 1293
This is a link to a T-SQL and a PowerShell script that can be used to automate SQL Restores.
Slevin
Slevin
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 385
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
Thomas Abraham
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2327 Visits: 2254
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.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
markaurit
markaurit
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 56
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
jayperi 68050
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 6
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
Shahin
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 58
Hi Paul,

Thanks for the article,
Just one question,
How can I open the database in a backup without having to restore it?

Shahin
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search