Conventional SQL Server backups read the data from the database files and write (stream) the data to a target backup location. Similarly, during restore, the data is read from the target backup location and written to the database files. Such operations can be read and write intensive and create a utilization and performance overhead for the server that performs this operation.
Conventional SQL Server backups are useful in the following cases:
- When database capacity is relatively small, and therefore the backup and recovery times are short or can take place when the system is not highly used.
- When a strategy of full, differential, and/or incremental backups are desired.
- When many databases have their datafiles that are shared across the same storage volumes. In such a case a storage snapshot includes datafiles of all these databases. If a more granular solution is desired, traditional backups can be used to perform a backup of just the desired databases.
- When seeding availability group replica from the snapshots.
The following challenges exist for conventional backups:
- Database performance impact: Conventional backups typically lead to a significant performance impact on the production database because of host resource usage. There is a heavy load on the database server, the storage system, and the storage network during the traditional copy-based backups.
- Shrinking backup time: Conventional backups can be scheduled only during less business activity to not hamper the business, but it has only become difficult with the database uptime requirement round the clock.
- Rapid growth of data: Data growth is directly proportional to backup set growth. Larger datasets take larger time to backup. Incremental or differential backup can address this issue, but this option results in a slow and complex restoration process eventually impacting the recovery time objective (RTO) or recovery point objective (RPO).
- More database upgrade time: Project plan for any database has the conventional backup time included. More backup time dramatically cuts down the total available time for the upgrade. The option to restore quickly allows more time for post upgrade issues. It also allows enough time to test the database after upgrade.
Rapid data growth along with the shrinking backup window creates the need of using different strategies for backup infrastructure. Storage snapshots is one such strategy to improve backup infrastructure because of its faster backup and restore time.