July 10, 2012 at 9:46 am
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!
July 10, 2012 at 12:42 pm
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.
Jared
CE - Microsoft
July 10, 2012 at 2:10 pm
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.
July 10, 2012 at 2:15 pm
Hmm... Not completely sure, but I would personally not be altering backup related tables. Maybe someone else will have some input.
Jared
CE - Microsoft
July 10, 2012 at 2:15 pm
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
July 10, 2012 at 2:36 pm
gotcha. i'll find out from the network guys if the backup device properties can be adjusted to not talk to sql.
Thanks!
July 10, 2012 at 8:18 pm
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.
February 19, 2013 at 2:48 pm
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
February 19, 2013 at 2:59 pm
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
February 20, 2013 at 12:26 pm
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? 🙂
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
February 20, 2013 at 1:59 pm
You could create an after-insert trigger on 'backupset' table in msdb database. Check for the condition is_snapshot=1 and run the full backup job.
This is a good idea. But luckily for me, something changed in the backup plan of the n/w admins and I didn't see those annoying snapshot entries anymore.
February 20, 2013 at 2:10 pm
rferreira.dba (2/20/2013)
Let me put this another way: Is there any valid reason for Microsoft not to fix this?
Because there's nothing broken here?
The kb article you referenced refers to a problem where an incorrect insert is made into the backup history tables reflecting a backup that did not happen, not the case where you really are taking valid backup of the database. The backup history tables do not drive valid restores, they're just a record of what backups happen. The issue you have is not related to that kb article.
In your case, you are taking two types of full backups and the differential that runs is correctly based on the latest full (whatever it may be). This isn't a bug, this is documented, normal behaviour
You can get into exactly the same mess with two scheduled of native backups, where one is taken off site and not available. A differential backup is always based on the latest non-copy only full backup
Is this possible? Is there an after_successful_snapshot trigger somewhere, or something similar that I can use?
As far as SQL is concerned, that's just a database backup run by a 3rd party product. No less valid or correct than a normal database backup. Can you hook into whatever is running the snapshot backups and have that run some SQL afterwards?
Options:
- 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
p.s. This is why I always say that all SQL backups should be done the same way and that there should only be one method for the SQL backups. Native or 3rd party, not a mix of both.
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
February 20, 2013 at 2:54 pm
I agree it's not exactly a bug. But the fact remains that differentials are running based on a non-existent full backup (a backup that's part of a volume snapshot). It could've been avoided if the VSS writer recorded it as 'copy only'.
And I agree that there should be only one method of backup. But in my case we require both. Volume snapshot is the preferred restoration method, in case of a crash. SQL maintenance plans are our fallback option.
If we don't have access to the backup device to check it's options, it's either your third option or the option proposed by rferreira (full backup immediately after the snapshot).
February 20, 2013 at 3:07 pm
sqlskj4000 (2/20/2013)
I agree it's not exactly a bug. But the fact remains that differentials are running based on a non-existent full backup (a backup that's part of a volume snapshot). It could've been avoided if the VSS writer recorded it as 'copy only'.
Agreed, the problem there is that many of the tools that use the VSS writer to back up a SQL database don't have an option for the user to set copy only, and it is not (and cannot be) the default.
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
February 21, 2013 at 10:16 am
Gail beat me to the "change to Full recovery model and do Log backups instead" option.
As far as your proposed "full after each snapshot" option, you can try looking at extended events, as well as seeing if Windows Task Scheduler can trigger off of the snapshot (then you use sqlcmd to run msdb.dbo.sp_start_job), but the brute-force way is to set up a job that polls every N minutes in about the right timerange, and compare the msdb list of snapshot backups to a stateful list of prior snapshot backups. When it sees a new one, kick off a full backup and record the new snapshot.
Personally, I'd have to say that Gail's options are the best - your Full backup after the snapshot may fail for a variety of reasons, leaving your every 4 hour Differential backups silently worthless.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply