在现代大多公司、企业中,为防止服务器或数据库或数据出现问题,一般都会对服务器的数据进行备份工作,如果人为的操作,浪费时间和人力,随着微软的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
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
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进行删除源服务器的备份副本,检查备份完整性,检查本地磁盘是否有足够空间,如果不足,用脚本自动删除过早的备份文件,生成备份日志文件等各种操作,都可以进行,这些代码和上面大同小异,代码就略了。