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>]
    [-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]
    [-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>]
    [-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]
    [-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>]
    [-SqlCredential <PSObject>]
    [-BackupDevice <BackupDeviceItem[]>]
    [-PassThru]
    [-Checksum]
    [-ContinueAfterError]
    [-NoRewind]
    [-Restart]
    [-UnloadTapeAfter]
    [-NoRecovery]
    [-DatabaseFile <String[]>]
    [-DatabaseFileGroup <String[]>]
    [-BlockSize <Int32>]
    [-BufferCount <Int32>]
    [-MaxTransferSize <Int32>]
    [-MediaName <String>]
    [-Script]
    [-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>]
    [-SqlCredential <PSObject>]
    [-BackupDevice <BackupDeviceItem[]>]
    [-PassThru]
    [-Checksum]
    [-ContinueAfterError]
    [-NoRewind]
    [-Restart]
    [-UnloadTapeAfter]
    [-NoRecovery]
    [-DatabaseFile <String[]>]
    [-DatabaseFileGroup <String[]>]
    [-BlockSize <Int32>]
    [-BufferCount <Int32>]
    [-MaxTransferSize <Int32>]
    [-MediaName <String>]
    [-Script]
    [-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>]
    [-SqlCredential <PSObject>]
    [-BackupDevice <BackupDeviceItem[]>]
    [-PassThru]
    [-Checksum]
    [-ContinueAfterError]
    [-NoRewind]
    [-Restart]
    [-UnloadTapeAfter]
    [-NoRecovery]
    [-DatabaseFile <String[]>]
    [-DatabaseFileGroup <String[]>]
    [-BlockSize <Int32>]
    [-BufferCount <Int32>]
    [-MaxTransferSize <Int32>]
    [-MediaName <String>]
    [-Script]
    [-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 you 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.