x

Validate a backup file before configuring mirroring

Does anyone restore a backup and run DBCC CHECKDB to check for errors before they restore the backup with no recovery to start Mirroring?

I'm guessing that you should just to be sure there are no errors in the file/restore process?

What happens if the mirror gets a corrupt sector? Would mirroring fail if normal processing touches that data?

If the sector is dormant whilst mirrored I assume you get no warning until the mirror goes live and the data gets requested or a CHECKDB finds it?
more ▼

asked Aug 22 '12 at 02:32 PM in Default

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Problem is you have to then re-restore to get back to a mirroring state (i.e. restoring), essentially overwriting the database again, and that's now a different database to the one you've just DBCCd.

And yes you are right, you won't get any indication that the mirror is corrupted until something touches the corrupted page.

Think the solution is to get mirroring up and running , then take a snapshot, and DBCC against that.
more ▼

answered Aug 22 '12 at 06:05 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

right, so specifically on Q4 - what if there is corruption on the mirror and then the activities in the primary database cause that sector to be changed. will mirroring fail?

but when I take the snapshot and restore isnt that a different database?
Aug 22 '12 at 06:10 PM Fatherjack ♦♦

TBH not sure, probably best to test. But I wouldn't be surprised if it did fail the mirroring.

The snapshot is a static copy of the original db - you are not restoring it anywhere. For eg on my laptop I have a 'sandpit' database that I mirror from one instance to another. On the mirror instance I generated a snapshot

 CREATE DATABASE KevSnap ON
 ( NAME = Sandpit, 
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2_I2\MSSQL\DATA\KevSnap.ss' )
 AS SNAPSHOT OF sandpit;
 go

and then I can connect to that database snapshot and run dbcc :

use KevSnap
go
dbcc checkdb
Aug 22 '12 at 06:20 PM Kev Riley ♦♦
Cool. not used snapshots in anger. and wont be able to as we only have Std Edition! :(
Aug 22 '12 at 06:25 PM Fatherjack ♦♦
:( sugar
Aug 22 '12 at 06:31 PM Kev Riley ♦♦

Have you tried the virtual DB software out there? That is if you are wishing to find potential DB corruption.

I know you are a FoR, but Idera do a decent tool.
Aug 22 '12 at 07:05 PM sp_lock
(comments are locked)
10|1200 characters needed characters left

+1. Very nice. Until now we are not doing it as there is a process of DBCCing some databases randomly and some regularly (I am sure you would be running such kind of process in your environment as well). So a viable option may be to restore the already restored and DBCCd backup and then applying all the subsequent log backups? Which means you would not have the different database restored which is not DBCCd. As always, it all depends upon the environment and constraints.

But it absolutely makes sense to restore a fully verified DB to take full benefits of Automatic Page Repair. This same repair process may also answer some of the possible concerns raised by your questions.

more ▼

answered Aug 23 '12 at 03:23 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x42
x12

asked: Aug 22 '12 at 02:32 PM

Seen: 781 times

Last Updated: Aug 23 '12 at 03:23 PM