APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
-->
Where MyDatabase is a SQL Server database you wish to backup. Backupdestination is a place where you want to write a backup. WITH withoptions is a command you may use to apply different options to a backup such as a compression or an encryption or a description and so on. To find more information about backup options you may read an article. Best Practice recommendations for SQL Server Database Backups. By the backup utilities BACKUP TSQL command, SQL Server Maintenance Plans, your backup software.
- The most important part of a SQL Server maintenance plan is backing up your databases regularly. To backup a database, you cannot simply copy the respective MDF and LDF files of the database as SQL Server has a lock on these. Instead, you need to create a true backup file through SQL Server.
- To back up a database by server instance and database name, specify the server instance in the ServerInstance parameter and the database name in the Database parameter. To back up a database using an Smo.Database object, specify the Smo.Database object in the DatabaseObject parameter, either directly or by using the pipeline operator. By default this cmdlet performs a full database backup. Set the type of the backup by using the BackupAction parameter.
- Verify that you have created a CREDENTIAL with the name equal to the URL of the blob storage where you want to backup your database. Script the CREDENTIAL and backup a database from SQL Server to Azure Blob Storage account. Check if your SAS credential placed in SECRET option of CREATE CREDENTIAL statement is valid.
This topic describes how to create a full database backup in SQL Server 2017 using SQL Server Management Studio, Transact-SQL, or PowerShell.
For information on SQL Server backup to the Azure Blob storage service, see SQL Server Backup and Restore with Azure Blob Storage Service and SQL Server Backup to URL.
Limitations and restrictions
- The
BACKUP
statement is not allowed in an explicit or implicit transaction. - Backups created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server.
For an overview of, and deeper dive into, backup concepts and tasks, see Backup Overview (SQL Server) before proceeding.
Recommendations
- As a database increases in size, full database backups take more time to complete and require more storage space. For large databases, consider supplementing full database backups with a series of differential database backups.
- Estimate the size of a full database backup by using the sp_spaceused system stored procedure.
- By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you back up frequently, these success messages will accumulate quickly, resulting in huge error logs! This can make finding other messages difficult. In such cases, you can suppress these backup log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).
![Backup Microsoft Sql Database Backup Microsoft Sql Database](/uploads/1/2/4/8/124863842/790920723.png)
Security
TRUSTWORTHY is set to OFF on a database backup. For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE SET Options (Transact-SQL).
Beginning with SQL Server 2012 (11.x), the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. You can still 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. The SQL Server service must be able to read and write to the device which means that the account under which the SQL Server service runs must have write permissions to the backup device. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. As a result, problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.
Using SQL Server Management Studio
Note
Backup All Microsoft Sql Databases
When you specify a backup task by using SQL Server Management Studio, you can generate the corresponding Transact-SQL BACKUP script by clicking the Script button and selecting a script destination.
-
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
-
Expand Databases, and either select a user database or expand System Databases and select a system database.
-
Right-click the database that you wish to backup, point to Tasks, and then click Back Up...
-
In the Back Up Database dialog box, the database that you selected appears in the drop-down list (which you can change to any other database on the server).
-
In the Backup type drop-down list, select the desired backup type - the default is Full.ImportantYou must perform at least one full database backup before you can perform a differential or a transaction log backup.
-
Under Backup component, select Database.
-
In the Destination section, review the default location for the backup file (in the ./mssql/data folder).To backup to a different device, change the selection using the Back up to drop-down list. To stripe the backup set across multiple files for increased backup speed, click Add to add additional backup objects and/or destinations.To remove a backup destination, select it and click Remove. To view the contents of an existing backup destination, select it and click Contents.
-
(optional) Review the other available settings under the Media Options and Backup Options pages.For more information about the various backup options, see General page, Media options page, and Backup options page.
-
Click OK to initiate the backup.
-
When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.
Additional information
-
After creating a full database backup, you can create a differential database backup or a transaction log backup.
-
(optional) You can select the Copy-only backup checkbox to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. For more information, see Copy-Only Backups (SQL Server). A copy-only backup is not available for the Differential backup type.
-
The Overwrite media option is disabled on the Media Options page if you are backing up to a URL.
Examples
For the following examples, create a test database with the following Transact-SQL code:
A. Full back up to disk to default location
In this example, the
SQLTestDB
database will be backed up to disk at the default backup location.
-
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
-
Expand Databases, right-click
SQLTestDB
, point to Tasks, and then click Back Up... -
Click OK.
-
When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.
B. Full back up to disk to non-default location
In this example, the
SQLTestDB
database will be backed up to disk at a location of your choice.
-
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
-
Expand Databases, right-click
SQLTestDB
, point to Tasks, and then click Back Up... -
On the General page in the Destination section select Disk from the Back up to: drop-down list.
-
Select Remove until all existing backup files have been removed.
-
Select Add and the Select Backup Destination dialog box will open.
-
Enter a valid path and file name in the File name text box and use .bak as the extension to simplify the classification of this file.
-
Click OK and then click OK again to initiate the backup.
-
When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.
C. Create an encrypted backup
In this example, the
SQLTestDB
database will be backed up with encryption to the default backup location.
-
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
-
Expand Databases, expand System Databases, right-click
master
, and click New Query to open a query window with a connection to yourSQLTestDB
database. -
Execute the following commands to create a database master key and a certificate within the
master
database. -
In Object Explorer, in the Databases node, right-click
SQLTestDB
, point to Tasks, and then click Back Up... -
On the Media Options page, in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.
-
On the Backup Options page in the Encryption section select the Encrypt backup check box.
-
From the Algorithm drop-down list, select AES 256.
-
From the Certificate or Asymmetric key drop-down list select
MyCertificate
. -
Select OK.
D. Back up to the Azure Blob storage service
The example below performs a full database backup of
SQLTestDB
to the Azure Blob storage service. This example assumes that you already have a storage account with a blob container. This example creates a shared access signature for you; this example fails of the container has an existing shared access signature.
If you do not have an Azure blob container in a storage account, create one before continuing. For more information, see Create a general purpose storage account and Create a container.
-
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
-
Expand Databases, right-click
SQLTestDB
, point to Tasks, and then click Back Up... -
On the General page in the Destination section select URL from the Back up to: drop-down list.
-
Click Add and the Select Backup Destination dialog box will open.
-
If you have previously registered the Azure storage container that you wish to use with SQL Server Management Studio, select it. Otherwise, click New container to register a new container.
-
In the Connect to a Microsoft Subscription dialog box, sign in to your account.
-
In the Select Storage Account drop-down text box, select your storage account.
-
In the Select Blob Container drop-down text box, select your blob container.
-
In the Shared Access Policy Expiration drop-down calendar box, select an expiration date for the shared access policy that you create in this example.
-
Click Create Credential to generate a shared access signature and credential in SQL Server Management Studio.
-
Click OK close the Connect to a Microsoft Subscription dialog box.
-
In the Backup File text box, modify the name of the backup file (optional).
-
Click OK to close the Select a backup destination dialog box.
-
Click OK to initiate the backup.
-
When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.
Using Transact-SQL
Create a full database backup by executing the
BACKUP DATABASE
statement to create the full database backup, specifying:
- The name of the database to back up.
- The backup device where the full database backup is written.
The basic Transact-SQL syntax for a full database backup is:
BACKUP DATABASE databaseTO backup_device [ ,..n ][ WITH with_options [ ,..o ] ] ;
Option | Description |
---|---|
database | Is the database that is to be backed up. |
backup_device [ ,..n ] | Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. To specify a physical backup device, use the DISK or TAPE option: { DISK | TAPE } =physical_backup_device_name For more information, see Backup Devices (SQL Server). |
WITH with_options [ ,..o ] | Optionally, specifies one or more additional options, o. For information about some of the basic with options, see step 2. |
Optionally, specify one or more WITH options. A few basic WITH options are described here. For information about all the WITH options, see BACKUP (Transact-SQL).
Basic backup set WITH options:
- { COMPRESSION | NO_COMPRESSION }: In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed on this backup, overriding the server-level default.
- ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY): In SQL Server 2014 or later only, specify the encryption algorithm to use, and the Certificate or Asymmetric key to use to secure the encryption.
- DESCRIPTION= { 'text' | @text_variable }: Specifies the free-form text that describes the backup set. The string can have a maximum of 255 characters.
- NAME = { backup_set_name | @backup_set_name_var }: Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.
By default,
BACKUP
appends the backup to an existing media set, preserving existing backup sets. To explicitly specify this, use the NOINIT
option. For information about appending to existing backup sets, see Media Sets, Media Families, and Backup Sets (SQL Server).
Alternatively, to format the backup media, use the FORMAT option:
FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]
Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name and description.
Important
Use extreme caution when you are using the FORMAT clause of the
BACKUP
statement because this destroys any backups that were previously stored on the backup media.
Examples
For the following examples, create a test database with the following Transact-SQL code:
A. Back up to a disk device
The following example backs up the complete
SQLTestDB
database to disk, by using FORMAT
to create a new media set.
B. Back up to a tape device
The following example backs up the complete
SQLTestDB
database to tape, appending the backup to the previous backups.
C. Back up to a logical tape device
The following example creates a logical backup device for a tape drive. The example then backs up the complete SQLTestDB database to that device.
Using PowerShell
Use the Backup-SqlDatabase cmdlet. To explicitly indicate that this is a full database backup, specify the -BackupAction parameter with its default value, Database. This parameter is optional for full database backups.
![Microsoft Microsoft](https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/media/create-a-full-database-backup-sql-server/change-db-location.png?view=sql-server-2017)
Note
These examples require the SqlServer module. To determine if it is installed, run
Get-Module -Name SqlServer
. To install this module, run Install-Module -Name SqlServer
in an administrator session of PowerShell.
For more information, see SQL Server PowerShell Provider.
Important
If you are opening a PowerShell window from within SQL Server Management Studio to connect to an installation of SQL Server, you can omit the credential portion of this example as your credential in SSMS is automatically used to establish the connection between PowerShell and your SQL Server instance.
Examples
A. Full backup (local)
The following example creates a full database backup of the
<myDatabase>
database to the default backup location of the server instance ComputerInstance
. Optionally, this example specifies -BackupAction Database.
For the full syntax and additional examples, see Backup-SqlDatabase.
B. Full backup to Azure
The following example creates a full backup of the database
<myDatabase>
on the <myServer>
instance to the Azure Blob Storage service. A stored access policy has been created with read, write, and list rights. The SQL Server credential, https://<myStorageAccount>.blob.core.windows.net/<myContainer>
, was created using a Shared Access Signature that is associated with the Stored Access Policy. The PowerShell command uses the BackupFile parameter to specify the location (URL) and the backup file name.
Related tasks
See also
-->Syntax
Description
The Backup-SqlDatabase cmdlet performs backup operations on a SQL Server database.
This includes full database backups, transaction log backups, and database file backups.
This cmdlet is modeled after the Microsoft.SqlServer.Management.Smo.Backup class.
The parameters on this class generally correspond to properties on that Smo object.
To back up a database by server instance path and database name, specify the server instance path in the Path parameter and the database name in the Database parameter.
To back up a database using an Smo.Server object and database name, specify the Smo.Server object in the InputObject parameter, either directly or by using the pipeline operator, and the database name in the Database parameter.
To back up a database by server instance and database name, specify the server instance in the ServerInstance parameter and the database name in the Database parameter.
To back up a database using an Smo.Database object, specify the Smo.Database object in the DatabaseObject parameter, either directly or by using the pipeline operator.
By default this cmdlet performs a full database backup.
Set the type of the backup by using the BackupAction parameter.
By default the backup file is stored in the default server backup location under the name databasename.bakfor full and/or file backups and under the name databasename.trn for log backups. To specify a different file name,use the BackupFile parameter.
To specify a backup file location and use an auto-generated file name, specify the location by using theBackupContainer parameter.
Examples
Example 1: Backup a complete database
This command creates a complete database backup of the database named 'MainDB' to the default backup locationof the server instance 'ComputerInstance'. The backup file is named 'MainDB.bak'.
Example 2: Backup a database based on location
This command creates a complete database backup of the database 'MainDB' to the default backup locationof the server instance 'ComputerInstance'. The current working directory is used to determine the serverinstance where the backup occurs.
Example 3: Backup the transaction log
This command creates a backup of the transaction log of the database 'MainDB' to the default backup locationof the server instance 'ComputerInstance'. The backup file is named 'MainDB.trn'.
Example 4: Backup a database and prompt for credentials
This command creates a complete database backup of the database 'MainDB' using the 'sa' SQL Server login.This command prompts for a password to complete the authentication.
Example 5: Backup a database to a network file share
This command creates a complete database backup of the database 'MainDB' to the file 'mainserverdatabasebackupMainDB.bak'.
Example 6: Backup all databases in a server instance
This command backs up all databases on the server instance 'ComputerInstance' to the default backup location.The backup files are named <database name>.bak.
Example 7: Backup all databases in a server instance to a network file share
This command creates a full backup for each database on the server instance 'ComputerInstance' to the share'mainserverdatabasebackup'. The backup files are named <database name>.bak.
Example 8: Backup all files in secondary file groups
This command creates a full file backup of every file in the secondary filegroups 'FileGroupJan' and 'FileGroupFeb'.
Example 9: Create a differential backup
This command creates a differential backup of the database 'MainDB' to the default backup location of the serverinstance 'ComputerInstance'. The backup file is named 'MainDB.bak'.
Example 10: Create a backup to a tape drive
This command creates a full backup of the database MainDB to the tape device '.tape0'.To represent this device, the command constructs an instance of the Microsoft.Sqlserver.Management.Smo.BackupDeviceItemobject. The constructor takes two arguments, the name of the backup device and the type of the backup device.This BackupDeviceItem object is passed to the BackupDevice parameter of the Backup-SqlDatabase cmdlet.
Example 11: Backup a database to the Azure Blob Storage service
This command creates a full backup of the database 'MainDB' to the Windows Azure Blob Storage service.It uses BackupContainer to specify the location (URL) of the Windows Azure Blob container.The name of the backup file is auto-generated.The SqlCredential parameter is used to specify the name of the SQL Server credential that stores theauthentication information.
Example 12: Backup a database to the Azure Blob Storage service and specify the file name
This command creates a full backup of the database MainDB to the Windows Azure Blob Storage service.It uses the BackupFile parameter to specify the location (URL) and the backup file name.The SqlCredential parameter is used to specify the name of the SQL Server credential.
Example 13: Backup all databases to the Azure Blob Storage service
This command backs up all databases on the server instance 'ComputerInstance' to the Windows Azure Blob Storageservice location by using the BackupContainer parameter. The backup file names are auto generated.
Example 14: Create an encrypted backup
This example creates the encryption options and uses it as a parameter value in Backup-SqlDatabase to create anencrypted backup.
Parameters
-BackupAction
Specifies the type of backup operation to perform.Valid values are:
- Database. Backs up all the data files in the database.
- Files. Backs up data files specified in the DatabaseFile or DatabaseFileGroup parameters.
- Log. Backs up the transaction log.
Type: | BackupActionType |
Accepted values: | Database, Files, Log |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the folder or location where the cmdlet stores backups.This can be a folder on a disk or URL for an Azure Blob container.This parameter can be useful when backing up multiple databases in a given instance.This parameter cannot be used with a BackupDevice parameter.The BackupContainer parameter cannot be used with the BackupFile parameter.
The path used to specify the location should end with a forward slash (/).
Type: | String |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the devices where the backups are stored.This parameter cannot be used with the BackupFile parameter.Use this parameter if you are backing up to tape.
Type: | BackupDeviceItem[] |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the location and file name of the backup.This is an optional parameter.If not specified, the backups are stored in the default backup location of the server under the namedatabasename.bak for full and file backups, or databasename.trn for log backups.This parameter cannot be used with the BackupDevice or BackupContainer parameters.
Type: | String[] |
Position: | 2 |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the description of the backup set. This parameter is optional.
Type: | String |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the name of the backup set.
Type: | String |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the physical block size for the backup, 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 for all other devices.
Type: | Int32 |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the number of I/O buffers to use for the backup operation.You can specify any positive integer.If there is insufficient virtual address space in the Sqlservr.exe process for the buffers, you willreceive an out of memory error.
Type: | Int32 |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that a checksum value is calculated during the backup operation.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the compression options for the backup operation.
Type: | BackupCompressionOptions |
Accepted values: | Default, On, Off |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Prompts you for confirmation before running the cmdlet.Prompts you for confirmation before running the cmdlet.
Type: | SwitchParameter |
Aliases: | cf |
Position: | Named |
Default value: | False |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the number of seconds to wait for a server connection before a timeout failure.The timeout value must be an integer between 0 and 65534.If 0 is specified, connection attempts do not timeout.
Type: | Int32 |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that the operation continues when a checksum error occurs.If not set, the operation will fail after a checksum error.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that the backup is a copy-only backup.A copy-only backup does not affect the normal sequence of your regularly scheduled conventional backups.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies a PSCredential object that contains the credentials for a SQL Server login that has permission toperform this operation. This is not the SQL credential object that is used to store authentication informationinternally by SQL Server when accessing resources outside SQL Server.
Type: | PSCredential |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the name of the database to back up.This parameter cannot be used with the DatabaseObject parameter.When this parameter is specified, the Path, InputObject, or ServerInstance parameters must also be specified.
Type: | String |
Position: | 1 |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies one or more database files to back up.This parameter is only used when BackupAction is set to Files.When BackupAction is set to Files, either the DatabaseFileGroups or DatabaseFiles parameter must be specified.
Type: | String[] |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the database file groups targeted by the backup operation.This parameter is only used when BackupAction property is set to Files.When BackupAction parameter is set to Files, either the DatabaseFileGroups or DatabaseFiles parameter must be specified.
Type: | String[] |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the database object for the backup operation.
Type: | Database |
Position: | 1 |
Default value: | None |
Accept pipeline input: | True (ByValue) |
Accept wildcard characters: | False |
Specifies the encryption options for the backup operation.
Type: | BackupEncryptionOptions |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the date and time when the backup set expires and the backup data is no longer considered valid.This can only be used for backup data stored on disk or tape devices.Backup sets older than the expiration date are available to be overwritten by a later backup.
Type: | DateTime |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that the tape is formatted as the first step of the backup operation.This doesnot apply to a disk backup.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that a differential backup is performed.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that devices associated with the backup operation are initialized.This overwrites any existing backup sets on the media and makes this backup the first backup set on the media.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the server object for the backup location.
Type: | Server[] |
Position: | Named |
Default value: | None |
Accept pipeline input: | True (ByValue) |
Accept wildcard characters: | False |
Specifies the truncation behavior for log backups.Valid values are:
-- TruncateOnly-- NoTruncate-- Truncate
The default value is Truncate.
Type: | BackupTruncateLogType |
Accepted values: | TruncateOnly, NoTruncate, Truncate |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the maximum number of bytes to be transferred between the backup media and the instance of SQL Server.The possible values are multiples of 65536 bytes (64 KB), up to 4194304 bytes (4 MB).
Type: | Int32 |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the description for the medium that contains the backup set.This parameter is optional.
Type: | String |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the name used to identify the media set.
Type: | String |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies an array of BackupDeviceList objects used by the mirrored backup.
Type: | BackupDeviceList[] |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that the tail end of the log is not backed up.When restored, the database is in the restoring state.When not set, the tail end of the log is backed up.This only applies when the BackupAction parameter is set to Log.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that a tape drive is left open at the ending position when the backup completes.When not set, the tape is rewound after the operation completes.This does not apply to disk or URL backups.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that the cmdlet outputs the Smo.Backup object that performed the backup.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the path to the instance of SQL Server to execute the backup operation.This is an optional parameter.If not specified, the value of this parameter defaults to the current working location.
Type: | String[] |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that the cmdlet continues processing a partially completed backup operation.If not set, the cmdlet restarts an interrupted backup operation at the beginning of the backup set.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the number of days that must elapse before a backup set can be overwritten.This can only be used for backup data stored on disk or tape devices.
Type: | Int32 |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that this cmdlet outputs a Transact-SQL script that performs the backup operation.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the name of a SQL Server instance.This server instance becomes the target of the backup operation.
Type: | String[] |
Position: | Named |
Default value: | None |
Accept pipeline input: | True (ByPropertyName, ByValue) |
Accept wildcard characters: | False |
Indicates that the tape header is not read.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies an SQL Server credential object that stores authentication information.If you are backing up to Blob storage service, you must specify this parameter.The authentication information stored includes the Storage account name and the associated access key values.Do not specify this parameter for disk or tape.
Type: | PSObject |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Set the timeout (in seconds) for the back-up operation.
If the value is 0 or the StatementTimeout parameter is not specified, the restore operation is not going to timeout.
Type: | Int32 |
Position: | Named |
Default value: | 0 |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Specifies the name of the undo file used to store uncommitted transactions that are rolled back during recovery.
Type: | String |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Indicates that the tape device is rewound and unloaded when the operation finishes.If not set, no attempt is made to rewind and unload the tape medium.This does not apply to disk or URL backups.
Type: | SwitchParameter |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Shows what would happen if the cmdlet runs.The cmdlet is not run.Shows what would happen if the cmdlet runs.The cmdlet is not run.
Type: | SwitchParameter |
Aliases: | wi |
Position: | Named |
Default value: | False |
Accept pipeline input: | False |
Accept wildcard characters: | False |
Inputs
SMO.Server
Microsoft.SqlServer.Management.Smo.Database
Microsoft.SqlServer.Management.Smo.Server[]
Specifies an SMO.Server object referring to the instance of SQL Server on which the backup operation occurs.
System.String[]
Outputs
System.Object