SQL Database Backup Policy Recommendations

SQL Backups

Please note, these are simply guidelines. Your organization's Information Technology or SQL DBA professionals may determine different backup policies and backup retention policies based on your organization's infrastructure, recovery policies, or needs.

The best way to automate backups in SQL Server is to use the built-in SQL Server Maintenance Plan Wizard. This wizard allows you to create a plan to back up databases on a regular schedule and store the backups in a secure location. It is also possible to configure the plan to delete old backups to ensure that the backup storage space is not exceeded.

We propose a backup strategy which encompasses transaction logs, differential backups, as well as full backups.

Transaction Log Backups
We recommend a transaction log backup strategy that will be executed every 15 minutes to ensure that all changes are captured, and that the transaction ldf container file does not grow to unsustainable sizes.

Let's hypothesize that a Venio project generates 10MB of transaction log activity every minute. After 15 minutes, you've generated 150MB of log activity - but that also means that since you need to have your Venio database in FULL recovery mode, your transaction log will need to be at least 150MB large. We propose a backup strategy for transaction logs that includes differential backups as well as full backups. This strategy will be executed every 15 minutes to ensure that all changes are captured.

If you back up that log file right away, and you don't have open transactions (or any of a host of other things that will cause SQL Server to keep the log file in use), you'll be able to clear out portions of it, keeping your log file small.

However, if you waited 6 hours to do a transaction log backup, that's approximately 3.5GB of logged activity - your transaction log file (LDF) would be 3.5GB.

Differential Backups
Differential SQL backups should be taken at least once every 8 hours to ensure that changes to the database are captured and stored.

Full backups:
The frequency of performing a full backup of a SQL database will vary depending on the size and complexity of the Venio project case, as well as how frequently the Venio project is utilized. Generally speaking, a full SQL database backup should be performed at least once per week.

To summarize, SQL Server Maintenance Plan Wizard allows you to create a plan to back up databases on a regular schedule and store the backups in a secure location. This plan should include transaction log backups every 15 minutes, differential backups every 8 hours, and full backups of the database at least once per week. Configuring the plan to delete old backups will ensure that backup storage space is not exceeded.

Deleting old backups

The best way to automate backups in SQL Server is to use the built-in SQL Server Maintenance Plan Wizard. This wizard allows you to create a plan to back up databases on a regular schedule and store the backups in a secure location. It is also possible to configure the plan to delete old backups to ensure that the backup storage space is not exceeded.

Transaction Log Backup, we recommend a retention period of 24 hours if you are making 15 minute backups. Any transaction logs older than 24 hours can be safely deleted.

Differential Backup we recommend a retention period of 72 hours if you are making 8 hour backups. Any Differential logs older than 72 hours can be safely deleted.

Full Backup, we recommend a retention period of 2 weeks if you are making weekly backups. Any Full backups older than 2 weeks can be safely deleted. Keep in mind, there are always trade offs. The longer you can store backups, the further back you can recover the database, but the more storage you will need for backups.

Another way to automatically delete old backup files is to use a scheduled task or job in SQL Server. This can be done using the SQL Server Agent, which is a feature that comes with most versions of SQL Server. This will allow you to schedule a task to run at regular intervals, such as daily or weekly, to delete any files that are older than a certain amount of time. You can also set a threshold for the size of the backup files that should be deleted, such as only deleting files that are larger than a certain size. Additionally, you can use the Windows Task Scheduler to manage the task and set it to run at regular intervals.