SQL Safe supports four standard database backup types:
- Full Backup.
- Differential Backup.
- Transaction Log Backup.
- File Backup.
You can use a backup type exclusively or combine types to fit your backup strategy.
What is a full backup?
A full backup creates a full copy of the data in a database. Full backups usually run at regularly scheduled intervals and require more storage space and time to complete. Full backups copy data and transaction log pages to the backup set. The backup is smaller than the database itself because unused space is not retained.
Full backups allow you to restore your database to its original state prior to backup. During the restoration of a full backup, the SQL Server instance being restored rolls back uncommitted transactions. Use transaction log backups to recover uncommitted transactions.
What is a differential backup?
Differential backups record only the data that changed since the last full backup. Consider using differential backups on active SQL Server instances where minimal database downtime is critical. Smaller and faster differential backups allow you to make more frequent backups with less impact on your server. Performing frequent backups helps maintain optimal database availability and minimizes data loss risks. Differential backups allow you to restore your database to the last completed differential backup.
What is a transaction log backup?
A transactional log backup creates a copy of the transaction log file. It sequentially records all database transactions that occurred since the last transaction log backup. In conjunction with a full or differential database restore, restoring a transaction log backup allows you to recover the database to the point of failure or a specific time.
Typically transaction log backups do not require intensive resource usage and can be scheduled more frequently than other backup types. Ensure you increase the frequency of your transaction log backups if your database has a high transaction rate. Also, consider storing critical transaction log backups on fault-tolerant storage devices.
While you cannot execute a transaction log backup during a full or differential backup, you can during a file backup. Ensure you create database or file backups before backing up the transaction log. The transaction log contains only the database changes made after the creation of the last backup.
What is a file or filegroup backup?
Backs up either individual files or all files in a filegroup within a database. Backing up single files or filegroups allows you to restore only corrupted files. Restoring only corrupted files increases recovery speed. Consider file and filegroup backups when your database has one or all of the following attributes:
- Database size hinders regular full or differential backups
- Database can be unavailable for short periods of time only
- Specific files are either regularly corrupted, are more critical, or change more frequently than others
You can backup files or filegroups and transaction logs at the same time.