SQL Server数据库备份/恢复
SQLDMO(SQL Distributed Management Objects,SQL分布式管理对象)封装 Microsoft SQL Server 2000 数据库中的对象。SQL-DMO 允许用支持自动化或 COM 的语言编写应用程序,以管理 SQL Server 安装的所有部分。SQL-DMO 是 SQL Server 2000 中的 SQL Server 企业管理器所使用的应用程序接口 (API);因此使用 SQL-DMO 的应用程序可以执行 SQL Server 企业管理器执行的所有功能。
SQL-DMO 用于必须包含 SQL Server 管理的任何自动化或 COM 应用程序,例如:
1.封装 SQL Server 作为自己的数据存储并想尽量减少用户的 SQL Server 管理任务的应用程序。
2.在程序本身并入了专门的管理逻辑的应用程序。
3.想在自己的用户界面中集成 SQL Server 管理任务的应用程序。
SQLDMO对象来自SQLDMO.dll,SQLDMO.dll是随SQL Server2000一起发布的。SQLDMO.dll自身是一个COM对象,因此,在你的.NET项目里必须先引用它
全部源代码:
Public Class FrmMain
Inherits System.Windows.Forms.Form
Windows 窗体设计器生成的代码
Private ServerName As String
Private UserName As String
Private Password As String
Private message As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'得到SQL服务器的列表
'必须安装SQL SERVER 2000 SP2 及以上版本
Dim I As Short
Dim sqlApp As New SQLDMO.Application
Dim ServerName As SQLDMO.NameList
ServerName = sqlApp.ListAvailableSQLServers
For I = 1 To ServerName.Count
cbServer.Items.Add(ServerName.Item(I))
Next
cbServer.Items.Add("(local)")
End Sub
Private Sub cbServer_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbServer.SelectedIndexChanged
'
'得到指定SQL服务器所有数据库的列表
Try
Dim sqlApp As New SQLDMO.Application
Dim oServer As New SQLDMO.SQLServer
If A.Checked Then
oServer.LoginSecure = True
oServer.Connect(cbServer.Text)
Else
oServer.Connect(cbServer.Text, txtUserName.Text, txtPassword.Text)
End If
'
cbDatabase.Items.Clear()
Dim db As SQLDMO.Database
For Each db In oServer.Databases
Me.cbDatabase.Items.Add(db.Name)
Next
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "系统消息")
End Try
End Sub
Private Sub cbDatabase_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbDatabase.SelectedIndexChanged
Try
Dim I As Short
Dim oServer As New SQLDMO.SQLServer
If A.Checked Then
oServer.LoginSecure = True
oServer.Connect(cbServer.Text)
Else
oServer.Connect(cbServer.Text, txtUserName.Text, txtPassword.Text)
End If
Dim db As New SQLDMO.Database
For I = 1 To oServer.Databases.Count
If oServer.Databases.Item(I, "dbo").Name = "Northwind" Then Exit For
Next
If I > oServer.Databases.Count Then Exit Sub
db = oServer.Databases.Item(I, "dbo")
ListBox1.Items.Clear()
'得到所有的存储过程
For I = 1 To db.StoredProcedures.Count
ListBox1.Items.Add(db.StoredProcedures.Item(I, "dbo").Name)
Next
'得到所有的表
For I = 1 To db.Tables.Count
ListBox1.Items.Add(db.Tables.Item(I, "dbo").Name)
Next
' 得到所有的视图
For I = 1 To db.Views.Count
ListBox1.Items.Add(db.Views.Item(I, "dbo").Name)
Next
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "系统消息")
End Try
End Sub
'声明
Public WithEvents bkps As SQLDMO.Backup
Public WithEvents rps As SQLDMO.Restore
Private Sub btnBak_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBak.Click
Try
Dim oServer As New SQLDMO.SQLServer
oServer.LoginSecure = False
If A.Checked Then
oServer.LoginSecure = True
oServer.Connect(cbServer.Text)
Else
oServer.Connect(cbServer.Text, txtUserName.Text, txtPassword.Text)
End If '连接服务器
Me.Cursor = Windows.Forms.Cursors.WaitCursor
bkps = CreateObject("SQLDMO.Backup")
bkps.Database = cbDatabase.Text '指定需备份的数据库
bkps.Action = 0
bkps.Files = "d:\" & cbDatabase.Text & ".bak" '指定备份文件
bkps.Initialize = True
ProgressBar1.Value = 0
ProgressBar1.Maximum = 100
Me.Cursor = Windows.Forms.Cursors.Default()
Application.DoEvents()
Dim mouseCur As Cursor
Me.Cursor = Windows.Forms.Cursors.WaitCursor
bkps.SQLBackup(oServer)
ProgressBar1.Value = 100
Application.DoEvents()
bkps = Nothing
Me.Cursor = Windows.Forms.Cursors.Default()
MsgBox("数据库恢复完成", MsgBoxStyle.Information, "系统消息")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "系统消息")
End Try
End Sub
Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
Try
Dim oServer As New SQLDMO.SQLServer
oServer.LoginSecure = False
If A.Checked Then
oServer.LoginSecure = True
oServer.Connect(cbServer.Text)
Else
oServer.Connect(cbServer.Text, txtUserName.Text, txtPassword.Text)
End If '连接服务器
Me.Cursor = Windows.Forms.Cursors.WaitCursor
rps = CreateObject("SQLDMO.Restore")
rps.Database = cbDatabase.Text '指定需备份的数据库
rps.Action = 0
rps.Files = "d:\" & cbDatabase.Text & ".bak" '指定备份文件
'rps.Initialize = True
ProgressBar1.Value = 0
ProgressBar1.Maximum = 100
Me.Cursor = Windows.Forms.Cursors.Default()
Application.DoEvents()
Dim mouseCur As Cursor
Me.Cursor = Windows.Forms.Cursors.WaitCursor
rps.SQLRestore(oServer)
ProgressBar1.Value = 100
Application.DoEvents()
rps = Nothing
Me.Cursor = Windows.Forms.Cursors.Default()
MsgBox("数据库备份完成", MsgBoxStyle.Information, "系统消息")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "系统消息")
End Try
End Sub
'显示进度
Private Sub bkps_PercentComplete(ByVal Message As String, ByVal Percent As Integer) Handles bkps.PercentComplete
ProgressBar1.Value = ProgressBar1.Maximum * (Percent / 100)
End Sub
Private Sub rps_PercentComplete(ByVal Message As String, ByVal Percent As Integer) Handles rps.PercentComplete
ProgressBar1.Value = ProgressBar1.Maximum * (Percent / 100)
End Sub
End Class
下载源代码:/Files/duanzt/DBManager.rar