批量備份資料庫腳本(PowerShell版)

開始

        昨天備份一個資料庫拿來測試，發現備份後的文件非常大。後來去檢查下使用的備份腳本，原來之前的備份腳本沒有壓縮功能。

        現把之前的備份腳本修改下，支持壓縮備份，和支持僅複製備份(CopyOnly).

備份資料庫(完整備份)腳本

   （註：開初編寫這腳本的目的是能批量備份資料庫，提高工作效率，後面提到的還原資料庫腳本也是如此。）

<#=====================================================================#>
##備份資料庫(完整備份)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 $Path

if ($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
    }
    
}

調用例子

 壓縮後備份文件只有1G多。

 還原資料庫可以參考《PowerShell應用之-批量還原資料庫(支持完整，差異，事務日誌)》