3

Is it possible to have two backup plans ?

  • allow sysadmins to have their own SQL Server 2008 set of backups for a database
  • allow DBAs to have their own separate backup plan

We have had issues with the sysadmin backups not working and would like to have a separate set for recovery.

Kin Shah
  • 62,225
  • 6
  • 120
  • 236
Randy
  • 31
  • 1
  • The database team should be responsible and have ownership of the database and SQL Server hardware and instance. Unless the DBA roles are broke out between the SysAdmin team and Database team such that SQL Server professionals are on both, there should only be one set of backups. – Steve Mangiameli Dec 04 '14 at 16:08
  • My first question would be what is the need ? – Shanky Dec 04 '14 at 19:17
  • Sounds more like the sysadmin needs to fix their backup plans. It's quite possible the issue causing their plans to fail will cause yours to fail as well. – Michael McGriff Dec 04 '14 at 19:28

3 Answers3

4

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.

Community
  • 1
Kin Shah
  • 62,225
  • 6
  • 120
  • 236
  • thank you to all who responded. This confirms what I had thought, and I appreciate the idea of Full COpy Only backups for our sysAdmin team and letting us do the diff/transaction log backups. Randy – Randy Dec 04 '14 at 20:04
3

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.

RLF
  • 14,015
  • 2
  • 33
  • 47
3

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)

Chris Woods
  • 1,771
  • 1
  • 16
  • 22