Back Up and Restore of SQL Server Databases
This topic 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.
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.
Starting with SQL Server 2012 SP1 Cumulative Update 2, SQL Server backup to the Windows Azure Blob storage service is supported. For more information, see Backup and Restore Enhancements. and SQL Server Backup and Restore with Windows Azure Blob Storage Service .
For solutions to common customer problems with Windows Azure SQL Database, SQL Server Data Tools, and SQL Server Management Studio, see Database Lifecycle Management .
Copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.
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.
A disk or tape device to which SQL Server backups are written and from which they can be restored.
One or more tapes or disk files to which one or more backup have been written.
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).
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.
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.
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.
A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model)
To return a database to a stable and consistent state.
A phase of database startup or of a restore with recovery that brings the database into a transaction-consistent state.
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.
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.
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 maximizes data availability and minimizes data loss, while considering your particular business requirements.
Place the database and backups on separate devices. Otherwise, if the device containing the database fails, your backups will be unavailable. Placing the data and backups on separate devices also enhances the I/O performance for both writing backups and the production use of the database.
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 and how restores should be performed to meet your goals for availability of the database and for minimizing data loss. We recommend that you document your backup and restore procedures and keep a copy of the documentation in your run book.
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. You must consider a variety of factors. These include the following:
The production goals of your organization for the databases, especially the requirements for availability and protection of data from loss.
The nature of each of your databases: 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.
The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, backup and restore work across 32-bit and 64-bit environments. A backup created on a server instance running in one environment can be restored on a server instance that runs in the other environment.
Impact of the Recovery Model on Backup and Restore
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. Also, the recovery model of a database determines what types of backups and what restore scenarios are supported for the database. Typically a database uses either the simple recovery model or the full recovery model. The full recovery model can be supplemented 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. For information about the effect of recovery models on backup and restore, see Backup Overview (SQL Server) .
Design the 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 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?
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) .
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.