SQL Server 使用 Powershell 备份数据库

----------------------------------------------------------------------------------------
--	SQL Server 2008 & 2008 r2
----------------------------------------------------------------------------------------
$instance = 'ServerName'
$database = 'dbName'
$backupDir = 'E:\backup'
$datestr = Get-Date -format yyyyMMddHHmmss
$backupFile = $backupDir + '\' + $database + '_' + $datestr + '.bak'

[system.reflection.assembly]::loadwithpartialname('microsoft.sqlserver.smo')
[system.reflection.assembly]::loadwithpartialname('microsoft.sqlserver.smoextended')
$svr = new-object Microsoft.SqlServer.Management.smo.server($instance)
$bk  = new-object Microsoft.SqlServer.Management.smo.backup
$bk.Action = [Microsoft.SqlServer.Management.smo.BackupActionType]::Database
$bk.Database = $database
$bk.Incremental = $FALSE
$bk.Checksum = $TRUE
$bk.CompressionOption = 1
$bk.Devices.AddDevice($backupFile,'File')
$bk.SqlBackup($svr)

#因不能直接备份到网络路径,可先备份到本地再拷贝到远程
$copyDir = '\\192.168.1.99\Backup'
Copy-Item $backupFile -Destination $copyDir
Remove-Item $backupFile

----------------------------------------------------------------------------------------
--	SQL Server 2012 及后续版本
----------------------------------------------------------------------------------------
# Get-ExecutionPolicy -List
# Set-ExecutionPolicy Unrestricted -Scope CurrentUser
# -CompressionOption [Default / On / Off] (是否压缩)
# -BackupAction [Database / Log / Files] (备份类型)
# -Incremental (差异备份)

import-module sqlps;
$instance = 'ServerName'
$database = 'dbName'
$targetDir = 'E:\backup'
$datestr = Get-Date -format yyyyMMddHHmmss
$backupFile = $targetDir + '\' + $database + '_' + $datestr + '.bak'

Backup-SqlDatabase -ServerInstance $instance -Database $database -CompressionOption On -Checksum -BackupFile $backupFile

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------