To avoid the aforementioned issue, follow these recommended backup strategies with MSSQL server plugin in BDRSuite clients:
a) Configure a separate backup job for the 'Master' database with 'FULL' backup schedule alone, as 'DIFFERENTIAL and TRANSACTIONAL' log backups cannot be done on the master database. Also, 'master' has to be restored first when restoring SQL Server for disaster recovery.
b) Databases with 'Recovery Model' as 'Simple' should be configured with 'FULL' backup type and optionally with 'DIFFERENTIAL Log' backup types, as 'TRANSACTIONAL' log backups cannot be performed.
c) Databases with 'Recovery Model' as 'Full' or 'Bulk Logged' should be configured with 'FULL' backup type and optionally with 'DIFFERENTIAL' and 'TRANSACTIONAL LOG' backup.
Additionally, it is recommended to implement the following backup schedule:
1. Execute a full database backup on a monthly basis.
2. Perform differential backups on a weekly basis.
3. Perform transaction log backups on a daily basis.
Adjust the frequency based on the size of your database and acceptable data loss for your company. If databases are changing rapidly, consider a combination of options such as differential backups and transaction log backups to minimize data loss.
For clients with rapidly changing databases, setting a maximum number of kept differential backups helps manage backup space usage. Transaction logs, capturing changes since the last backup, are automatically purged during the next Full/Differential backup schedule.
Implementing a well-thought-out backup schedule aligns with best practices and ensures efficient MSSQL Server backup operations with BDRSuite.