<#=====================================================================#>##备份数据库(完整备份)V2.0 Andy 2017-4-13 增加了设置压缩备份，和是否使用复制备份功能##备份数据库(完整备份)V1.0 Andy####[string]$serverInstance="IP\InstanceName" [string]$userName="Login"[string]$password="Password"[string]$Path="\\xxx.xxx.xxx.xxx\xxxBackup"[string]$DBList="dbname" #(选填)数据库列表，数据库之间使用‘,‘隔开，留空表示所有数据库[bool]$CopyOnly=$true; #为 True，仅复制备份；否则不是仅复制备份，而是平常备份序列的一部分[int32]$CompressionOption=1 #1 启动压缩  ，2 禁用压缩[bool]$AddBackupTime= 1 #(选填) 备份文件名是否加备份时间，格式为：_yyyyMMddHHmmss<#=====================================================================#>[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null[string]$DBName=""[datetime]$StartTime=Get-Date$Path=$Path+$(If($($Path.Split("\"))[-1] -eq "" ){""} Else {"\"})$ServerConnection =new-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password Clear-Host #清除控制台的内容#--------------------------------------#检查备份数据库清单#--------------------------------------Function CheckDB{    Param([String]$DBvar)        Begin     {        [Boolean]$CheckResult=$false    }    Process    {        If($DBList -eq "")        {            $CheckResult=$true        }        Else        {            Foreach($x In $DBList.Split(","))            {                If($x -eq $DBvar)                {                    $CheckResult=$true                    Break                }            }        }    }    End    {        Return $CheckResult    }}#1. 连接实例Try{    $ServerConnection.Connect()}Catch{        Write-Host "无法连接到数据库实例：$serverInstance"   -ForegroundColor Red}#2. 判断备份路径是否正确[bool]$PathExists=Test-Path $Pathif ($PathExists -eq $false){    Write-Host "无效的路径：$Path"   -ForegroundColor Red}#3. 备份数据库if ($ServerConnection.IsOpen -and $PathExists -eq $true){    Try    {        $Server=new-object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection        $Backup=new-object "Microsoft.SqlServer.Management.Smo.Backup"                [string]$strDate =if($AddBackupTime -eq $true){Get-Date -Format "_yyyyMMddHHmmss"}else{""}                $Backup.Action=[Microsoft.SqlServer.Management.SMO.BackupActionType]::Database        $Backup.Incremental =$false        $Backup.CompressionOption=$CompressionOption;        $Backup.CopyOnly=$CopyOnly;                  foreach ($DB in $Server.Databases | Select-Object -Property Name,IsSystemObject | Where-Object -FilterScript {$_.IsSystemObject -eq $false})        {            $DBName=$DB.Name            if (CheckDB($DBName) -eq $true ) #判断$DBName是否在备份数据库列表中            {                                $Backup.Database =$DBName                $Backup.Devices.Clear()                $Backup.Devices.AddDevice($Path+$DBName+$strDate+".bak",[Microsoft.SqlServer.Management.Smo.DeviceType]::File)                            $Backup.SqlBackupAsync($Server) #异步处理备份                Write-Host "正在备份 $DBName ... ..." -BackgroundColor Blue -ForegroundColor White                                $Backup.Wait() #等待备份完成才继续                Write-Host "完成备份 $DBName .      " -BackgroundColor Green                                }        }                [TimeSpan]$Times = (Get-Date) - $StartTime        [String]$Str=if($Times.Hours -eq 0){""}else{$Times.Hours.ToString()+ " 小时 "}        $Str+=if($Times.Minutes -eq 0){""}else{$Times.Minutes.ToString()+" 分钟 "}        $Str+=if($Times.Seconds -eq 0){""}else{$Times.Seconds.ToString()+" 秒 "}        Write-Host "备份总耗时： $Str"            }    Catch    {            Write-Error $_        Write-Host "在备份 $DB 过程中发生错误."   -ForegroundColor Red    }    }