Backup-SqlDatabase

Backup-SqlDatabase
    [-Database] <String>
    [[-BackupFile] <String[]>]
    [-BackupContainer <String>]
    [-MirrorDevices <BackupDeviceList[]>]
    [-BackupAction <BackupActionType>]
    [-BackupSetName <String>]
    [-BackupSetDescription <String>]
    [-CompressionOption <BackupCompressionOptions>]
    [-CopyOnly]
    [-ExpirationDate <DateTime>]
    [-FormatMedia]
    [-Incremental]
    [-Initialize]
    [-LogTruncationType <BackupTruncateLogType>]
    [-MediaDescription <String>]
    [-RetainDays <Int32>]
    [-SkipTapeHeader]
    [-UndoFileName <String>]
    [-EncryptionOption <BackupEncryptionOptions>]
    [-StatementTimeout <Int32>]
    [-Path <String[]>]
    [-SqlCredential <PSObject>]
    [-BackupDevice <BackupDeviceItem[]>]
    [-PassThru]
    [-Checksum]
    [-ContinueAfterError]
    [-NoRewind]
    [-Restart]
    [-UnloadTapeAfter]
    [-NoRecovery]
    [-DatabaseFile <String[]>]
    [-DatabaseFileGroup <String[]>]
    [-BlockSize <Int32>]
    [-BufferCount <Int32>]
    [-MaxTransferSize <Int32>]
    [-MediaName <String>]
    [-Script]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Backup-SqlDatabase
    [-DatabaseObject] <Database>
    [[-BackupFile] <String[]>]
    [-BackupContainer <String>]
    [-MirrorDevices <BackupDeviceList[]>]
    [-BackupAction <BackupActionType>]
    [-BackupSetName <String>]
    [-BackupSetDescription <String>]
    [-CompressionOption <BackupCompressionOptions>]
    [-CopyOnly]
    [-ExpirationDate <DateTime>]
    [-FormatMedia]
    [-Incremental]
    [-Initialize]
    [-LogTruncationType <BackupTruncateLogType>]
    [-MediaDescription <String>]
    [-RetainDays <Int32>]
    [-SkipTapeHeader]
    [-UndoFileName <String>]
    [-EncryptionOption <BackupEncryptionOptions>]
    [-StatementTimeout <Int32>]
    [-SqlCredential <PSObject>]
    [-BackupDevice <BackupDeviceItem[]>]
    [-PassThru]
    [-Checksum]
    [-ContinueAfterError]
    [-NoRewind]
    [-Restart]
    [-UnloadTapeAfter]
    [-NoRecovery]
    [-DatabaseFile <String[]>]
    [-DatabaseFileGroup <String[]>]
    [-BlockSize <Int32>]
    [-BufferCount <Int32>]
    [-MaxTransferSize <Int32>]
    [-MediaName <String>]
    [-Script]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Backup-SqlDatabase
    [-Database] <String>
    [[-BackupFile] <String[]>]
    -ServerInstance <String[]>
    [-BackupContainer <String>]
    [-MirrorDevices <BackupDeviceList[]>]
    [-BackupAction <BackupActionType>]
    [-BackupSetName <String>]
    [-BackupSetDescription <String>]
    [-CompressionOption <BackupCompressionOptions>]
    [-CopyOnly]
    [-ExpirationDate <DateTime>]
    [-FormatMedia]
    [-Incremental]
    [-Initialize]
    [-LogTruncationType <BackupTruncateLogType>]
    [-MediaDescription <String>]
    [-RetainDays <Int32>]
    [-SkipTapeHeader]
    [-UndoFileName <String>]
    [-EncryptionOption <BackupEncryptionOptions>]
    [-StatementTimeout <Int32>]
    [-Credential <PSCredential>]
    [-ConnectionTimeout <Int32>]
    [-SqlCredential <PSObject>]
    [-BackupDevice <BackupDeviceItem[]>]
    [-PassThru]
    [-Checksum]
    [-ContinueAfterError]
    [-NoRewind]
    [-Restart]
    [-UnloadTapeAfter]
    [-NoRecovery]
    [-DatabaseFile <String[]>]
    [-DatabaseFileGroup <String[]>]
    [-BlockSize <Int32>]
    [-BufferCount <Int32>]
    [-MaxTransferSize <Int32>]
    [-MediaName <String>]
    [-Script]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Backup-SqlDatabase
    [-Database] <String>
    [[-BackupFile] <String[]>]
    -InputObject <Server[]>
    [-BackupContainer <String>]
    [-MirrorDevices <BackupDeviceList[]>]
    [-BackupAction <BackupActionType>]
    [-BackupSetName <String>]
    [-BackupSetDescription <String>]
    [-CompressionOption <BackupCompressionOptions>]
    [-CopyOnly]
    [-ExpirationDate <DateTime>]
    [-FormatMedia]
    [-Incremental]
    [-Initialize]
    [-LogTruncationType <BackupTruncateLogType>]
    [-MediaDescription <String>]
    [-RetainDays <Int32>]
    [-SkipTapeHeader]
    [-UndoFileName <String>]
    [-EncryptionOption <BackupEncryptionOptions>]
    [-StatementTimeout <Int32>]
    [-SqlCredential <PSObject>]
    [-BackupDevice <BackupDeviceItem[]>]
    [-PassThru]
    [-Checksum]
    [-ContinueAfterError]
    [-NoRewind]
    [-Restart]
    [-UnloadTapeAfter]
    [-NoRecovery]
    [-DatabaseFile <String[]>]
    [-DatabaseFileGroup <String[]>]
    [-BlockSize <Int32>]
    [-BufferCount <Int32>]
    [-MaxTransferSize <Int32>]
    [-MediaName <String>]
    [-Script]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Backup-SqlDatabase
    [[-BackupFile] <String[]>]
    [-BackupContainer <String>]
    [-MirrorDevices <BackupDeviceList[]>]
    [-BackupAction <BackupActionType>]
    [-BackupSetName <String>]
    [-BackupSetDescription <String>]
    [-CompressionOption <BackupCompressionOptions>]
    [-CopyOnly]
    [-ExpirationDate <DateTime>]
    [-FormatMedia]
    [-Incremental]
    [-Initialize]
    [-LogTruncationType <BackupTruncateLogType>]
    [-MediaDescription <String>]
    [-RetainDays <Int32>]
    [-SkipTapeHeader]
    [-UndoFileName <String>]
    [-EncryptionOption <BackupEncryptionOptions>]
    [-StatementTimeout <Int32>]
    [-SqlCredential <PSObject>]
    [-BackupDevice <BackupDeviceItem[]>]
    [-PassThru]
    [-Checksum]
    [-ContinueAfterError]
    [-NoRewind]
    [-Restart]
    [-UnloadTapeAfter]
    [-NoRecovery]
    [-DatabaseFile <String[]>]
    [-DatabaseFileGroup <String[]>]
    [-BlockSize <Int32>]
    [-BufferCount <Int32>]
    [-MaxTransferSize <Int32>]
    [-MediaName <String>]
    [-Script]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [-WhatIf]
    [-Confirm]
    [<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.

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.bak for 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 the BackupContainer parameter.

PS C:\> Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB"

This command creates a complete database backup of the database named 'MainDB' to the default backup location of the server instance 'Computer\Instance'. The backup file is named 'MainDB.bak'.

PS C:\> Set-Location "SQLSERVER:\SQL\Computer\Instance"
PS SQLSERVER:\SQL\Computer\Instance> Backup-SqlDatabase -Database "MainDB"

This command creates a complete database backup of the database 'MainDB' to the default backup location of the server instance 'Computer\Instance'. The current working directory is used to determine the server instance where the backup occurs.

PS C:\> Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupAction Log

This command creates a backup of the transaction log of the database 'MainDB' to the default backup location of the server instance 'Computer\Instance'. The backup file is named 'MainDB.trn'.

PS C:\> Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -Credential (Get-Credential "sa")

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.

PS C:\> Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "\\mainserver\databasebackup\MainDB.bak"

This command creates a complete database backup of the database 'MainDB' to the file "\\mainserver\databasebackup\MainDB.bak".

PS C:\> Get-ChildItem "SQLSERVER:\SQL\Computer\Instance\Databases" | Backup-SqlDatabase

This command backs up all databases on the server instance 'Computer\Instance' to the default backup location. The backup files are named <database name>.bak.

PS C:\> Set-Location "SQLSERVER:\SQL\Computer\Instance\Databases"
PS SQLSERVER:\SQL\Computer\Instance\Databases> foreach ($database in (Get-ChildItem)) {
     $dbName = $database.Name
     Backup-SqlDatabase -Database $dbName -BackupFile "\\mainserver\databasebackup\$dbName.bak" }

This command creates a full backup for each database on the server instance 'Computer\Instance' to the share "\\mainserver\databasebackup". The backup files are named <database name>.bak.

PS C:\> Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupAction Files -DatabaseFileGroup "FileGroupJan","FileGroupFeb"

This command creates a full file backup of every file in the secondary filegroups 'FileGroupJan' and 'FileGroupFeb'.

PS C:\> Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -Incremental

This command creates a differential backup of the database 'MainDB' to the default backup location of the server instance 'Computer\Instance'. The backup file is named "MainDB.bak".

PS C:\> $TapeDevice = New-Object Microsoft.Sqlserver.Management.Smo.BackupDeviceItem("\\.\tape0", "Tape")
PS C:\> Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupDevice $TapeDevice

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.BackupDeviceItem object. 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.

PS C:\> Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupContainer "https://storageaccountname.blob.core.windows.net/containername" -SqlCredential "SQLCredentialName"

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 the authentication information.

PS C:\> Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainyDB" -BackupFile "https://storageaccountname.blob.core.windows.net/containername/MainDB.bak" -SqlCredential "SQLCredentialName"

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.

PS C:\> Get-ChildItem "SQLSERVER:\SQL\Computer\Instance\Databases" | Backup-SqlDatabase -BackupContainer "https://storageaccountname.blob.core.windows.net/containername" -SqlCredential "SQLCredentialName"

This command backs up all databases on the server instance 'Computer\Instance' to the Windows Azure Blob Storage service location by using the BackupContainer parameter. The backup file names are auto generated.

PS C:\> $EncryptionOption = New-SqlBackupEncryptionOption -Algorithm Aes256 -EncryptorType ServerCertificate -EncryptorName "BackupCert"
PS C:\> Backup-SqlDatabase -ServerInstance "." -Database "MainDB" -BackupFile "MainDB.bak" -CompressionOption On -EncryptionOption $EncryptionOption

This example creates the encryption options and uses it as a parameter value in Backup-SqlDatabase to create an encrypted backup.

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutBuffer, -OutVariable, -PipelineVariable, -ProgressAction, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.

Specifies an SMO.Server object referring to the instance of SQL Server on which the backup operation occurs.