Is it possible to have two backup plans ?
We have had issues with the sysadmin backups not working and would like to have a separate set for recovery.
You can have sysadmins doing COPY_ONLY backups and DBA's doing regular FULL, DIFF and/or Transaction log backups.
The key is COPY_ONLY backups for allowing ad-hoc backups. But they cannot be used for point in time recovery.
Note that taking a random Transactional log backup (without using COPY_ONLY) will break the log chain and you wont be able to do a point in time recovery.
I dont understand the reasons for taking separate backups, as your entire goal should be to be able to restore your backups and bring up your system (RTO and RPO) thereby meeting your SLAs.
Alternatively, if only full backups are taken by your sysadmins, then you can Restrict users to COPY ONLY backups.
Yes, it is possible, but it requires some agreement between both parties.
If the "sysadmin" (it does not need to be a sysadmin by the way) wants his own backups, does he want all the transaction logs and differentials in addition to a full backup?
If the "sysadmin" only needs full backups, he can do his own backups WITH COPY_ONLY and use them without disrupting the DBA's scheduled coverage of backups. This could be used for populating a test server or creating a test database.
However, the DBA team is normally guarding the recoverability of all the data on the server, which means that the DBA backups (FULL, DIFF, LOGS) could provide any point-in-time restore that the backups cover. That is why you do not want the other backups to interfere.
No you cannot have two sets of backups otherwise you mess up the backup chain. Think of this occuring: Day one 1. Person A does a full backup Day two 2. Person B does a full backup 3. Then an hour later Person A does a diff backup
That diff backup that Person A does will be based off the Full backup that Person B just did, not the Full backup that Person A did the day before. This is not even taking into consideration all the transaction log backups you are likely both doing.
Your best option if you really want to do this is to use Copy Only backups for one of the people. But you MUST be certain that person ALWAYS does copy only, if it ever does a regular backup you are in trouble. The downfall of copy only is then you cannot restore to a specific point in time. (Unless I'm mistaken, copy only tlog backups won't work like you want in this situation)
