patternMinor
Is it possible to have 2 backup plans for a single database?
Viewed 0 times
havedatabasepossiblesingleforplansbackup
Problem
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.
- 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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#84319, answer score: 4
Revisions (0)
No revisions yet.