Back up and restore SQL Server databases

Applies to: SQL Server

This article describes the benefits of backing up SQL Server databases, introduces basic backup and restore terms, and covers backup and restore strategies and security considerations for SQL Server.

The SQL Server backup and restore component provides an essential safeguard for critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, back up your databases regularly to preserve modifications to your data. A well-planned backup and restore strategy helps protect databases against data loss caused by many types of failure. Test your strategy by restoring a set of backups and then recovering your database, so you're ready to respond to a disaster.

In addition to local storage, SQL Server also supports backup to and restore from Azure Blob Storage. For more information, see SQL Server backup and restore with Azure Blob Storage. For database files stored using Azure Blob Storage, SQL Server 2016 (13.x) provides the option to use Azure snapshots for nearly instantaneous backups and faster restores. For more information, see File-snapshot backups for database files in Azure. Azure also offers an enterprise-class backup solution for SQL Server running in Azure VMs. A fully managed backup solution, it supports Always On availability groups, long-term retention, point-in-time recovery, and central management and monitoring. For more information, see About SQL Server backup on Azure VMs.

Why back up?

• Backing up your SQL Server databases, running test restore procedures on your backups, and storing copies of backups in a safe, off-site location protects you from potentially catastrophic data loss. Backing up is the only way to protect your data.

  With valid backups of a database, you can recover your data from many failures, such as:

  • Media failure.

  • User errors, for example, dropping a table by mistake.

  • Hardware failures, for example, a damaged disk drive or permanent loss of a server.

  • Natural disasters. By using SQL Server Backup to Azure Blob Storage, you can create an off-site backup in a different region from your on-premises location, to use if a natural disaster affects your on-premises location.

• Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up Always On availability groups or database mirroring, and archiving.

Glossary of backup terms

Backup and restore strategies

You must customize backup and restore strategies to your environment and available resources. Reliable recovery requires a backup and restore strategy. A well-designed strategy balances the business requirements for maximum data availability and minimum data loss against the cost of maintaining and storing backups.

A backup and restore strategy contains a backup portion and a restore portion. The backup portion defines the type and frequency of backups, the type and speed of the hardware they require, how to test backups, and where and how to store backup media (including security considerations). The restore portion defines who's responsible for performing restores, how to perform restores to meet your goals for database availability and minimum data loss, and how to test restores.

An effective backup and restore strategy requires careful planning, implementation, and testing. Testing is required. You don't have a backup strategy until you successfully restore backups in every combination included in your restore strategy and test each restored database for physical consistency. Consider several factors, including:

• Your organization's goals regarding your production databases, especially the requirements for availability and protecting data from loss or damage.

• The nature of each database: its size, its usage patterns, the nature of its content, the requirements for its data, and so on.

• Constraints on resources, such as hardware, personnel, space for storing backup media, the physical security of the stored media, and so on.

Best practice recommendations

Don't grant the accounts that perform backup or restore operations more privileges than necessary. For more information, see backup and restore for specific permission details. Encrypt database backups and, if possible, compress them.

Use consistent file extensions to make backups easier to identify and manage. SQL Server doesn't require or enforce these extensions, but consistency helps with operational tasks such as configuring antivirus exclusions for backup files. For more information, see Configure antivirus software to work with SQL Server.

• Database backup files should have the .BAK extension.
• Log backup files should have the .TRN extension.

Use separate storage

Place your database backups in a separate physical location or on a separate device from the database files. When the physical drive that stores your databases fails or crashes, recovery depends on your ability to access the separate drive or remote device that stored the backups. You can create several logical volumes or partitions from the same physical disk drive. Carefully review the disk partition and logical volume layouts before you choose a storage location for the backups.

Choose appropriate recovery model

Backup and restore operations occur within the context of a recovery model. A recovery model is a database property that controls how the transaction log is managed. Therefore, a database's recovery model determines what types of backup and restore scenarios the database supports, and the size of its transaction log backups. Typically, a database uses either the simple recovery model or the full recovery model. You can augment the full recovery model by switching to the bulk-logged recovery model before bulk operations. For an introduction to these recovery models and how they affect transaction log management, see the transaction log.

The best choice of database recovery model depends on your business requirements. To avoid transaction log management and simplify backup and restore, use the simple recovery model. To minimize work-loss exposure at the cost of administrative overhead, use the full recovery model. To minimize the effect on log size during bulk-logged operations while still allowing recovery of those operations, use the bulk-logged recovery model. For information about the effect of recovery models on backup and restore, see Backup overview (SQL Server).

Design your backup strategy

After you select a recovery model that meets your business requirements for a specific database, plan and implement a matching backup strategy. The best backup strategy depends on several factors. The following factors are especially important:

• How many hours a day do applications need to access the database?

  If there's a predictable off-peak period, you should schedule full database backups for that period.

• How frequently are changes and updates likely to occur?

  If changes are frequent, consider:

  • Under the simple recovery model, you can schedule differential backups between full database backups. A differential backup captures only the changes since the last full database backup.

  • Under the full recovery model, you can schedule frequent log backups. Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data.

• Are changes likely to occur in only a small part of the database, or in a large part?

  For a large database in which changes are concentrated in a subset of the files or filegroups, partial backups or full file backups can be useful. For more information, see Partial Backups (SQL Server) and Full File Backups (SQL Server).

• How much disk space does a full database backup require?

• How far in the past does your business require to maintain backups?

  Ensure you have a proper backup schedule that matches the application's needs and business requirements. As backups age, the risk of data loss grows unless you have a way to regenerate all the data up to the point of failure. Before you dispose of old backups because of storage limits, consider whether you need recovery that far in the past.

Estimate the size of a full database backup

Before you implement a backup and restore strategy, estimate how much disk space a full database backup uses. The backup operation copies the data in the database to the backup file. The backup contains only the actual data in the database, not any unused space. Therefore, the backup is usually smaller than the database itself. To estimate the size of a full database backup, use the sp_spaceused system stored procedure. For more information, see sp_spaceused.

Schedule backups

A backup operation has minimal effect on running transactions, so you can run backups during regular operations. You can perform a SQL Server backup with minimal effect on production workloads.

After you decide what types of backups you need and how often to perform each type, schedule regular backups as part of a database maintenance plan. For information about maintenance plans and how to create them for database backups and log backups, see Use the Maintenance Plan Wizard.

Test your backups

You don't have a restore strategy until you test your backups. Thoroughly test your backup strategy for each database by restoring a copy of the database onto a test system. You must test restoring every type of backup that you intend to use. After you restore the backup, run DBCC CHECKDB against the database to confirm the backup media isn't damaged.

Verify media stability and consistency

Use the verification options provided by the backup utilities (BACKUP T-SQL command, SQL Server Maintenance Plans, your backup software or solution, and so on). For an example, see RESTORE Statements - VERIFYONLY.

Use advanced features such as BACKUP CHECKSUM to detect problems with the backup media itself. For more information, see Possible Media Errors During Backup and Restore (SQL Server).

Document backup/restore strategy

Document your backup and restore procedures, and keep a copy of the documentation in your run book.

You should also maintain an operations manual for each database. This operations manual should document the location of the backups, backup device names (if any), and the time required to restore the test backups.

Security risk of restoring backups from untrusted sources

This section outlines the security risk associated with restoring backups from untrusted sources to any SQL Server environment, including on-premises, Azure SQL Managed Instance, SQL Server on Azure Virtual Machines (VMs) and any other environment.

Why this matters

Restoring SQL backup files (.bak) introduces a potential risk if the backup originates from an untrusted source. The security risk is exacerbated further when a SQL Server environment has multiple instances, as it amplifies the area of threat. While backups that remain within a trusted boundary pose no security issue, restoring a malicious backup can compromise the security of the entire environment.

A malicious .bak file can:

• Take over the entire SQL Server instance.
• Escalate privileges and gain unauthorized access to the underlying host or virtual machine.

This attack occurs before any validating scripts or security checks can execute, which makes it extremely dangerous. Restoring an untrusted backup is equivalent to running untrusted applications on a critical server or virtual machine, and introducing arbitrary code execution into your environment.

Best practices

Follow these backup security best practices to reduce the threat to your SQL Server environments:

• Treat restoring backups as a high-risk operation.
• Reduce the threat service area by using isolated instances.
• Only allow trusted backups: never restore backups from unknown or external sources.
• Only allow backups that have remained within a trusted boundary: ensure backups originate from within the trusted boundary.
• Do not bypass security controls for convenience.
• Enable server-level auditing to capture backup and restore events and mitigate audit evasion.

Monitor progress with XEvent

Backup and restore operations can take a long time because of the size of a database and the complexity of the operations involved. When issues arise with either operation, use the backup_restore_progress_trace extended event to monitor progress live. For more information about extended events, see Extended Events overview.

-- Create the backup_restore_progress_trace extended event session
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file (SET filename = N'BackupRestoreTrace')
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = START;
GO

-- Stop the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = STOP;
GO

Sample output from Extended Event

More about backup tasks

• Create a maintenance plan
• Create a SQL Server Agent job in SQL Server Management Studio
• Configure schedule for SQL Server Agent job

Work with backup devices and backup media

• Define a logical backup device for a disk file (SQL Server)
• Define a logical backup device for a tape drive (SQL Server)
• Specify a disk or tape backup destination (SQL Server)
• Delete a backup device (SQL Server)
• Set the expiration date on a backup (SQL Server)
• View the contents of a backup tape or file (SQL Server)
• View the data and log files in a backup set (SQL Server)
• View the properties and contents of a logical backup device (SQL Server)
• Restore a backup from a device (SQL Server)

Create backups

For partial or copy-only backups, use the Transact-SQL BACKUP statement with the PARTIAL or COPY_ONLY option, respectively.

Use SSMS

• Create a full database backup
• Back up a transaction log
• Back up files and filegroups
• Create a differential database backup (SQL Server)

Use T-SQL

• Use Resource Governor to limit CPU usage by backup compression
• Back up the transaction log when the database is damaged (SQL Server)
• Enable or disable backup checksums during backup or restore (SQL Server)
• Specify backup or restore to continue or stop after error

Restore data backups

Use SSMS

• Restore a database backup using SSMS
• Restore a database to a new location (SQL Server)
• Restore a differential database backup (SQL Server)
• Restore files and filegroups (SQL Server)

Use T-SQL

• Restore a database backup under the simple recovery model
• Restore database to point of failure - full recovery
• Restore files and filegroups over existing files (SQL Server)
• Restore files to a new location (SQL Server)
• Restore the master database

Restore transaction logs (full recovery model)

Use SSMS

• Restore a database to a marked transaction (SQL Server Management Studio)
• Restore a transaction log backup (SQL Server)
• Restore a SQL Server database to a point in time (full recovery model)

Use T-SQL

• Restore a SQL Server database to a point in time (full recovery model)
• Restart an interrupted restore operation
• Recover a database without restoring data

Related content

• Backup overview (SQL Server)
• Restore and recovery overview (SQL Server)
• BACKUP (Transact-SQL)
• RESTORE Statements (Transact-SQL)
• Backup and Restore of Analysis Services Databases
• Back up and restore full-text catalogs and indexes
• Back up and restore replicated databases
• The transaction log
• Recovery models (SQL Server)
• Media sets, media families, and backup sets (SQL Server)