powershell实现数据库的自动备份

在现代大多公司、企业中，为防止服务器或数据库或数据出现问题，一般都会对服务器的数据进行备份工作，如果人为的操作，浪费时间和人力，随着微软的powershell的推出，这些工作可以让服务器自动执行操作，这样简化的操作，节省的人力，以下是我这段时间的劳动成果，实现数据库的自动备份，现在分享一下部分代码。欢迎大家拍砖上瓦。 

首先判断源服务器的备份是否成功:

代码如下:

 

  1 #===============================(1)judge is source server's bak is ok or not===============================
  2 #=======================================design by mahong 2009-11-26========================================
  3 #start
  4 
  5 $outputMessage=" "
  6 
  7 $realFolder=Get-Date -UFormat "%Y-%m-%d%A日志"
  8 $txtFileName=$outFolder+"源服务器备份日志.txt"
  9 
 10 $sourceArray="Plant.bak","PurityWater.bak","Service.bak","WaterOffice.bak","WaterOfficeCommunity.bak","WaterOfficeSchedule.bak","WonderCommunityData.bak"
 11 $plant=0
 12 $puritywater=0
 13 $service=0
 14 $wateroffice=0
 15 $waterofficecommunity=0
 16 $waterofficeschedule=0
 17 $wondercommunitydata=0
 18 
 19 #define array for the data
 20 $waterBaseArray=Get-ChildItem \\192.168.10.10\WaterBaseSqlDataCenter | Sort-Object -Property LastWriteTime | select Name,Length
 21 $waterControlArray=Get-ChildItem \\192.168.20.1\DiaoduBack | Sort-Object -Property LastWriteTime | select Name,Length
 22 $backupDataArray=Get-ChildItem \\192.168.10.11\backupdata | Sort-Object -Property LastWriteTime | select Name,Length
 23 
 24 #judge the waterbase database
 25 if($waterBaseArray.Name -eq "WaterSale2008.bak")
 26 {
 27    $outputMessage+="营业收费数据库备份文件存在 `r`n "
 28    if($waterBaseArray.Length/1gb -gt 0)
 29    {
 30       $outputMessage+="营业收费数据库文件大小正常 `r`n "
 31    }
 32 }
 33 else
 34 {
 35    $outputMessage+="营业收费数据库备份文件不存在"
 36 }
 37 
 38 #judge the watercontrol database
 39 if($waterControlArray.Name -eq "WaterControlData2008.bak")
 40 {
 41    $outputMessage+="调度数据库备份文件存在 `r`n "
 42    if($waterControlArray.Length/1gb -gt 0)
 43    {
 44       $outputMessage+="调度数据库备份文件大小正常 `r`n "
 45    }
 46 }
 47 else
 48 {
 49    $outputMessage+="调度数据库备份文件不存在 `r`n "
 50 }
 51 
 52 #judge the backupdata other database
 53 for($i=0;$i -lt $sourceArray.Length;$i++)
 54 {
 55    $tempName=$sourceArray[$i]
 56    switch($tempName)
 57    {
 58       "Plant.bak"
 59       {
 60          for($j=0;$j -lt $backupDataArray.Length;$j++)
 61          {
 62             if($backupDataArray[$j].Name -eq $tempName)
 63             {
 64                $plant=1
 65                $outputMessage+="Plant数据库备份存在 `r`n "
 66                if($backupDataArray[$j].Length/1gb -gt 0)
 67                {
 68                   $outputMessage+="Plant数据库备份大小正常 `r`n "
 69                }
 70                break
 71             }
 72          }
 73       }
 74       "PurityWater.bak"
 75       {
 76          for($j=0;$j -lt $backupDataArray.Length;$j++)
 77          {
 78             if($backupDataArray[$j].Name -eq $tempName)
 79             {
 80                $puritywater=1
 81                $outputMessage+="PurityWater数据库备份存在 `r`n "
 82                if($backupDataArray[$j].Length/1gb -gt 0)
 83                {
 84                   $outputMessage+="PurityWater数据库备份大小正常 `r`n "
 85                }
 86                break
 87             }
 88          }     
 89       }
 90       "Service.bak"
 91       {
 92          for($j=0;$j -lt $backupDataArray.Length;$j++)
 93          {
 94             if($backupDataArray[$j].Name -eq $tempName)
 95             {
 96                $service=1
 97                $outputMessage+="Service数据库备份存在 `r`n "
 98                if($backupDataArray[$j].Length/1gb -gt 0)
 99                {
100                   $outputMessage+="Service数据库备份大小正常 `r`n "
101                }
102                break  
103             }
104          }
105       }
106       "WaterOffice.bak"
107       {
108          for($j=0;$j -lt $backupDataArray.Length;$j++)
109          {
110             if($backupDataArray[$j].Name -eq $tempName)
111             {
112                $wateroffice=1
113                $outputMessage+="WaterOffice数据库备份存在 `r`n "
114                if($backupDataArray[$j].Length/1gb -gt 0)
115                {
116                   $outputMessage+="WaterOffice数据库备份大小正常 `r`n "
117                }
118                break
119             }
120          }
121       }
122       "WaterOfficeCommunity.bak"
123       {
124          for($j=0;$j -lt $backupDataArray.Length;$j++)
125          {
126             if($backupDataArray[$j].Name -eq $tempName)
127             {
128                $waterofficecommunity=1
129                $outputMessage+="WaterOfficeCommunity数据库备份存在 `r`n "
130                if($backupDataArray[$j].Length/1gb -gt 0)
131                {
132                   $outputMessage+="WaterOfficeCommunity数据库备份大小正常 `r`n "
133                }
134                break
135             }
136          }
137       }
138       "WaterOfficeSchedule.bak"
139       {
140          for($j=0;$j -lt $backupDataArray.Length;$j++)
141          {
142             $waterofficeschedule=1
143             $outputMessage+="WaterOfficeSchedule数据库备份存在 `r`n "
144             if($backupDataArray[$j].Length/1gb -gt 0)
145             {
146                $outputMessage+="WaterOfficeSchedule数据库备份大小正常 `r`n "
147             }
148             break
149          }
150       }
151       "WonderCommunityData.bak"
152       {
153          for($j=0;$j -lt $backupDataArray.Length;$j++)
154          {
155             $wondercommunitydata=1
156             $outputMessage+="WonderCommunityData数据库存在 `r`n "
157             if($backupDataArray[$j].Length/1gb -gt 0)
158             {
159                $outputMessage+="WonderCommunityData数据库备份大小正常 `r`n "
160             }
161             break
162          }
163       }
164    }
165 }
166 
167 #if the database variable is 0,it means the database is not exist
168 if($plant -eq 0)
169 {
170    $outputMessage+="Plant数据库不存在 `r`n "
171 }
172 if($puritywater -eq 0)
173 {
174    $outputMessge+="PurityWater数据库不存在 `r`n "
175 }
176 if($service -eq 0)
177 {
178    $outputMessage+="Service数据库不存在 `r`n "
179 }
180 if($wateroffice -eq 0)
181 {
182    $outputMessage+="WaterOffice数据库不存在 `r`n "
183 }
184 if($waterofficecommunity -eq 0)
185 {
186    $outputMessage+="WaterOfficeCommunity数据库不存在 `r`n "
187 }
188 if($waterofficeschedule -eq 0)
189 {
190    $outputMessage+="WaterOfficeSchedule数据库不存在 `r`n "
191 }
192 if($wondercommunitydata -eq 0)
193 {
194    $outputMessage+="WonderCommunitydata数据库不存在 `r`n "
195 }
196 
197 New-Item D:\备份报告\$realFolder -type directory
198 
199 $outputMessage > d:\备份报告\$realFolder\$txtFileName  #output is log on local disk
200 
201 #end
202 
203 
204 

 

然后进行自动拷贝工作：

代码如下：

 

 1 #===================Copy data from remote server to localhost=======================
 2 #========================Design by mahong at 2009-11-23=============================
 3 $outFolder=Get-Date -UFormat "%Y-%m-%d%A"   #define the out Folder
 4 $outPutMessage=""
 5 
 6 New-Item D:\数据库备份\$outFolder -type directory  #destination is the drive D: new a folder named "数据库备份"
 7 
 8 $waterSale="WaterSale" #set the name of watersale
 9 $waterControl="WaterControl"+$outFonder # set the name of watercontrol
10 $backupData="BackupData"+$outFonder # set the name of backupdata
11 
12 #new the folder 
13 New-Item D:\数据库备份\$outFolder\$waterSale -type directory
14 
15 New-Item D:\数据库备份\$outFolder\$waterControl -type directory
16 
17 New-Item D:\数据库备份\$outFolder\$backupData -type directory
18 #new end
19 
20 #copy data from remote server
21 Copy-Item \\192.168.10.10\WaterBaseSqlDataCenter\*.bak D:\数据库备份\$outFolder\$waterSale -recurse
22 Copy-Item \\192.168.10.11\backupdata\*.bak D:\数据库备份\$outFolder\$backupData -recurse
23 Copy-Item \\192.168.20.1\DiaoduBack\*.bak D:\数据库备份\$outFolder\$waterControl -recurse
24 #copy end
25 
26 
27 

再次就是用powershell进行删除源服务器的备份副本，检查备份完整性，检查本地磁盘是否有足够空间，如果不足，用脚本自动删除过早的备份文件,生成备份日志文件等各种操作，都可以进行,这些代码和上面大同小异，代码就略了。