批量备份数据库脚本(PowerShell版)
时间：2021-07-01 10:21:17
帮助过：23人阅读
=====================================================================#>
##备份数据库(完整备份)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应用之-批量还原数据库(支持完整，差异，事务日志)》
批量备份数据库脚本(PowerShell版)
标签：function erro sync white sqlserver word 批量 gem check
