A full backup in SQL Server is a complete copy of all the data in a database or a subset of a database.
The purpose of full backups is to provide a comprehensive snapshot of the database that can be used to restore and recover the database in the event of data loss or corruption. Full backups capture the state of the database at the exact point in time when the backup completed.
Full backups are generally taken less frequently than incremental or differential backups, due to their larger size and resource demands. Doing a full backup typically requires more time, storage space, and I/O resources compared to other backup types.
Full backups are recommended when first configuring a backup solution for a database, as a baseline. They are also preferred in situations when smaller backup files are not a priority, such as before major application upgrades or database migrations.
How Full Backups Work
A full backup copies the entire database and not just individual parts. This includes the data files where all the data is stored, as well as the transaction log that tracks all transactions and database modifications (learn.microsoft.com/en-us/sql/relational-databases/backup-restore/full-database-backups-sql-server?view=sql-server-ver16).
When a full backup occurs, SQL Server will back up all data files and the active portion of the transaction log, capturing everything to allow the database to be restored to the state it was in at the time the backup completed. This makes full backups larger in size compared to other backup types since it contains all the data and transaction logs.
The tradeoff for the increased storage requirements of full backups is that it allows you to restore the entire database to a previous point in time. Full backups provide a complete picture of the database so are required for certain restore scenarios.
Recovery Models
SQL Server provides three recovery models that control transaction log maintenance and backup restore operations: full, bulk-logged, and simple. The full recovery model allows all types of backups – full, differential, transaction log – and supports point-in-time restores.
With the full recovery model, all changes made to the database are fully logged in the transaction log. This allows the database to be restored to a specific point in time by applying transaction log backups after the full or differential backup. The full recovery model provides the highest level of data recoverability.
The bulk-logged recovery model is similar to full, but minimizes logging for bulk operations like bulk inserts. This improves performance for bulk loads at the cost of point-in-time recovery granularity. Bulk-logged recovery allows full, differential and transaction log backups.
The simple recovery model only allows full database backups. Transaction log backups are minimized, so point-in-time restore is not supported. The simple model has the least logging overhead, but provides the lowest level of data recoverability.
Only the full and bulk-logged recovery models support full backups. The key difference is full recovery provides complete point-in-time recovery while bulk-logged recovery does not.
Creating a Full Backup
There are several methods for creating full database backups in SQL Server:
Using SSMS
The easiest way to create a full backup is using SQL Server Management Studio (SSMS). In the Object Explorer pane, right-click on the database you want to back up and select “Tasks > Back Up”. This will open the Back Up Database window where you can specify the type of backup as “Full” and configure options like backup destination, compression, etc. Before running the backup, verify the settings are correct [1].
Using T-SQL
Full database backups can also be created by executing the BACKUP DATABASE T-SQL command and specifying the DATABASE option. For example:
BACKUP DATABASE AdventureWorks2017
TO DISK = 'C:\Backups\AdventureWorks2017.bak'
WITH FORMAT
Additional options like compression and encryption can be specified as needed [2].
Using Maintenance Plans
SQL Server Maintenance Plans provide a GUI to schedule and automate database backups. A full backup task can be added to the maintenance plan and configured to run on a schedule. This allows full backups to be created automatically on a regular basis with minimal admin effort.
Backup Compression
SQL Server’s backup compression feature compresses backups as they are written to disk or tape. This reduces the size of the backup file, saving storage space. According to Microsoft, enabling backup compression typically reduces the backup size by 60-70%.
Backup compression works by using CPU resources to compress the data as the backup is generated. The more CPUs available, the faster the compression can occur. Enabling compression does result in additional CPU usage during backups. However, restoring compressed backups requires less CPU than restoring uncompressed backups of the same size.
Backup compression is compatible with full, differential, file and filegroup, and transaction log backups. It can be enabled at the database or instance level. Compression is not available in every edition of SQL Server; it requires Enterprise, Developer, or Evaluation edition.
Restoring from Full Backups
Restoring an entire database from a full backup is straightforward in SQL Server using SQL Server Management Studio (SSMS). The basic steps are:
- In SSMS, right-click on the Databases node and select Restore Database.
- In the Restore Database window, under Source, select Device and click the browse button. Navigate to the location of your full database backup file.
- Select the backup file and click OK. This will populate the backup sets to restore section.
- Under Destination, choose the database name. You can overwrite the existing database or restore to a new location.
- Click OK to start the restore process.
SQL Server also provides a point-in-time restore option when restoring a full backup. This allows restoring the database to a specific point in time rather than just the time the backup completed. To utilize this:
- Follow the steps above to select the full backup file.
- On the Options page of the Restore Database window, select the Restore point in time option.
- Specify the exact time to restore to in the provided field.
- Click OK to restore to that point-in-time rather than just the end of the backup.
Finally, SQL Server offers flexibility in restoring full backups to new locations. Using the Destination options, you can choose a different database name, file locations, etc. This allows restoring copies of production databases to development or test environments.
Backup Verification
Verifying backups is an important step to ensure your backup files are valid and can be restored when needed. SQL Server provides several methods to verify backup integrity and readability.
One method is using the RESTORE VERIFYONLY statement, which checks the backup file headers and verifies that the backup is complete and valid, without actually restoring the data. This can detect issues like missing or damaged backup files [1].
Another option is enabling the CHECKSUM backup option, which adds checksums to each page during a backup. The checksums are validated during restores to check for corruption. CHECKSUM is more thorough than VERIFYONLY but also more resource intensive [2].
DBCC CHECKDB can also validate database consistency and detect physical corruption. Running CHECKDB regularly is recommended to monitor database health [3].
Finally, performing test restores to an alternate location provides the most comprehensive validation of a backup. However, test restores are more disruptive than other options.
Using a combination of these methods allows DBAs to thoroughly validate backups and ensure availability.
Backup Encryption
Backup encryption allows encrypting the data when creating backups in SQL Server. Encrypting backups helps protect confidential data at rest and meet compliance requirements. There are two types of encryption algorithms supported for backups in SQL Server – AES 128, AES 256 and Triple DES.
SQL Server provides Transparent Data Encryption (TDE) to encrypt data at rest. When TDE is enabled on a database, the backups are also encrypted automatically using the database encryption key. No additional steps are required to encrypt the backup. See Microsoft’s Transparent Data Encryption documentation for more details.
SQL Server also allows creating encrypted backups using certificates or asymmetric keys. An encryption certificate or asymmetric key needs to be created on the server. This encryption key is then specified when taking backups, encrypting the backup file. Only users with access to the certificate or asymmetric key can restore these encrypted backups. See Microsoft’s Create an Encrypted Backup documentation for a step-by-step guide.
It is recommended to periodically regenerate encryption certificates or asymmetric keys used for backup encryption as per your organization’s compliance policies. The encrypted backups can be restored as long as the correct encryption certificate or key is available.
Third-Party Backup Tools
In addition to the native backup functionality in SQL Server, there are many third-party tools available that offer additional features and benefits for backing up SQL Server databases. Some of the most popular SQL Server backup tools include:
Quest LiteSpeed for SQL Server – LiteSpeed by Quest Software is one of the most widely used third-party backup tools for SQL Server. It offers advanced compression to reduce backup sizes, backup encryption, log shipping, and the ability to restore to a point-in-time. For more information see: https://www.quest.com/products/litespeed-for-sql-server/
Redgate SQL Backup – SQL Backup from Redgate provides a simple interface for managing SQL Server backups. Key features include backup compression, encryption, log shipping, integration with monitoring tools, and the ability to restore to a point-in-time. For more information see: https://www.red-gate.com/products/dba/sql-backup/
IDERA SQL Safe Backup – SQL Safe from IDERA offers backup compression, encryption, log shipping and point-in-time restore capabilities. It also provides monitoring, alerting and reporting on the status of SQL Server backups. For more information see: https://www.idera.com/productssolutions/sqlserver/sqlsafebackup
These tools provide additional flexibility, performance, security and management capabilities beyond the native SQL Server backup functionality. Using a third-party backup solution can help efficiently protect critical SQL Server databases.
Best Practices
When implementing a backup strategy for SQL Server, following best practices is crucial for ensuring recoverability. Some key best practices include:
Backup Schedule
Establish a regular backup schedule that aligns with your recovery point objective (RPO) and recovery time objective (RTO). Full backups are generally performed weekly, while differential and transaction log backups are done more frequently (often daily). Stick to the schedule consistently.[1]
Testing Restores
Periodically restore backups to test recoverability. Test on a separate environment, not production. Verify database and log files restore fully and database comes back online. This ensures your backup strategy is working.[2]
Storing Backups Securely
Store backup files on a separate volume from production data. Retain backups according to compliance requirements. Encrypt backups and protect with limited access controls. Consider offsite rotation to guard against data center failures.[3]