Backup-SqlDatabase

The Backup-SqlDatabase cmdlet performs backup operations on a SQL Server database.

Backup-SqlDatabase [-Database*] <string> [[-BackupFile] <string[]>] [-BackupAction <BackupActionType>][-BackupDevice <BackupDeviceItem[]>] [-BackupSetDescription <string>] [-BackupSetName <string>] [-BlockSize <int>][-BufferCount <int>] [-Checksum] [-CompressionOption <BackupCompressionOptions>] [-ContinueAfterError] [-CopyOnly][-DatabaseFile <string[]>] [-DatabaseFileGroup <string[]>] [-ExpirationDate <DateTime>] [-FormatMedia][-Incremental] [-Initialize] [-LogTruncationType <BackupTruncateLogType>] [-MaxTransferSize <int>][-MediaDescription <string>] [-MediaName <string>] [-MirrorDevices <BackupDeviceList[]>] [-NoRecovery] [-NoRewind][-Passthru] [-Path <string[]>] [-Restart] [-RetainDays <int>] [-Script] [-SkipTapeHeader] [-UndoFileName <string>][-UnloadTapeAfter] [-Confirm] [-WhatIf] [<CommonParameters>]

Backup-SqlDatabase [-Database*Object] <Database> [[-BackupFile] <string[]>] [-BackupAction <BackupActionType>][-BackupDevice <BackupDeviceItem[]>] [-BackupSetDescription <string>] [-BackupSetName <string>] [-BlockSize <int>][-BufferCount <int>] [-Checksum] [-CompressionOption <BackupCompressionOptions>] [-ContinueAfterError] [-CopyOnly][-DatabaseFile <string[]>] [-DatabaseFileGroup <string[]>] [-ExpirationDate <DateTime>] [-FormatMedia][-Incremental] [-Initialize] [-LogTruncationType <BackupTruncateLogType>] [-MaxTransferSize <int>][-MediaDescription <string>] [-MediaName <string>] [-MirrorDevices <BackupDeviceList[]>] [-NoRecovery] [-NoRewind][-Passthru] [-Restart] [-RetainDays <int>] [-Script] [-SkipTapeHeader] [-UndoFileName <string>] [-UnloadTapeAfter][-Confirm] [-WhatIf] [<CommonParameters>]

Backup-SqlDatabase [-Database*] <string> [[-BackupFile] <string[]>] -InputObject* <Server> [-BackupAction<BackupActionType>] [-BackupDevice <BackupDeviceItem[]>] [-BackupSetDescription <string>] [-BackupSetName<string>] [-BlockSize <int>] [-BufferCount <int>] [-Checksum] [-CompressionOption <BackupCompressionOptions>][-ContinueAfterError] [-CopyOnly] [-DatabaseFile <string[]>] [-DatabaseFileGroup <string[]>] [-ExpirationDate<DateTime>] [-FormatMedia] [-Incremental] [-Initialize] [-LogTruncationType <BackupTruncateLogType>][-MaxTransferSize <int>] [-MediaDescription <string>] [-MediaName <string>] [-MirrorDevices <BackupDeviceList[]>][-NoRecovery] [-NoRewind] [-Passthru] [-Restart] [-RetainDays <int>] [-Script] [-SkipTapeHeader] [-UndoFileName<string>] [-UnloadTapeAfter] [-Confirm] [-WhatIf] [<CommonParameters>]

Backup-SqlDatabase [-Database*] <string> [[-BackupFile] <string[]>] -ServerInstance* <string[]> [-BackupAction<BackupActionType>] [-BackupDevice <BackupDeviceItem[]>] [-BackupSetDescription <string>] [-BackupSetName<string>] [-BlockSize <int>] [-BufferCount <int>] [-Checksum] [-CompressionOption <BackupCompressionOptions>][-ConnectionTimeout <int>] [-ContinueAfterError] [-CopyOnly] [-Credential <PSCredential>] [-DatabaseFile<string[]>] [-DatabaseFileGroup <string[]>] [-ExpirationDate <DateTime>] [-FormatMedia] [-Incremental][-Initialize] [-LogTruncationType <BackupTruncateLogType>] [-MaxTransferSize <int>] [-MediaDescription <string>][-MediaName <string>] [-MirrorDevices <BackupDeviceList[]>] [-NoRecovery] [-NoRewind] [-Passthru] [-Restart][-RetainDays <int>] [-Script] [-SkipTapeHeader] [-UndoFileName <string>] [-UnloadTapeAfter] [-Confirm] [-WhatIf][<CommonParameters>]

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.

This cmdlet supports four modes of operation to backup the database with the specified name on a server instance.1. Pass the path of a server instance to the -Path parameter and a database name to the -Database parameter.2. Pass a Smo.Server object to the -InputObject parameter, either directly or through the pipeline, and a database name to the -Database parameter.3. Pass the name of a server instance to the -ServerInstance parameter, and a database name to the -Database parameter.4. Pass a Smo.Database object to the -DatabaseObject parameter, either directly or through the pipeline.

By default the cmdlet performs a full database backup. You can set the type of the backup by specifying the -BackupAction parameter.

By default the backup file is stored in the default server backup location under the name “databasename.bak” for full and/or file backups and under “databasename.trn” for log backups. You can set the location of the backup file by specifying the -BackupFile parameter.

Parameters
`-BackupAction <BackupActionType>` Default value is Microsoft.SqlServer.Management.Smo.BackupActionType.Database The type of backup operation to be performed. Valid options are: Database, Files, or Log, as follows: Database: Backs up all the data files in the database. Files: Backs up specified data files. This requires the DatabaseFile or DatabaseFileGroup parameter to be set. Log: Backs up the transaction log.
`-BackupDevice <BackupDeviceItem[]>` 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 some non-disk medium such as tape.
`-BackupFile <string[]>` The location or locations on disk where the backups are stored. This is an optional parameter. If not specified, the backups are stored in the default backup location of the server under the name “databasename.bak” for full and file backups, or “databasename.trn” for log backups. This parameter cannot be used with the BackupDevice parameter.
`-BackupSetDescription <string>` The optional description of the backup set.
`-BackupSetName <string>` The name of the backup set.
`-BlockSize <int>` 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 otherwise.
`-BufferCount <int>` The total number of I/O buffers to be used for the backup operation. You can specify any positive integer. Using many buffers might cause “out of memory” errors because of insufficient virtual address space in the Sqlservr.exe process.
`-Checksum [<SwitchParameter>]` Calculates a checksum value during the backup operation. If not set, no checksum is calculated.
`-CompressionOption <BackupCompressionOptions>` Default value is Microsoft.SqlServer.Management.Smo.BackupCompressionOptions.Default The compression options for the backup operation. The options are Default, On, and Off.
`-ConnectionTimeout <int>` The number of seconds to wait for a server connection before a time-out failure. The time-out value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.
`-ContinueAfterError [<SwitchParameter>]` The operation continues when a checksum error occurs. If not set, the operation will fail after a checksum error.
`-CopyOnly [<SwitchParameter>]` The backup is a copy-only backup. A copy-only backup does not affect the normal sequence of your regularly scheduled, conventional backups.
`-Credential <PSCredential>` A PSCredential object specifying the credentials for a SQL Server login that has permission to perform this operation.
`-Database <string>` This value is required The name of the database to be backed up. -Database cannot be used with the -DatabaseObject parameter. When the -Database parameter is specified, the -Path, -InputObject, or -ServerInstance parameter must also be specified.
`-DatabaseFile <string[]>` One or more database files to be backed up. Only used when the BackupAction property is set to Files. When the BackupAction parameter is set to Files, either the DatabaseFileGroups property or the DatabaseFiles property must be specified.
`-DatabaseFileGroup <string[]>` The database file groups targeted by the backup operation. Only used when the BackupAction property is set to Files. When the BackupAction parameter is set to Files, either the DatabaseFileGroups property or the DatabaseFiles property must be specified.
`-DatabaseObject <Database>` This value is required Accepts pipeline input ByValue A database object. The backup operation will be performed on this database.
`-ExpirationDate <DateTime>` Default value is System.DateTime.MinValue The date and time when the backup set expires and the backup data is no longer considered valid. Can only be used for backup data stored on disk or tape devices. Backup sets older than the expiration date can be overwritten by a later backup.
`-FormatMedia [<SwitchParameter>]` Formats the tape as the first step of the backup operation. Does not apply to a disk backup.
`-Incremental [<SwitchParameter>]` Performs a differential backup.
`-Initialize [<SwitchParameter>]` Devices associated with the backup operation are initialized. Overwrites any existing backup sets on the media and this backup becomes the first backup set on the media.
`-InputObject <Server>` This value is required Accepts pipeline input ByValue The Server object of the location where the backup should occur.
`-LogTruncationType <BackupTruncateLogType>` Default value is Microsoft.SqlServer.Management.Smo.BackupTruncateLogType.Truncate The truncation behavior for log backups. Options are TruncateOnly, NoTruncate, or Truncate. By default, this option is set to Truncate.
`-MaxTransferSize <int>` 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).
`-MediaDescription <string>` The optional description for the medium that contains the backup set.
`-MediaName <string>` The name used to identify the media set.
`-MirrorDevices <BackupDeviceList[]>` Specifies an array of BackupDeviceList objects used by the mirrored backup.
`-NoRecovery [<SwitchParameter>]` Does not backup the tail end of the log. When restored, the database is in the restoring state. When not set, the tail end of the log is backed up. Only applies when the -BackupAction parameter is set to Log.
`-NoRewind [<SwitchParameter>]` Leaves a tape drive open at the ending position when the backup completes. When not set, the tape is rewound after the operation completes. Does not apply to disk backups.
`-Passthru [<SwitchParameter>]` When set, outputs the Smo Backup object that performed the backup.
`-Path <string[]>` The path to the instance of SQL Server on which you want to execute the backup operation. This is an optional parameter. If not specified, the value of this parameter defaults to the current working location.
`-Restart [<SwitchParameter>]` Continues processing a partially completed backup operation. If not set (default), the cmdlet restarts an interrupted backup operation at the beginning of the backup set.
`-RetainDays <int>` Default value is -1 The number of days that must elapse before a backup set can be overwritten.
`-Script [<SwitchParameter>]` Outputs a Transact-SQL script that performs the task executed by this cmdlet.
`-ServerInstance <string[]>` This value is required Accepts pipeline input ByValue The name of an instance of SQL Server. This server instance becomes the target of the backup operation.
`-SkipTapeHeader [<SwitchParameter>]` Skips reading the tape header.
`-UndoFileName <string>` The name of the undo file used to store uncommitted transactions that are rolled back during recovery.
`-UnloadTapeAfter [<SwitchParameter>]` The tape device is rewound and unloaded when the operation completes. If not set, no attempt is made to rewind and unload the tape medium. Does not apply to disk backups.
`-Confirm [<SwitchParameter>]` Prompts you for confirmation before executing the command.
`-WhatIf [<SwitchParameter>]` Describes what would happen if you executed the command without actually executing the command.
`<CommonParameters>` This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Parameters

-BackupAction <BackupActionType>

Default value is Microsoft.SqlServer.Management.Smo.BackupActionType.Database

The type of backup operation to be performed. Valid options are: Database, Files, or Log, as follows:

Database: Backs up all the data files in the database. Files: Backs up specified data files. This requires the DatabaseFile or DatabaseFileGroup parameter to be set.

Log: Backs up the transaction log.

-BackupDevice <BackupDeviceItem[]>

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 some non-disk medium such as tape.

-BackupFile <string[]>

The location or locations on disk where the backups are stored. This is an optional parameter. If not specified, the backups are stored in the default backup location of the server under the name “databasename.bak” for full and file backups, or “databasename.trn” for log backups. This parameter cannot be used with the BackupDevice parameter.

-BackupSetDescription <string>

The optional description of the backup set.

-BackupSetName <string>

The name of the backup set.

-BlockSize <int>

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 otherwise.

-BufferCount <int>

The total number of I/O buffers to be used for the backup operation. You can specify any positive integer. Using many buffers might cause “out of memory” errors because of insufficient virtual address space in the Sqlservr.exe process.

-Checksum [<SwitchParameter>]

Calculates a checksum value during the backup operation. If not set, no checksum is calculated.

-CompressionOption <BackupCompressionOptions>

Default value is Microsoft.SqlServer.Management.Smo.BackupCompressionOptions.Default

The compression options for the backup operation. The options are Default, On, and Off.

-ConnectionTimeout <int>

The number of seconds to wait for a server connection before a time-out failure. The time-out value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.

-ContinueAfterError [<SwitchParameter>]

The operation continues when a checksum error occurs. If not set, the operation will fail after a checksum error.

-CopyOnly [<SwitchParameter>]

The backup is a copy-only backup. A copy-only backup does not affect the normal sequence of your regularly scheduled, conventional backups.

-Credential <PSCredential>

A PSCredential object specifying the credentials for a SQL Server login that has permission to perform this operation.

-Database <string>

This value is required

The name of the database to be backed up. -Database cannot be used with the -DatabaseObject parameter. When the -Database parameter is specified, the -Path, -InputObject, or -ServerInstance parameter must also be specified.

-DatabaseFile <string[]>

One or more database files to be backed up. Only used when the BackupAction property is set to Files. When the BackupAction parameter is set to Files, either the DatabaseFileGroups property or the DatabaseFiles property must be specified.

-DatabaseFileGroup <string[]>

The database file groups targeted by the backup operation. Only used when the BackupAction property is set to Files. When the BackupAction parameter is set to Files, either the DatabaseFileGroups property or the DatabaseFiles property must be specified.

-DatabaseObject <Database>

This value is required
Accepts pipeline input ByValue

A database object. The backup operation will be performed on this database.

-ExpirationDate <DateTime>

Default value is System.DateTime.MinValue

The date and time when the backup set expires and the backup data is no longer considered valid. Can only be used for backup data stored on disk or tape devices. Backup sets older than the expiration date can be overwritten by a later backup.

-FormatMedia [<SwitchParameter>]

Formats the tape as the first step of the backup operation. Does not apply to a disk backup.

-Incremental [<SwitchParameter>]

Performs a differential backup.

-Initialize [<SwitchParameter>]

Devices associated with the backup operation are initialized. Overwrites any existing backup sets on the media and this backup becomes the first backup set on the media.

-InputObject <Server>

This value is required
Accepts pipeline input ByValue

The Server object of the location where the backup should occur.

-LogTruncationType <BackupTruncateLogType>

Default value is Microsoft.SqlServer.Management.Smo.BackupTruncateLogType.Truncate

The truncation behavior for log backups. Options are TruncateOnly, NoTruncate, or Truncate. By default, this option is set to Truncate.

-MaxTransferSize <int>

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

-MediaDescription <string>

The optional description for the medium that contains the backup set.

-MediaName <string>

The name used to identify the media set.

-MirrorDevices <BackupDeviceList[]>

Specifies an array of BackupDeviceList objects used by the mirrored backup.

-NoRecovery [<SwitchParameter>]

Does not backup the tail end of the log. When restored, the database is in the restoring state. When not set, the tail end of the log is backed up. Only applies when the -BackupAction parameter is set to Log.

-NoRewind [<SwitchParameter>]

Leaves a tape drive open at the ending position when the backup completes. When not set, the tape is rewound after the operation completes. Does not apply to disk backups.

-Passthru [<SwitchParameter>]

When set, outputs the Smo Backup object that performed the backup.

-Path <string[]>

The path to the instance of SQL Server on which you want to execute the backup operation. This is an optional parameter. If not specified, the value of this parameter defaults to the current working location.

-Restart [<SwitchParameter>]

Continues processing a partially completed backup operation. If not set (default), the cmdlet restarts an interrupted backup operation at the beginning of the backup set.

-RetainDays <int>

Default value is -1

The number of days that must elapse before a backup set can be overwritten.

-Script [<SwitchParameter>]

Outputs a Transact-SQL script that performs the task executed by this cmdlet.

-ServerInstance <string[]>

This value is required
Accepts pipeline input ByValue

The name of an instance of SQL Server. This server instance becomes the target of the backup operation.

-SkipTapeHeader [<SwitchParameter>]

Skips reading the tape header.

-UndoFileName <string>

The name of the undo file used to store uncommitted transactions that are rolled back during recovery.

-UnloadTapeAfter [<SwitchParameter>]

The tape device is rewound and unloaded when the operation completes. If not set, no attempt is made to rewind and unload the tape medium. Does not apply to disk backups.

-Confirm [<SwitchParameter>]

Prompts you for confirmation before executing the command.

-WhatIf [<SwitchParameter>]

Describes what would happen if you executed the command without actually executing the command.

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Inputs

SMO.Server

A SMO.Server object referring to the instance of SQL Server on which the backup operation should occur.

Examples

This command creates a complete database backup of the database ‘MyDB’ to the default backup location of the server instance ComputerInstance’:
```
C:PS> Backup-SqlDatabase -ServerInstance ComputerInstance -Database MyDB
```
The backup file will be named ‘MyDB.bak’.
This command creates a complete database backup of the database ‘MyDB’ to the default backup location of the server instance ‘ComputerInstance’:
```
C:PS> cd SQLSERVER:SQLComputerInstance
Backup-SqlDatabase -Database MyDB
```
The current working directory is used to determine the server instance where the backup should occur.
This command creates a backup of the transaction log of the database ‘MyDB’ to the default backup location of the server instance ‘ComputerInstance’:
```
C:PS> Backup-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupAction Log
```
The backup file are named ‘MyDB.trn’.
This command creates a complete database backup of the database ‘MyDB’, using the sa SQL Server credential:
```
C:PS> Backup-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -Credential (Get-Credential sa)
```
This command will prompt you for a password to complete SQL Server authentication.
This command creates a complete database backup of the database ‘MyDB’ to the file ‘\sharefolderMyDB.bak’:
```
C:PS> Backup-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupFile \sharefolderMyDB.bak
```
This command backs up all databases on the server instance ‘ComputerInstance’ to the default backup location:
```
C:PS> Get-ChildItem SQLSERVER:SQLComputerInstanceDatabases | Backup-SqlDatabase
```
The backup files are named ‘.bak’.

This script creates a full backup for each database on the server instance ‘ComputerInstance’ to the share ‘\sharefolder’:

C:PS> cd SQLSERVER:SQLComputerInstanceDatabases
foreach($database in (Get-ChildItem))
{

       $dbName = $database.Name
       Backup-SqlDatabase -Database $dbName -BackupFile "\sharefolder$dbName.bak" 
   }

The backup files are named ‘.bak’.

This command creates a full file backup of every file in the secondary filegroups ‘FileGroup1’ and ‘FileGroup2’:

C:PS> Backup-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupAction Files -DatabaseFileGroup "FileGroup1","FileGroup2"

This command creates a differential backup of the database ‘MyDB’ to the default backup location of the server instance ‘ComputerInstance’:
```
C:PS> Backup-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -Incremental
```
The backup file are named ‘MyDB.bak’.
This command creates a full backup of the database ‘MyDB’ to the tape device ‘\.tape0’:
```
C:PS> $tapeDevice = New-Object Microsoft.Sqlserver.Management.Smo.BackupDeviceItem("\.tape0", "Tape")
Backup-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupDevice $tapeDevice
```
To represent this device, the command constructs an instance of the Microsoft.Sqlserver.Management.Smo.BackupDeviceItem. The constructor takes two arguments: the name of the backup device (‘\.tape0’) and the type of the backup device (‘Tape’). This BackupDeviceItem object is passed to the -BackupDevice parameter of the Backup-SqlDatabase cmdlet.

Additional Notes

Related Links