SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Differential backups rendered useless by backup device


Differential backups rendered useless by backup device

Author
Message
sqlskj4000
sqlskj4000
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: 69
This is my situation. Hope someone can give some advice.
I created a maintenance plan for a production database that takes full backup once a week and differential backups every 4 hours. There is also a "snapshotting" device in the network that takes an image of the server at 11:30 pm daily. SQL is interpreting this as a snapshot backup and recording it in the backupset table.

So, my differential backups are really "differential" from these nightly snapshots and NOT from the actual full backup that i have from the previous weekend.

What can be done so that the diff backups ignore the snapshots and treat the full backup as their checkpoint? I tried manually marking the snapshot entries as 'is_copy_only'=1 in backupset. But that didn't seem to help.

Any pointers? Thank you!
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13938 Visits: 3697
I would check with the company that makes said "snapshot" device and find out why they are writing to the database or exactly what they are doing with SQL Server.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
sqlskj4000
sqlskj4000
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: 69
Jared,
Thanks for the reply. Apparently, some backup devices use sql VSS writer to inform sql server that the disk is being backed up, which is recorded as a snapshot backup.
I don't know if this can be avoided (except by turning off the vss writer).
i was wondering if the backup history itself could be manipulated somehow, so that the snapshot backup is not treated as a differential base.
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13938 Visits: 3697
Hmm... Not completely sure, but I would personally not be altering backup related tables. Maybe someone else will have some input.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229346 Visits: 46344
sqlskj4000 (7/10/2012)
What can be done so that the diff backups ignore the snapshots and treat the full backup as their checkpoint?


Ask the people running the snapshot backups to please not snapshot the databases. That's about the only option.

I tried manually marking the snapshot entries as 'is_copy_only'=1 in backupset. But that didn't seem to help.


No it won't. Changing the history record doesn't change the properties of the backup. It just changes the history in the table and makes it incorrect.
That's like updating a shop's inventory list and marking chicken as a vegetable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlskj4000
sqlskj4000
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: 69
gotcha. i'll find out from the network guys if the backup device properties can be adjusted to not talk to sql.
Thanks!
SQLisAwE5OmE
SQLisAwE5OmE
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3148 Visits: 3075
sqlskj4000 (7/10/2012)
gotcha. i'll find out from the network guys if the backup device properties can be adjusted to not talk to sql.
Thanks!


Please keep us posted with your findings. This is interesting to know the outcome.

Thanks,
TA

Regards,
SQLisAwe5oMe.
rferreira.dba
rferreira.dba
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 156
I have the same exact problem. The suggestions in the thread are not viable. You see, there is a reason for us to be doing a snapshot of the DB machine. We WANT the snapshot to happen. Doing away with it is not an option. Also, it is not anybody's fault by Microsoft, apparently. Check Knowledge base article 951288. It is a problem with Microsoft's VSS itself. This is really annoying and dangerous. All differential backups do work, but they are nearly useless, since they are based on a "full" backup that does actually exist. The alternative is to restore from the snapshot, and then use the differentials to bring the DB up to date. Not a good option for us.

So, in short:
1- We need to maintain the snapshots
2- Is seems to be a Microsoft issue
3- We need to prevent SQL from registering the snapshots as viable, valid full DB backups
OR
4- We need SQL to ignore the snapshots when doing transaction log and differential backups

Help appreciated. Raphael
GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229346 Visits: 46344
The only real solutions here are:
Set the backup app to snapshot the machine but exclude the databases. Some tools can, some can't. If you have a good SQL backup strategy, as it sounds like you have, then there isn't a need to snapshot the databases as well.
Set the backup app to do a 'copy only' snapshot. Probably won't be an option, haven't seen that as a setting in many tools.
Change the backup strategy to be a mix of full and log backups. If you're not doing differentials then the additional full backups won't have any effect on restoring. Not the best option, probably the last option if nothing else is possible

As soon as a full backup is taken without the copy only option, it becomes the differential base for the next full backups, regardless of how that full backup was taken. The full backup however has no effect on log backups, regardless of options or how they were taken.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rferreira.dba
rferreira.dba
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 156
I'm coming to the conclusion that my best option is to reschedule my full backups to happen exactly after the snapshot is taken.

Now, is that retarded of Microsoft or what? Smile

Let me put this another way: Is there any valid reason for Microsoft not to fix this? After all, if we look at both KB articles 951288 and 903643, the issue has been around in a form or another for quite a while. Do they have this scheduled for a fix any time soon?

Additional question:

Is there a way I can setup a trigger to fire my full backup right after the snapshot? This way I can be certain that even if someone creates and unscheduled, ad-hoc snapshot I will not have the problem. What I have in mind is:

1-Setup a trigger on the server to watch for snapshots.

2-On successful snapshot, right after, fire off a full "proper" DB backup.

Is this possible? Is there an after_successful_snapshot trigger somewhere, or something similar that I can use?

Much appreciate the help,

Raphael
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