August 13, 2014 at 8:56 am
Hi guys,
A question about backups and ddl triggers. I have an application using one of my sql servers, that takes daily full backups internally. No real issue with this except it breaks my backup chain. I have made multiple calls to the vendor to try and disable this or even just change it to a copy-only backup to no avail.
Ideally I would be able to intercept the backup job with a DDL trigger, parse out the backup statement and add "COPY_ONLY" in there. Everyone is happy. For the life of me I cannot figure out how to have this trigger fire on a backup event.
Any ideas out there?
Thanks!
August 13, 2014 at 9:11 am
You have differential backups as part of your backup strategy?
The list of DDL operations that can have a trigger created on them is listed here: http://msdn.microsoft.com/en-us/library/bb522542.aspx, and backup database is not one of them.
Besides, DDL triggers are AFTER triggers, so it would only fire after the backup has already run.
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
August 13, 2014 at 9:21 am
Yes I have the differentials. I have a very thorough backup strategy across the corporate infrastructure; Differentials every 6 hours, Log backups hourly. All sorts of maintenance.
As I said, this is just one ridiculous application. I work at a bank. I'm not sure if you're well versed in the banking industry but some of the software and vendors they use are just awful.
Thanks for the link; I had gone over that as well. I was hoping someone might know of a less than documented feature somewhere that would allow me to intercept the backup process.
Thanks so much for the reply.
August 13, 2014 at 9:34 am
You could deny the application account whatever permission it needs to run backups, assuming that doesn't break anything else.
John
August 13, 2014 at 9:38 am
Just checking... I keep running into people who think full backups without copy_only break the log chain, so....
Worked at a bank 5 years.
Denying permission is probably easiest, unless the damn app runs as 'sa'
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
August 13, 2014 at 9:42 am
Full backups that do not use the 'COPY_ONLY' option do indeed break the log chain; am I missing something here? It has always been that way as far as I know.
Denying permission is indeed the easiest, but I would like to have the backups continue as they are logged inside the application and give the managers and power users a nice warm and fuzzy feeling.
August 13, 2014 at 9:44 am
App does not run as SA, but it does run with a db_owner permission of it's own database(cringe, I know) some developers just like to watch the world burn when they make their applications.
August 13, 2014 at 9:48 am
No, COPY_ONLY only breaks the differential "chain".
Managers and users shouldn't need to worry about warmth and fuzziness - that's why they pay you to administer their databases. Although if the account is db_owner (and I see that in far too many applications) then I don't think you can stop it from backing up the database anyway. Where does it back up to - the default backup location, or can you specify the location?
John
August 13, 2014 at 10:05 am
yes so sorry; it doesn't break the log chain, it breaks my diff chain. How quickly we forget sometimes. I will bury my head in the sand later.
The warm and fuzzies are unavoidable; these are the big shots at the bank and they want them.
Again, I know none of this is ideal but there are times when the business rules will trump what the dba says.
I think this has to do with the fact that they are not on a regular schedule; they are kicked off right before the EOD processes start, then right after; then again right before SOD. the application queues them and it is not at the same time every day; could vary by as much as 30 minutes. For this reason, they are dead set on keeping them.
Really appreciate all the feedback everyone; tanks so much again!
August 13, 2014 at 2:12 pm
josborne 48714 (8/13/2014)
Full backups that do not use the 'COPY_ONLY' option do indeed break the log chain;
No they don't, and they never have. The only thing that COPY_ONLY does on a full backup is not reset the differential base. That is it.
Unfortunately you're going to have to address this at the source. Maybe with a document that details the risks of having these extra backups resetting the differential base, with an example of what problems they could cause (eg you need to restore but whereever these backups are stored is not available and so you lose X days data. Use that as motivation for why these non-standard backups need to have a special option set (copy_only)
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
August 13, 2014 at 2:12 pm
Back up policy on database is set up based on the business requirements as well. Looks like this particular database has a policy of its own against the standard back up policy of your agency. You need to document the risks/concerns of a non-standard approach and the related restore, DR et al impacts. Managers love to react when they see verbage like 'non-standard' , 'risks' et al 😛
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply