We have a 3rd party Payroll application which uses a SQL database on our server. On the database server we have weekly full backups, nightly differential and regular log backups. These are kept for a period of time to meet the business DR requirements.
The problem we have is that when a payroll is run, the payroll department require that a backup of the database be taken and kept for a specified period of time (5-7 years depending on the country for legal reasons). When they run the backup from the Payroll application it does a full backup. This obviously puts out our differential backups, as SQL now thinks it has a new full backup.
Is there a way that we could capture the backup script coming from the Payroll application and alter it to be Copy-Only, or is our only options to get the program changed and/or do the backup outside of the payroll program?
=======
Update to try and explain our goal a bit better:
What we want to do is intercept the backup statement coming out of the payroll program, add the command copy-only to it, and then pass it to SQL, and then return the results back to the payroll program.
Best Answer
Not really an answer - it's not possible to do it.
2 work arounds that we considered:
We went with option #2, and scheduled a copy-only backup to run every week on payroll day, and also at the end of the financial year. We told them that we can also run the backup whenever they ask us to do it. We also demonstrated that we can restore any database up to the minute based on our RPO. It made some people in HR disappointed, but the manager signed off on it.
We also disabled their access to backup to prevent any accidents.