Back Up and Restore of SQL Server Databases

Applies to: SQL Server

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

This article introduces SQL Server backups. For specific steps to back up SQL Server databases, see Creating backups.

The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

In addition to local storage for storing the backups, SQL Server also supports backup to and restore from Azure Blob Storage. For more information, see SQL Server Backup and Restore with Microsoft 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 Azure Backup for SQL Server in Azure VM.

Why back up?

• Backing up your SQL Server databases, running test restores 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 than your on-premises location, to use in the event of a natural disaster affecting 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

back up [verb]
The process of creating a backup [noun] by copying data records from a SQL Server database, or log records from its transaction log.

backup [noun]
A copy of data that can be used to restore and recover the data after a failure. Backups of a database can also be used to restore a copy the database to a new location.

backup device
A disk or tape device to which SQL Server backups are written and from which they can be restored. SQL Server backups can also be written to an Azure Blob Storage, and URL format is used to specify the destination and the name of the backup file. For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage.

backup media
One or more tapes or disk files to which one or more backups have been written.

data backup
A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).

database backup
A backup of a database. Full database backups represent the whole database at the time the backup finished. Differential database backups contain only changes made to the database since its most recent full database backup.

differential backup
A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data that has changed since that base.

full backup
A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

log backup
A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model)

recover
To return a database to a stable and consistent state.

recovery
A phase of database startup or of a restore with recovery that brings the database into a transaction-consistent state.

recovery model
A database property that controls transaction log maintenance on a database. Three recovery models exist: simple, full, and bulk-logged. The recovery model of database determines its backup and restore requirements.

restore
A multi-phase process that copies all the data and log pages from a specified SQL Server backup to a specified database, and then rolls forward all the transactions that are logged in the backup by applying logged changes to bring the data forward in time.

Backup and restore strategies

Backing up and restoring data must be customized to a particular environment and must work with the available resources. Therefore, a reliable use of backup and restore for recovery requires a backup and restore strategy.A well-designed backup and restore strategy balances the business requirements for maximum data availability and minimum data loss, while considering the cost of maintaining and storing backups.

A backup and restore strategy contains a backup portion and a restore portion. The backup part of the strategy defines the type and frequency of backups, the nature, and speed of the hardware that is required for them, how backups are to be tested, and where and how backup media is to be stored (including security considerations). The restore part of the strategy defines who is responsible for performing restores, how restores should be performed to meet your goals for database availability and minimizing data loss, and how restores are tested.

Designing an effective backup and restore strategy requires careful planning, implementation, and testing. Testing is required: you do not have a backup strategy until you have successfully restored backups in all the combinations that are included in your restore strategy and have tested the restored database for physical consistency. You must consider a variety of factors. These include:

• The goals of your organization regarding your production databases, especially the requirements for availability and protection of 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

The accounts that perform backup or restore operations should not be granted more privileges than necessary. Review backup and restore for specific permission details. It's recommended that backups are encrypted and, if possible, compressed.

To ensure security, backup files should have extensions that follow proper conventions:

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

Use Separate Storage

Important

Ensure that you place your database backups on a separate physical location or device from the database files. When your physical drive that stores your databases malfunctions or crashes, recoverability depends on the ability to access the separate drive or remote device that stored the backups in order to perform a restore. Keep in mind that you could create several logical volumes or partitions from a same physical disk drive. Carefully study the disk partition and logical volume layouts before choosing 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. Thus, the recovery model of a database determines what types of backups and restore scenarios are supported for the database, and what the size of the transaction log backups would be. Typically, a database uses either the simple recovery model or the full recovery model. The full recovery model can be augmented 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 (SQL Server)

The best choice of recovery model for the database 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 impact on log size during bulk-logged operations while at the same time allowing for recoverability of those operations, use 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 have selected a recovery model that meets your business requirements for a specific database, you have to plan and implement a corresponding backup strategy. The optimal backup strategy depends on a variety of factors, of which the following are especially significant:

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

  If there is a predictable off-peak period, we recommend that you schedule full database backups for that period.

• How frequently are changes and updates likely to occur?

  If changes are frequent, consider the following:

  • Under the simple recovery model, consider scheduling 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 should 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 of the database?

  For a large database in which changes are concentrated in a part of the files or filegroups, partial backups and 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 will a full database backup require?

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

  Make sure you have a proper backup schedule established according to the needs of the application and business requirements. As the backups get old, the risk of data loss is higher unless you have a way to regenerate all the data till the point of failure. Before you choose to dispose of old backups due to storage resource limitations, consider if recoverability is required that far in the past

Estimate the size of a full database backup

Before you implement a backup and restore strategy, you should estimate how much disk space a full database backup will use. The backup operation copies the data in the database to the backup file. The backup contains only the actual data in the database and not any unused space. Therefore, the backup is usually smaller than the database itself. You can estimate the size of a full database backup by using the sp_spaceused system stored procedure. For more information, see sp_spaceused (Transact-SQL).

Schedule backups

Performing a backup operation has minimal effect on transactions that are running; therefore, backup operations can be run during regular operations. You can perform a SQL Server backup with minimal effect on production workloads.

For information about concurrency restrictions during backup, see Backup Overview (SQL Server).

After you decide what types of backups you require and how frequently you have to perform each type, we recommend that you schedule regular backups as part of a database maintenance plan for the database. 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 do not have a restore strategy until you have tested your backups. It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. You must test restoring every type of backup that you intend to use. It is also recommended that once you restore the backup, you perform database consistency checks via DBCC CHECKDB of the database to validate the backup media was not 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, etc.). For an example, see [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) Use advanced features like 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

We recommend that you document your backup and restore procedures and keep a copy of the documentation in your run book. We also recommend that you maintain an operations manual for each database. This operations manual should document the location of the backups, backup device names (if any), and the amount of time that is required to restore the test backups.

Monitor progress with XEvent

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

Warning

Using the backup_restore_progress_trace extended event can cause a performance issue and consume a significant amount of disk space. Use for short periods of time, exercise caution, and test thoroughly before implementing in production.

-- 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 Job

• Schedule a Job

Working 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 As a 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)

Creating backups

Note

For partial or copy-only backups, you must use the Transact-SQLBACKUP statement with the PARTIAL or COPY_ONLY option, respectively.

Using SSMS

• Create a Full Database Backup (SQL Server)

• Back Up a Transaction Log (SQL Server)

• Back Up Files and Filegroups (SQL Server)

• Create a Differential Database Backup (SQL Server)

Using T-SQL

• Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)

• Back Up the Transaction Log When the Database Is Damaged (SQL Server)

• Enable or Disable Backup Checksums During Backup or Restore (SQL Server)

• Specify Whether a Backup or Restore Operation Continues or Stops After Encountering an Error (SQL Server)

Restore data backups

Using 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)

Using T-SQL

• Restore a Database Backup Under the Simple Recovery Model (Transact-SQL)

• Restore a Database to the Point of Failure Under the Full Recovery Model (Transact-SQL)

• Restore Files and Filegroups over Existing Files (SQL Server)

• Restore Files to a New Location (SQL Server)

• Restore the master Database (Transact-SQL)

Restore transaction logs (Full Recovery Model)

Using 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)

Using T-SQL

• Restore a SQL Server Database to a Point in Time (Full Recovery Model)

• Restart an Interrupted Restore Operation (Transact-SQL)

• Recover a Database Without Restoring Data (Transact-SQL)

More information and resources

Backup Overview (SQL Server)
Restore and Recovery Overview (SQL Server)
BACKUP (Transact-SQL)
RESTORE (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 (SQL Server)
Recovery Models (SQL Server)
Media Sets, Media Families, and Backup Sets (SQL Server)