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>
|
-BackupDevice <BackupDeviceItem[]>
|
-BackupFile <string[]>
|
-BackupSetDescription <string>
|
-BackupSetName <string>
|
-BlockSize <int>
|
-BufferCount <int>
|
-Checksum [<SwitchParameter>]
|
-CompressionOption <BackupCompressionOptions>
|
-ConnectionTimeout <int>
|
-ContinueAfterError [<SwitchParameter>]
|
-CopyOnly [<SwitchParameter>]
|
-Credential <PSCredential>
|
-Database <string>
|
-DatabaseFile <string[]>
|
-DatabaseFileGroup <string[]>
|
-DatabaseObject <Database>
|
-ExpirationDate <DateTime>
|
-FormatMedia [<SwitchParameter>]
|
-Incremental [<SwitchParameter>]
|
-Initialize [<SwitchParameter>]
|
-InputObject <Server>
|
-LogTruncationType <BackupTruncateLogType>
|
-MaxTransferSize <int>
|
-MediaDescription <string>
|
-MediaName <string>
|
-MirrorDevices <BackupDeviceList[]>
|
-NoRecovery [<SwitchParameter>]
|
-NoRewind [<SwitchParameter>]
|
-Passthru [<SwitchParameter>]
|
-Path <string[]>
|
-Restart [<SwitchParameter>]
|
-RetainDays <int>
|
-Script [<SwitchParameter>]
|
-ServerInstance <string[]>
|
-SkipTapeHeader [<SwitchParameter>]
|
-UndoFileName <string>
|
-UnloadTapeAfter [<SwitchParameter>]
|
-Confirm [<SwitchParameter>]
|
-WhatIf [<SwitchParameter>]
|
<CommonParameters>
|
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