Figure 15 Back Up Database. In the Back up to option, select URL. In file name, you can write any name or keep the default values. In SQL credential, select the credential created in figure 14. In azure storage container, use the container created in figure 9. Finally, for the URL prefix, used the URL container used in the figure 10.
-->Backs up a SQL database.
Click one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular SQL version with which you are working.
For more information about the syntax conventions, see Transact-SQL Syntax Conventions.
Click a product!
In the following row, click whichever product name you are interested in. The click displays different content here, appropriate for whichever product you click:
* SQL Server * | SQL Database managed instance | Analytics Platform System (PDW) |
SQL Server
Backs up a complete SQL Server database to create a database backup, or one or more files or filegroups of the database to create a file backup (BACKUP DATABASE). Also, under the full recovery model or bulk-logged recovery model, backs up the transaction log of the database to create a log backup (BACKUP LOG).
Syntax
Arguments
DATABASESpecifies a complete database backup. If a list of files and filegroups is specified, only those files and filegroups are backed up. During a full or differential database backup, SQL Server backs up enough of the transaction log to produce a consistent database when the backup is restored.
When you restore a backup created by BACKUP DATABASE (a data backup), the entire backup is restored. Only a log backup can be restored to a specific time or transaction within the backup.
Note
Only a full database backup can be performed on the master database.
LOG
Specifies a backup of the transaction log only. The log is backed up from the last successfully executed log backup to the current end of the log. Before you can create the first log backup, you must create a full backup.
You can restore a log backup to a specific time or transaction within the backup by specifying
WITH STOPAT
, STOPATMARK
, or STOPBEFOREMARK
in your RESTORE LOG statement.Note
After a typical log backup, some transaction log records become inactive, unless you specify
WITH NO_TRUNCATE
or COPY_ONLY
. The log is truncated after all the records within one or more virtual log files become inactive. If the log is not being truncated after routine log backups, something might be delaying log truncation. For more information, see Factors that can delay log truncation.{ database_name | @database_name_var }Is the database from which the transaction log, partial database, or complete database is backed up. If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var=database name) or as a variable of character string data type, except for the ntext or text data types.
Note
The mirror database in a database mirroring partnership cannot be backed up.
<file_or_filegroup> [ ,..n ]Used only with BACKUP DATABASE, specifies a database file or filegroup to include in a file backup, or specifies a read-only file or filegroup to include in a partial backup.
FILE = { logical_file_name | @logical_file_name_var }Is the logical name of a file or a variable whose value equates to the logical name of a file that is to be included in the backup.
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }Is the logical name of a filegroup or a variable whose value equates to the logical name of a filegroup that is to be included in the backup. Under the simple recovery model, a filegroup backup is allowed only for a read-only filegroup.
Note
Consider using file backups when the database size and performance requirements make a database backup impractical. The NUL device can be used to test the performance of backups, but should not be used in production environments.
nIs a placeholder that indicates that multiple files and filegroups can be specified in a comma-separated list. The number is unlimited.
For more information, see Full File Backups and Back Up Files and Filegroups.
READ_WRITE_FILEGROUPS [ , FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } [ ,..n ] ]Specifies a partial backup. A partial backup includes all the read/write files in a database: the primary filegroup and any read/write secondary filegroups, and also any specified read-only files or filegroups.
READ_WRITE_FILEGROUPSSpecifies that all read/write filegroups be backed up in the partial backup. If the database is read-only, READ_WRITE_FILEGROUPS includes only the primary filegroup.
Important
Explicitly listing the read/write filegroups by using FILEGROUP instead of READ_WRITE_FILEGROUPS creates a file backup.
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }Is the logical name of a read-only filegroup or a variable whose value equates to the logical name of a read-only filegroup that is to be included in the partial backup. For more information, see '<file_or_filegroup>,' earlier in this topic.
nIs a placeholder that indicates that multiple read-only filegroups can be specified in a comma-separated list.
For more information about partial backups, see Partial Backups.
TO <backup_device> [ ,..n ]Indicates that the accompanying set of backup devices is either an unmirrored media set or the first of the mirrors within a mirrored media set (for which one or more MIRROR TO clauses are declared).
<backup_device>
Specifies a logical or physical backup device to use for the backup operation.
{ logical_device_name | @logical_device_name_var }Applies to: SQL ServerIs the logical name of the backup device to which the database is backed up. The logical name must follow the rules for identifiers. If supplied as a variable (@logical_device_name_var), the backup device name can be specified either as a string constant (@logical_device_name_var= logical backup device name) or as a variable of any character string data type except for the ntext or text data types.
{ DISK | TAPE | URL} = { 'physical_device_name' | @physical_device_name_var | 'NUL' }Applies to: DISK, TAPE, and URL apply to SQL Server.Specifies a disk file or tape device, or a Microsoft Azure Blob storage service. The URL format is used for creating backups to the Microsoft Azure storage service. For more information and examples, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. For a tutorial, see Tutorial: SQL Server Backup and Restore to Microsoft Azure Blob Storage Service.
Note
The NUL disk device will discard all information sent to it and should only be used for testing. This is not for production use.
Important
Starting with SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2014 (12.x), you can only backup to a single device when backing up to URL. In order to backup to multiple devices when backing up to URL, you must use SQL Server 2016 (13.x) through SQL Server 2017 and you must use Shared Access Signature (SAS) tokens. For examples creating a Shared Access Signature, see SQL Server Backup to URL and Simplifying creation of SQL Credentials with Shared Access Signature (SAS) tokens on Azure Storage with Powershell.
URL applies to: SQL Server ( SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2017).
A disk device does not have to exist before it is specified in a BACKUP statement. If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.
Note
The NUL device will discard all input sent to this file, however the backup will still mark all pages as backed up.
For more information, see Backup Devices.
Note
The TAPE option will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
nIs a placeholder that indicates that up to 64 backup devices may be specified in a comma-separated list.
MIRROR TO <backup_device> [ ,..n ]Specifies a set of up to three secondary backup devices, each of which mirrors the backups devices specified in the TO clause. The MIRROR TO clause must specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three.
This option is available only in the Enterprise edition of SQL Server.
Note
For MIRROR TO = DISK, BACKUP automatically determines the appropriate block size for disk devices based on the sector size of the disk. If the MIRROR TO disk is formatted with a different sector size than the disk specified as the primary backup device, the backup command will fail. In order to mirror backups to devices that have different sector sizes, the BLOCKSIZE parameter must be specified, and should be set to the highest sector size amongst all the target devices. For more information about block size, see 'BLOCKSIZE' later in this topic.
<backup_device>See '<backup_device>,' earlier in this section.
nIs a placeholder that indicates that up to 64 backup devices may be specified in a comma-separated list. The number of devices in the MIRROR TO clause must equal the number of devices in the TO clause.
For more information, see 'Media Families in Mirrored Media Sets' in the Remarks section, later in this topic.
[ next-mirror-to ]Is a placeholder that indicates that a single BACKUP statement can contain up to three MIRROR TO clauses, in addition to the single TO clause.
WITH Options
Specifies options to be used with a backup operation.
CREDENTIALApplies to: SQL Server ( SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2017).Used only when creating a backup to the Microsoft Azure Blob storage service.
FILE_SNAPSHOTApplies to: SQL Server ( SQL Server 2016 (13.x) through SQL Server 2017).
Used to create an Azure snapshot of the database files when all of the SQL Server database files are stored using the Azure Blob storage service. For more information, see SQL Server Data Files in Microsoft Azure. SQL Server Snapshot Backup takes Azure snapshots of the database files (data and log files) at a consistent state. A consistent set of Azure snapshots make up a backup and are recorded in the backup file. The only difference between
BACKUP DATABASE TO URL WITH FILE_SNAPSHOT
and BACKUP LOG TO URL WITH FILE_SNAPSHOT
is that the latter also truncates the transaction log while the former does not. With SQL Server Snapshot Backup, after the initial full backup that is required by SQL Server to establish the backup chain, only a single transaction log backup is required to restore a database to the point in time of the transaction log backup. Furthermore, only two transaction log backups are required to restore a database to a point in time between the time of the two transaction log backups.DIFFERENTIAL
Used only with BACKUP DATABASE, specifies that the database or file backup should consist only of the portions of the database or file changed since the last full backup. A differential backup usually takes up less space than a full backup. Use this option so that all individual log backups performed since the last full backup do not have to be applied.
Note
By default,
BACKUP DATABASE
creates a full backup.For more information, see Differential Backups.
ENCRYPTIONUsed to specify encryption for a backup. You can specify an encryption algorithm to encrypt the backup with or specify
NO_ENCRYPTION
to not have the backup encrypted. Encryption is recommended practice to help secure backup files. The list of algorithms you can specify are:AES_128
AES_192
AES_256
TRIPLE_DES_3KEY
NO_ENCRYPTION
If you choose to encrypt you will also have to specify the encryptor using the encryptor options:
SERVER CERTIFICATE
= Encryptor_NameSERVER ASYMMETRIC KEY
= Encryptor_Name
The
SERVER CERTIFICATE
and SERVER ASYMMETRIC KEY
are a certificate and an asymmetric key created in master
database. For more information see CREATE CERTIFICATE
and CREATE ASYMMETRIC KEY
respectively.Warning
When encryption is used in conjunction with the
FILE_SNAPSHOT
argument, the metadata file itself is encrypted using the specified encryption algorithm and the system verifies that Transparent Data Encryption (TDE) was completed for the database. No additional encryption happens for the data itself. The backup fails if the database was not encrypted or if the encryption was not completed before the backup statement was issued.Backup Set Options Best free epub drm removal software.
These options operate on the backup set that is created by this backup operation.
Note
To specify a backup set for a restore operation, use the
FILE = <backup_set_file_number>
option. For more information about how to specify a backup set, see 'Specifying a Backup Set' in RESTORE Arguments.COPY_ONLYSpecifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.
Copy-only backups should be used in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted.
- When used with
BACKUP DATABASE
, theCOPY_ONLY
option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base.ImportantIfDIFFERENTIAL
andCOPY_ONLY
are used together,COPY_ONLY
is ignored, and a differential backup is created. - When used with
BACKUP LOG
, theCOPY_ONLY
option creates a copy-only log backup, which does not truncate the transaction log. The copy-only log backup has no effect on the log chain, and other log backups behave as if the copy-only backup does not exist.
For more information, see Copy-Only Backups.
{ COMPRESSION | NO_COMPRESSION }In SQL Server 2008 Enterprise and later versions only, specifies whether backup compression is performed on this backup, overriding the server-level default.
At installation, the default behavior is no backup compression. But this default can be changed by setting the backup compression default server configuration option. For information about viewing the current value of this option, see View or Change Server Properties.
For information about using backup compression with Transparent Data Encryption (TDE) enabled databases, see the Remarks section.
COMPRESSIONExplicitly enables backup compression.
NO_COMPRESSIONExplicitly disables backup compression.
DESCRIPTION = { 'text' | @text_variable }Specifies the free-form text describing the backup set. The string can have a maximum of 255 characters.
NAME = { backup_set_name | @backup_set_var }Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.
{ EXPIREDATE ='date' | RETAINDAYS =days }Specifies when the backup set for this backup can be overwritten. If these options are both used, RETAINDAYS takes precedence over EXPIREDATE.
If neither option is specified, the expiration date is determined by the mediaretention configuration setting. For more information, see Server Configuration Options.
Important
These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic.
EXPIREDATE = { 'date' | @date_var }Specifies when the backup set expires and can be overwritten. If supplied as a variable (@date_var), this date must follow the configured system datetime format and be specified as one of the following:
- A string constant (@date_var= date)
- A variable of character string data type (except for the ntext or text data types)
- A smalldatetime
- A datetime variable
For example:
'Dec 31, 2020 11:59 PM'
'1/1/2021'
For information about how to specify datetime values, see Date and Time Types.
Note
To ignore the expiration date, use the
SKIP
option.RETAINDAYS = { days | @days_var }Specifies the number of days that must elapse before this backup media set can be overwritten. If supplied as a variable (@days_var), it must be specified as an integer.
Media Set Options
These options operate on the media set as a whole.
{ NOINIT | INIT }Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).
Note
For information about the interactions between { NOINIT | INIT } and { NOSKIP | SKIP }, see Remarks later in this topic.
NOINITIndicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.
For more information, see Media Sets, Media Families, and Backup Sets.
INITSpecifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:
- Any backup set has not yet expired. For more information, see the
EXPIREDATE
andRETAINDAYS
options. - The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME option, earlier in this section.
To override these checks, use the
SKIP
option.For more information, see Media Sets, Media Families, and Backup Sets.
{ NOSKIP | SKIP }Controls whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them.
Note
For information about the interactions between { NOINIT | INIT } and { NOSKIP | SKIP }, see 'Remarks,' later in this topic.
NOSKIPInstructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten. This is the default behavior.
SKIPDisables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets. For information about the interactions between { INIT | NOINIT } and { NOSKIP | SKIP }, see 'Remarks,' later in this topic.To view the expiration dates of backup sets, query the expiration_date column of the backupset history table.
{ NOFORMAT | FORMAT }Specifies whether the media header should be written on the volumes used for this backup operation, overwriting any existing media header and backup sets.
NOFORMATSpecifies that the backup operation preserves the existing media header and backup sets on the media volumes used for this backup operation. This is the default behavior.
FORMATSpecifies that a new media set be created. FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.
Important
Use
FORMAT
carefully. Formatting any volume of a media set renders the entire media set unusable. For example, if you initialize a single tape belonging to an existing striped media set, the entire media set is rendered useless.Specifying FORMAT implies
SKIP
; SKIP
does not need to be explicitly stated.MEDIADESCRIPTION = { text | @text_variable }Specifies the free-form text description, maximum of 255 characters, of the media set.
MEDIANAME = { media_name | @media_name_variable }Specifies the media name for the entire backup media set. The media name must be no longer than 128 characters, If
MEDIANAME
is specified, it must match the previously specified media name already existing on the backup volumes. If it is not specified, or if the SKIP option is specified, there is no verification check of the media name.BLOCKSIZE = { blocksize | @blocksize_variable }Specifies the physical block size, in bytes. The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. The default is 65536 for tape devices and 512 otherwise. Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. Explicitly stating a block size overrides the automatic selection of block size.
If you are taking a backup that you plan to copy onto and restore from a CD-ROM, specify BLOCKSIZE=2048.
Note
This option typically affects performance only when writing to tape devices.
Data Transfer Options
BUFFERCOUNT = { buffercount | @buffercount_variable }Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause 'out of memory' errors because of inadequate virtual address space in the Sqlservr.exe process.
The total space used by the buffers is determined by:
BUFFERCOUNT * MAXTRANSFERSIZE
.Note
For important information about using the
BUFFERCOUNT
option, see the Incorrect BufferCount data transfer option can lead to OOM condition blog.MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable }Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).
Note
When creating backups by using the SQL Writer Service, if the database has configured FILESTREAM, or includes memory optimized filegroups, then the
MAXTRANSFERSIZE
at the time of a restore should be greater than or equal to the MAXTRANSFERSIZE
that was used when the backup was created.Note
For Transparent Data Encryption (TDE) enabled databases with a single data file, the default
MAXTRANSFERSIZE
is 65536 (64 KB). For non-TDE encrypted databases the default MAXTRANSFERSIZE
is 1048576 (1 MB) when using backup to DISK, and 65536 (64 KB) when using VDI or TAPE.For more information about using backup compression with TDE encrypted databases, see the Remarks section.Error Management Options
These options allow you to determine whether backup checksums are enabled for the backup operation and whether the operation stops on encountering an error.
{ NO_CHECKSUM | CHECKSUM }Controls whether backup checksums are enabled.
NO_CHECKSUMExplicitly disables the generation of backup checksums (and the validation of page checksums). This is the default behavior.
CHECKSUMSpecifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.
Using backup checksums may affect workload and backup throughput.
For more information, see Possible Media Errors During Backup and Restore.
{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }Controls whether a backup operation stops or continues after encountering a page checksum error.
STOP_ON_ERRORInstructs BACKUP to fail if a page checksum does not verify. This is the default behavior.
CONTINUE_AFTER_ERRORInstructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.
If you are unable to back up the tail of the log using the NO_TRUNCATE option when the database is damaged, you can attempt a tail-log log backup by specifying CONTINUE_AFTER_ERROR instead of NO_TRUNCATE.
For more information, see Possible Media Errors During Backup and Restore.
Compatibility Options
RESTARTBeginning with SQL Server 2008, has no effect. This option is accepted by the version for compatibility with previous versions of SQL Server.
Monitoring Options
STATS [ =percentage ]Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.
The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.
Tape Options
These options are used only for TAPE devices. If a nontape device is being used, these options are ignored.
{ REWIND | NOREWIND }REWIND
Specifies that SQL Server releases and rewinds the tape. REWIND is the default.
NOREWIND
Specifies that SQL Server will keep the tape open after the backup operation. You can use this option to help improve performance when performing multiple backup operations to a tape.
NOREWIND implies NOUNLOAD, and these options are incompatible within a single BACKUP statement.
Note
If you use
NOREWIND
, the instance of SQL Server retains ownership of the tape drive until a BACKUP or RESTORE statement that is running in the same process uses either the REWIND
or UNLOAD
option, or the server instance is shut down. Keeping the tape open prevents other processes from accessing the tape. For information about how to display a list of open tapes and to close an open tape, see Backup Devices.{ UNLOAD | NOUNLOAD }
Note
UNLOAD
and NOUNLOAD
are session settings that persist for the life of the session or until it is reset by specifying the alternative.UNLOAD
Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is the default when a session begins.
NOUNLOAD
Specifies that after the BACKUP operation the tape remains loaded on the tape drive.
Note
For a backup to a tape backup device, the
BLOCKSIZE
option to affect the performance of the backup operation. This option typically affects performance only when writing to tape devices.Log-specific options
These options are only used with
BACKUP LOG
.Note
If you do not want to take log backups, use the simple recovery model. For more information, see Recovery Models.
{ NORECOVERY | STANDBY =undo_file_name }
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation.
To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the
NO_TRUNCATE
and NORECOVERY
options together.STANDBY =standby_file_name
Backs up the tail of the log and leaves the database in a read-only and STANDBY state. The STANDBY clause writes standby data (performing rollback, but with the option of further restores). Using the STANDBY option is equivalent to BACKUP LOG WITH NORECOVERY followed by a RESTORE WITH STANDBY.
Using standby mode requires a standby file, specified by standby_file_name, whose location is stored in the log of the database. If the specified file already exists, the Database Engine overwrites it; if the file does not exist, the Database Engine creates it. The standby file becomes part of the database.
This file holds the rolled back changes, which must be reversed if RESTORE LOG operations are to be subsequently applied. There must be enough disk space for the standby file to grow so that it can contain all the distinct pages from the database that were modified by rolling back uncommitted transactions.
NO_TRUNCATE
Specifies that the is log not truncated and causes the Database Engine to attempt the backup regardless of the state of the database. Consequently, a backup taken with
NO_TRUNCATE
might have incomplete metadata. This option allows backing up the log in situations where the database is damaged.The NO_TRUNCATE option of BACKUP LOG is equivalent to specifying both COPY_ONLY and CONTINUE_AFTER_ERROR.
Without the
NO_TRUNCATE
option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE
. But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE
. For information about database states, see Database States.About working with SQL Server backups
This section introduces the following essential backup concepts:
Note
For an introduction to backup in SQL Server, see Backup Overview.
Backup types
The supported backup types depend on the recovery model of the database, as follows
- All recovery models support full and differential backups of data.
Scope of backup Backup types Whole database Database backups cover the whole database.
Optionally, each database backup can serve as the base of a series of one or more differential database backups.Partial database Partial backups cover read/-write filegroups and, possibly, one or more read-only files or filegroups.
Optionally, each partial backup can serve as the base of a series of one or more differential partial backups.File or filegroup File backups cover one or more files or filegroups, and are relevant only for databases that contain multiple filegroups. Under the simple recovery model, file backups are essentially restricted to read-only secondary filegroups.
Optionally, each file backup can serve as the base of a series of one or more differential file backups. - Under the full recovery model or bulk-logged recovery model, conventional backups also include sequential transaction log backups (or log backups), which are required. Each log backup covers the portion of the transaction log that was active when the backup was created, and it includes all log records not backed up in a previous log backup.To minimize work-loss exposure, at the cost of administrative overhead, 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.We recommend that you put log backups on a separate volume than the database backups.NoteBefore you can create the first log backup, you must create a full backup.
- A copy-only backup is a special-purpose full backup or log backup that is independent of the normal sequence of conventional backups. To create a copy-only backup, specify the COPY_ONLY option in your BACKUP statement. For more information, see Copy-Only Backups.
Transaction Log Truncation
To avoid filling up the transaction log of a database, routine backups are essential. Under the simple recovery model, log truncation occurs automatically after you back up the database, and under the full recovery model, after you back up the transaction log. However, sometimes the truncation process can be delayed. For information about factors that can delay log truncation, see The Transaction Log.
Note
The
BACKUP LOG WITH NO_LOG
and WITH TRUNCATE_ONLY
options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. For more information, see View or Change the Recovery Model of a Database.Formatting Backup Media
Backup media is formatted by a BACKUP statement if and only if any of the following is true:
- The
FORMAT
option is specified. - The media is empty.
- The operation is writing a continuation tape.
Working with backup devices and media sets
Backup devices in a striped media set (a stripe set)
A stripe set is a set of disk files on which data is divided into blocks and distributed in a fixed order. The number of backup devices used in a stripe set must stay the same (unless the media is reinitialized with
FORMAT
).The following example writes a backup of the AdventureWorks2012 database to a new striped media set that uses three disk files.
After a backup device is defined as part of a stripe set, it cannot be used for a single-device backup unless FORMAT is specified. Similarly, a backup device that contains nonstriped backups cannot be used in a stripe set unless FORMAT is specified. To split a striped backup set, use FORMAT.
If neither MEDIANAME or MEDIADESCRIPTION is specified when a media header is written, the media header field corresponding to the blank item is empty.
Working with a mirrored media set
Typically, backups are unmirrored, and BACKUP statements simply include a TO clause. However, a total of four mirrors is possible per media set. For a mirrored media set, the backup operation writes to multiple groups of backup devices. Each group of backup devices comprises a single mirror within the mirrored media set. Every mirror must use the same quantity and type of physical backup devices, which must all have the same properties.
To back up to a mirrored media set, all of the mirrors must be present. To back up to a mirrored media set, specify the
TO
clause to specify the first mirror, and specify a MIRROR TO
clause for each additional mirror.For a mirrored media set, each
MIRROR TO
clause must list the same number and type of devices as the TO clause. The following example writes to a mirrored media set that contains two mirrors and uses three devices per mirror:Important
This example is designed to allow you to test it on your local system. In practice, backing up to multiple devices on the same drive would hurt performance and would eliminate the redundancy for which mirrored media sets are designed.
Media families in mirrored media sets
Each backup device specified in the
TO
clause of a BACKUP statement corresponds to a media family. For example, if the TO
clauses lists three devices, BACKUP writes data to three media families. In a mirrored media set, every mirror must contain a copy of every media family. This is why the number of devices must be identical in every mirror.When multiple devices are listed for each mirror, the order of the devices determines which media family is written to a particular device. For example, in each of the device lists, the second device corresponds to the second media family. For the devices in the above example, the correspondence between devices and media families is shown in the following table.
Mirror | Media family 1 | Media family 2 | Media family 3 |
---|---|---|---|
0 | Z:AdventureWorks1a.bak | Z:AdventureWorks2a.bak | Z:AdventureWorks3a.bak |
1 | Z:AdventureWorks1b.bak | Z:AdventureWorks2b.bak | Z:AdventureWorks3b.bak |
A media family must always be backed up onto the same device within a specific mirror. Therefore, each time you use an existing media set, list the devices of each mirror in the same order as they were specified when the media set was created.
For more information about mirrored media sets, see Mirrored Backup Media Sets. For more information about media sets and media families in general, see Media Sets, Media Families, and Backup Sets.
Restoring SQL Server backups
To restore a database and, optionally, recover it to bring it online, or to restore a file or filegroup, use either the Transact-SQL RESTORE statement or the SQL Server Management Studio Restore tasks. For more information see Restore and Recovery Overview.
Additional considerations about BACKUP options
Interaction of SKIP, NOSKIP, INIT, and NOINIT
This table describes interactions between the { NOINIT | INIT } and { NOSKIP | SKIP } options.
Note
If the tape media is empty or the disk backup file does not exist, all these interactions write a media header and proceed. If the media is not empty and lacks a valid media header, these operations give feedback stating that this is not valid MTF media, and they terminate the backup operation.
NOINIT | INIT | |
---|---|---|
NOSKIP | If the volume contains a valid media header, verifies that the media name matches the given MEDIANAME , if any. If it matches, appends the backup set, preserving all existing backup sets.If the volume does not contain a valid media header, an error occurs. | If the volume contains a valid media header, performs the following checks:
If these checks pass, overwrites any backup sets on the media, preserving only the media header. If the volume does not contain a valid media header, generates one with using specified MEDIANAME and MEDIADESCRIPTION , if any. |
SKIP | If the volume contains a valid media header, appends the backup set, preserving all existing backup sets. | If the volume contains a valid2 media header, overwrites any backup sets on the media, preserving only the media header. If the media is empty, generates a media header using the specified MEDIANAME and MEDIADESCRIPTION , if any. |
1 The user must belong to the appropriate fixed database or server roles to perform a backup operation.
2 Validity includes the MTF version number and other header information. If the version specified is unsupported or an unexpected value, an error occurs.
Compatibility
Caution
Backups that are created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server.
BACKUP supports the
RESTART
option to provide backward compatibility with earlier versions of SQL Server. But RESTART has no effect.General remarks
Database or log backups can be appended to any disk or tape device, allowing a database and its transaction logs to be kept within one physical location.
The BACKUP statement is not allowed in an explicit or implicit transaction.
Cross-platform backup operations, even between different processor types, can be performed as long as the collation of the database is supported by the operating system.
When using backup compression with Transparent Data Encryption (TDE) enabled databases with a single data file, it is recommended to use a
MAXTRANSFERSIZE
setting larger than 65536 (64 KB).Starting with SQL Server 2016 (13.x), this enables an optimized compression algorithm for TDE encrypted databases that first decrypts a page, compresses it and then encrypts it again. If using MAXTRANSFERSIZE = 65536
(64 KB), backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios. For more information, see Backup Compression for TDE-enabled Databases.Note
There are some cases where the default
MAXTRANSFERSIZE
is greater than 64K:- When the database has multiple data files created, it uses
MAXTRANSFERSIZE
> 64K - When performing backup to URL, the default
MAXTRANSFERSIZE = 1048576
(1MB)
Even if one of these conditions applies, you must explicitly set
MAXTRANSFERSIZE
greater than 64K in your backup command in order to get the new backup compression algorithm.By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags.
Interoperability
SQL Server uses an online backup process to allow a database backup while the database is still in use. During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation.
Operations that cannot run during a database or transaction log backup include:
- File management operations such as the
ALTER DATABASE
statement with either theADD FILE
orREMOVE FILE
options. - Shrink database or shrink file operations. This includes auto-shrink operations.
If a backup operation overlaps with a file-management or shrink operation, a conflict arises. Regardless of which of the conflicting operation began first, the second operation waits for the lock set by the first operation to time out (the time-out period is controlled by a session timeout setting). If the lock is released during the time-out period, the second operation continues. If the lock times out, the second operation fails.
Metadata
SQL Server includes the following backup history tables that track backup activity:
When a restore is performed, if the backup set was not already recorded in the msdb database, the backup history tables might be modified.
Security
Beginning with SQL Server 2012 (11.x), the
PASSWORD
and MEDIAPASSWORD
options are discontinued for creating backups. It is still possible to restore backups created with passwords.Permissions
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.
Examples
This section contains the following examples:
- A. Backing up a complete database
- B. Backing up the database and log
- C. Creating a full file backup of the secondary filegroups
- D. Creating a differential file backup of the secondary filegroups
- E. Creating and backing up to a single-family mirrored media set
- F. Creating and backing up to a multifamily mirrored media set
- G. Backing up to an existing mirrored media set
- H. Creating a compressed backup in a new media set
- I. Backing up to the Microsoft Azure Blob storage service
- J. Track the progress of backup statement
Note
The backup how-to topics contain additional examples. For more information, see Backup Overview.
A. Backing up a complete database
The following example backs up the AdventureWorks2012 database to a disk file.
B. Backing up the database and log
The following example backups up the AdventureWorks2012 sample database, which uses the simple recovery model by default. To support log backups, the AdventureWorks2012 database is modified to use the full recovery model.
Next, the example uses sp_addumpdevice to create a logical backup device for backing up data,
AdvWorksData
, and creates another logical backup device for backing up the log, AdvWorksLog
.The example then creates a full database backup to
AdvWorksData
, and after a period of update activity, backs up the log to AdvWorksLog
.Note
For a production database, back up the log regularly. Log backups should be frequent enough to provide sufficient protection against data loss.
C. Creating a full file backup of the secondary filegroups
The following example creates a full file backup of every file in both of the secondary filegroups.
D. Creating a differential file backup of the secondary filegroups
The following example creates a differential file backup of every file in both of the secondary filegroups.
E. Creating and backing up to a single-family mirrored media set
The following example creates a mirrored media set containing a single media family and four mirrors and backs up the AdventureWorks2012 database to them.
F. Creating and backing up to a multifamily mirrored media set
The following example creates a mirrored media set in which each mirror consists of two media families. The example then backs up the AdventureWorks2012 database to both mirrors.
G. Backing up to an existing mirrored media set
The following example appends a backup set to the media set created in the preceding example.
Note
NOINIT, which is the default, is shown here for clarity.
H. Creating a compressed backup in a new media set
The following example formats the media, creating a new media set, and perform a compressed full backup of the AdventureWorks2012 database.
I. Backing up to the Microsoft Azure Blob storage service
The example performs a full database backup of
Sales
to the Microsoft Azure Blob storage service. The storage Account name is mystorageaccount
. The container is called myfirstcontainer
. A stored access policy has been created with read, write, delete, and list rights. The SQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer
, was created using a Shared Access Signature that is associated with the Stored Access Policy. For information on SQL Server backup to the Microsoft Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service and SQL Server Backup to URL.J. Track the progress of backup statement
The following query returns information about the currently running backup statements:
See Also
SQL Server | * SQL Database managed instance * | Analytics Platform System (PDW) |
Azure SQL Database managed instance
Backs up a SQL database placed/hosted in an Azure SQL Databae managed instance. SQL Database managed instance has automatic backups, and enables users to create full database
COPY_ONLY
backups. Differential, log, and file snapshot backups are not supported.Syntax
Arguments
DATABASESpecifies a complete database backup. During a database backup, the managed instance backs up enough of the transaction log to produce a consistent database when the backup is restored.
Important
A database backup created on a managed instance can only be restored on another managed instance. It cannot be restored to a SQL Server on-premises instance (similar to the way that a backup of a SQL Server 2016 database cannot be restored to a SQL Server 2012 instance).
When you restore a backup created by BACKUP DATABASE (a data backup), the entire backup is restored. To restore from Azure SQL Database managed instance automatic backups, see Restore a database to a Managed Instance.
{ database_name | @database_name_var }Is the database from which the complete database is backed up. If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var=database name) or as a variable of character string data type, except for the ntext or text data types.
For more information, see Full File Backups and Back Up Files and Filegroups.
TO URL
Specifies the URL to use for the backup operation. The URL format is used for creating backups to the Microsoft Azure storage service.
Important
In order to backup to multiple devices when backing up to URL, you must use Shared Access Signature (SAS) tokens. For examples creating a Shared Access Signature, see SQL Server Backup to URL and Simplifying creation of SQL Credentials with Shared Access Signature (SAS) tokens on Azure Storage with Powershell.
nIs a placeholder that indicates that up to 64 backup devices may be specified in a comma-separated list.
WITH OptionsSpecifies options to be used with a backup operation
CREDENTIALUsed only when creating a backup to the Microsoft Azure Blob storage service.
ENCRYPTIONUsed to specify encryption for a backup. You can specify an encryption algorithm to encrypt the backup with or specify
NO_ENCRYPTION
to not have the backup encrypted. Encryption is recommended practice to help secure backup files. The list of algorithms you can specify are:AES_128
AES_192
AES_256
TRIPLE_DES_3KEY
NO_ENCRYPTION
If you choose to encrypt you will also have to specify the encryptor using the encryptor options:
SERVER CERTIFICATE = <Encryptor_Name>
SERVER ASYMMETRIC KEY = <Encryptor_Name>
Backup Set Options
COPY_ONLYSpecifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of the Azure SQL Database automatic backups. For more information, see Copy-Only Backups.
{ COMPRESSION | NO_COMPRESSION }Specifies whether backup compression is performed on this backup, overriding the server-level default.
The default behavior is no backup compression. But this default can be changed by setting the backup compression default server configuration option. For information about viewing the current value of this option, see View or Change Server Properties.
For information about using backup compression with Transparent Data Encryption (TDE) enabled databases, see the Remarks section.
COMPRESSIONExplicitly enables backup compression.
NO_COMPRESSIONExplicitly disables backup compression.
DESCRIPTION = { 'text' | @text_variable }Specifies the free-form text describing the backup set. The string can have a maximum of 255 characters.
NAME = { backup_set_name | @backup_set_var }Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.
MEDIADESCRIPTION = { text | @text_variable }Specifies the free-form text description, maximum of 255 characters, of the media set.
MEDIANAME = { media_name | @media_name_variable }Specifies the media name for the entire backup media set. The media name must be no longer than 128 characters, If
MEDIANAME
is specified, it must match the previously specified media name already existing on the backup volumes. If it is not specified, or if the SKIP option is specified, there is no verification check of the media name.BLOCKSIZE = { blocksize | @blocksize_variable }Specifies the physical block size, in bytes. The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. The default is 65536 for tape devices and 512 otherwise. Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. Explicitly stating a block size overrides the automatic selection of block size.
Data Transfer Options
BUFFERCOUNT = { buffercount | @buffercount_variable }Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause 'out of memory' errors because of inadequate virtual address space in the Sqlservr.exe process.
The total space used by the buffers is determined by:
BUFFERCOUNT * MAXTRANSFERSIZE
.Note
For important information about using the
BUFFERCOUNT
option, see the Incorrect BufferCount data transfer option can lead to OOM condition blog.MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable }Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).
Note
For Transparent Data Encryption (TDE) enabled databases with a single data file, the default
MAXTRANSFERSIZE
is 65536 (64 KB). For non-TDE encrypted databases the default MAXTRANSFERSIZE
is 1048576 (1 MB) when using backup to DISK, and 65536 (64 KB) when using VDI or TAPE.For more information about using backup compression with TDE encrypted databases, see the Remarks section.Error Management Options
These options allow you to determine whether backup checksums are enabled for the backup operation and whether the operation stops on encountering an error.
{ NO_CHECKSUM | CHECKSUM }Controls whether backup checksums are enabled.
NO_CHECKSUMExplicitly disables the generation of backup checksums (and the validation of page checksums). This is the default behavior.
CHECKSUMSpecifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.
Using backup checksums may affect workload and backup throughput.
For more information, see Possible Media Errors During Backup and Restore.
{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }Controls whether a backup operation stops or continues after encountering a page checksum error.
STOP_ON_ERRORInstructs BACKUP to fail if a page checksum does not verify. This is the default behavior.
CONTINUE_AFTER_ERRORInstructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.
If you are unable to back up the tail of the log using the NO_TRUNCATE option when the database is damaged, you can attempt a tail-log log backup by specifying CONTINUE_AFTER_ERROR instead of NO_TRUNCATE.
For more information, see Possible Media Errors During Backup and Restore.
Compatibility Options
RESTARTHas no effect. This option is accepted by the version for compatibility with previous versions of SQL Server.
Monitoring Options
STATS [ =percentage ]Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.
The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.
Limitations for SQL Database managed instance
Max backup stripe size is 195 GB (maximum blob size). Increase the number of stripes in the backup command to reduce individual stripe size and stay within this limit.
Security
Permissions
BACKUP DATABASE permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
Ownership and permission problems on the URL can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions.
Examples
The example performs a COPY_ONLY backup of
Sales
to the Microsoft Azure Blob storage service. The storage Account name is mystorageaccount
. The container is called myfirstcontainer
. A stored access policy has been created with read, write, delete, and list rights. The SQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer
, was created using a Shared Access Signature that is associated with the Stored Access Policy. For information on SQL Server backup to the Microsoft Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service and SQL Server Backup to URL.See Also
SQL Server | SQL Database managed instance | * Analytics Platform System (PDW) * |
Analytics Platform System
Creates a backup of a Parallel Data Warehouse database and stores the backup off the appliance in a user-specified network location. Use this statement with RESTORE DATABASE - Analytics Platform System for disaster recovery, or to copy a database from one appliance to another.
Before you begin, see 'Acquire and Configure a Backup Server' in the Parallel Data Warehouse product documentation.
There are two types of backups in Parallel Data Warehouse. A full database backup is a backup of an entire Parallel Data Warehouse database. A differential database backup only includes changes made since the last full backup. A backup of a user database includes database users, and database roles. A backup of the master database includes logins.
For more information about Parallel Data Warehouse database backups, see 'Backup and Restore' in the Parallel Data Warehouse product documentation.
Syntax
Arguments
database_nameThe name of the database on which to create a backup. The database can be the master database or a user database.
TO DISK = 'UNC_pathbackup_directory'The network path and directory to which Parallel Data Warehouse will write the backup files. For example, 'xxx.xxx.xxx.xxxbackups2012Monthly08.2012.Mybackup'.
- The path to the backup directory name must already exist and must be specified as a fully qualified universal naming convention (UNC) path.
- The backup directory, backup_directory, must not exist before running the backup command. Parallel Data Warehouse will create the backup directory.
- The path to the backup directory cannot be a local path and it cannot be a location on any of the Parallel Data Warehouse appliance nodes.
- The maximum length of the UNC path and backup directory name is 200 characters.
- The server or host must be specified as an IP address. You cannot specify it as the host or server name.
DESCRIPTION = 'text'Specifies a textual description of the backup. The maximum length of the text is 255 characters.
The description is stored in the metadata, and will be displayed when the backup header is restored with RESTORE HEADERONLY.
NAME = 'backup _name'Specifies the name of the backup. The backup name can be different from the database name.
- Names can have a maximum of 128 characters.
- Cannot include a path.
- Must begin with a letter or number character or an underscore (_). Special characters permitted are the underscore (_), hyphen (-), or space ( ). Backup names cannot end with a space character.
- The statement will fail if backup_name already exists in the specified location.
This name is stored in the metadata, and will be displayed when the backup header is restored with RESTORE HEADERONLY.
DIFFERENTIALSpecifies to perform a differential backup of a user database. If omitted, the default is a full database backup. The name of the differential backup does not need to match the name of the full backup. For keeping track of the differential and its corresponding full backup, consider using the same name with 'full' or 'diff' appended.
For example:
BACKUP DATABASE Customer TO DISK = 'xxx.xxx.xxx.xxxbackupsCustomerFull';
BACKUP DATABASE Customer TO DISK = 'xxx.xxx.xxx.xxxbackupsCustomerDiff' WITH DIFFERENTIAL;
Permissions
Requires the BACKUP DATABASE permission or membership in the db_backupoperator fixed database role. The master database cannot be backed up but by a regular user that was added to the db_backupoperator fixed database role. The master database can only be backed up by sa, the fabric administrator, or members of the sysadmin fixed server role.
Requires a Windows account that has permission to access, create, and write to the backup directory. You must also store the Windows account name and password in Parallel Data Warehouse. To add these network credentials to Parallel Data Warehouse, use the sp_pdw_add_network_credentials - SQL Data Warehous stored procedure.
For more information about managing credentials in Parallel Data Warehouse, see the Security section.
Error Handling
BACKUP DATABASE errors under the following conditions:
- User permissions are not sufficient to perform a backup.
- Parallel Data Warehouse does not have the correct permissions to the network location where the backup will be stored.
- The database does not exist.
- The target directory already exists on the network share.
- The target network share is not available.
- The target network share does not have enough space for the backup. The BACKUP DATABASE command does not confirm that sufficient disk space exists prior to initiating the backup, making it possible to generate an out-of-disk-space error while running BACKUP DATABASE. When insufficient disk space occurs, Parallel Data Warehouse rolls back the BACKUP DATABASE command. To decrease the size of your database, run DBCC SHRINKLOG (Azure SQL Data Warehouse)
- Attempt to start a backup within a transaction.
General Remarks
Before you perform a database backup, use DBCC SHRINKLOG (Azure SQL Data Warehouse) to decrease the size of your database.
A Parallel Data Warehouse backup is stored as a set of multiple files within the same directory.
A differential backup usually takes less time than a full backup and can be performed more frequently. When multiple differential backups are based on the same full backup, each differential includes all of the changes in the previous differential backup.
If you cancel a BACKUP command, Parallel Data Warehouse will remove the target directory and any files created for the backup. If Parallel Data Warehouse loses network connectivity to the share, the rollback cannot complete.
Full backups and differential backups are stored in separate directories. Naming conventions are not enforced for specifying that a full backup and differential backup belong together. You can track this through your own naming conventions. Alternatively, you can track this by using the WITH DESCRIPTION option to add a description, and then by using the RESTORE HEADERONLY statement to retrieve the description.
Limitations and Restrictions
You cannot perform a differential backup of the master database. Only full backups of the master database are supported.
The backup files are stored in a format suitable only for restoring the backup to a Parallel Data Warehouse appliance by using the RESTORE DATABASE - Analytics Platform System statement.
The backup with the BACKUP DATABASE statement cannot be used to transfer data or user information to SMP SQL Server databases. For that functionality, you can use the remote table copy feature. For more information, see 'Remote Table Copy' in the Parallel Data Warehouse product documentation.
Parallel Data Warehouse uses SQL Server backup technology to backup and restore databases. SQL Server backup options are preconfigured to use backup compression. You cannot set backup options such as compression, checksum, block size, and buffer count.
Only one database backup or restore can run on the appliance at any given time. Parallel Data Warehouse will queue backup or restore commands until the current backup or restore command has completed.
The target appliance for restoring the backup must have at least as many Compute nodes as the source appliance. The target can have more Compute nodes than the source appliance, but cannot have fewer Compute nodes.
Parallel Data Warehouse does not track the location and names of backups since the backups are stored off the appliance.
Parallel Data Warehouse does track the success or failure of database backups.
A differential backup is only allowed if the last full backup completed successfully. For example, suppose that on Monday you create a full backup of the Sales database and the backup finishes successfully. Then on Tuesday you create a full backup of the Sales database and it fails. After this failure, you cannot then create a differential backup based on Monday's full backup. You must first create a successful full backup before creating a differential backup.
Metadata
These dynamic management views contain information about all backup, restore, and load operations. The information persists across system restarts.
Performance
To perform a backup, Parallel Data Warehouse first backs up the metadata, and then it performs a parallel backup of the database data stored on the Compute nodes. Data is copied directly from each Compute nodes to the backup directory. To achieve the best performance for moving data from the Compute nodes to the backup directory, Parallel Data Warehouse controls the number of Compute nodes that are copying data concurrently.
Locking
Takes an ExclusiveUpdate lock on the DATABASE object.
Security
Parallel Data Warehouse backups are not stored on the appliance. Therefore, your IT team is responsible for managing all aspects of the backup security. For example, this includes managing the security of the backup data, the security of the server used to store backups, and the security of the networking infrastructure that connects the backup server to the Parallel Data Warehouse appliance.
Manage Network Credentials
Network access to the backup directory is based on standard Windows file sharing security. Before performing a backup, you need to create or designate a Windows account that will be used for authenticating Parallel Data Warehouse to the backup directory. This windows account must have permission to access, create, and write to the backup directory.
Important
To reduce security risks with your data, we advise that you designate one Windows account solely for the purpose of performing backup and restore operations. Allow this account to have permissions to the backup location and nowhere else.
You need to store the user name and password in Parallel Data Warehouse by running the sp_pdw_add_network_credentials - SQL Data Warehouse stored procedure. Parallel Data Warehouse uses Windows Credential Manager to store and encrypt user names and passwords on the Control node and Compute nodes. The credentials are not backed up with the BACKUP DATABASE command.
To remove network credentials from Parallel Data Warehouse, see sp_pdw_remove_network_credentials - SQL Data Warehouse.
To list all of the network credentials stored in Parallel Data Warehouse, use the sys.dm_pdw_network_credentials dynamic management view.
Examples
Microsoft Sql Backup Database Script
A. Add network credentials for the backup location
To create a backup, Parallel Data Warehouse must have read/write permission to the backup directory. The following example shows how to add the credentials for a user. Parallel Data Warehouse will store these credentials and use them to for backup and restore operations.
Important
For security reasons, we recommend creating one domain account solely for the purpose of performing backups.
B. Remove network credentials for the backup location
Backup Microsoft Sql Server Database
The following example shows how to remove the credentials for a domain user from Parallel Data Warehouse.
C. Create a full backup of a user database
The following example creates a full backup of the Invoices user database. Parallel Data Warehouse will create the Invoices2013 directory and will save the backup files to the 10.192.63.147backupsyearlyInvoices2013Full directory.
D. Create a differential backup of a user database
The following example creates a differential backup, which includes all changes made since the last full backup of the Invoices database. Parallel Data Warehouse will create the xxx.xxx.xxx.xxxbackupsyearlyInvoices2013Diff directory to which it will store the files. The description 'Invoices 2013 differential backup' will be stored with the header information for the backup.
The differential backup will only run successfully if the last full backup of Invoices completed successfully.
E. Create a full backup of the master database
The following example creates a full backup of the master database and stores it in the directory '10.192.63.147backups2013daily20130722master'.
F. Create a backup of appliance login information
The master database stores the appliance login information. To backup the appliance login information you need to backup master.
The following example creates a full backup of the master database.