1

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.

  • DENY backup to the account used by the app then create an SP that does the right backup and get payroll runners to run that SP each time. Be sure to setup some kinda logging for the SP execution and if really critical, setup alerts if the SP hasn't been run in x time depending on how often payroll runs are conducted. – SQLmojoe Nov 13 '15 at 18:21
2

Not really an answer - it's not possible to do it.

2 work arounds that we considered:

  1. Creating a Stored Proc/SQL Agent Job that could be executed by the user that did the backup
  2. Convince the business that they can trust us to do their backups for them

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.