What are the most common backup strategies?

Choosing a database backup strategy is crucial because it determines how data in your SQL Server database is protected. You can read more about database backup strategies, but here we are going to present you some statistics on how SQLBackupAndFTP users backup their databases. The numbers are based on a sample of about a thousand users.

17% of users do not do automatic scheduled backups

Some of SQLBackupAndFTP users create backups manually, without any schedule. This backup strategy is, for example, good in the development environment when you need to quickly take a snapshot of the current database state. But in production,  you need to make sure that your database is secured by regular backups.

68% of users prefer to make Full Database Backups only

The most popular backup strategy, according to the statistics, is to periodically make only a full database backup. It’s a very good plan if your database size is relatively small. In this case each backup file is self-sufficient and can be restored independently. But if your database is gigabytes in size, it will be time and space consuming to create full backups only and you will have to add some differential backups as well.

10% of users use FULL+DIFFERENTIAL Backup Strategy

This backup strategy is perfect for large databases and when you need to take database snapshots more frequently. As differential backups contain only data that have been changed since the last full backup, they are created faster and take less space on your disk. In order to restore the database, you need to restore the last created full backup and then the last differential backup following it.

In order to restore the database, you need to restore the last created full backup and then the last differential backup following it.

3% of users implement full backup strategy: Full + Differential + Transaction Log Backups

Only 3% of users create all three types of database backups. From our experience, this is the most reliable and convenient backup strategy. Each transaction log backup stores all transactions occurred since the previous transaction log backup. This allows you to restore your database to any point in time. If your database contains important data that is too costly to lose (e.g. financial information) we highly recommend you to implement such backup plan. In this case, to restore the database you need to start with the full backup, then restore last differential backup and after that all transaction log backups that goes after the differential backup.

2% of users create Full backup + Transaction Log backup

Such backup strategy isn’t bad, but in case of failure you might need to restore too many Transaction backups and this may become very time-consuming. But it still depends on how often you create your full and log backups.

Conclusion

This statistics shows that the majority of SQLBackupAndFTP users find it sufficient to periodically create full database backups. It’s the most simple way and we believe that many users simply do not have a clear understanding of differential and transaction log backups. If your database has grown large and creating a full backup has become a time-consuming procedure then think about including differential backup to your backup strategy. You can read about them in our backup academy.

[Total: 6    Average: 3.8/5]