使用VB.net实现sql数据库的备份与恢复代码

Private Sub Button _Click(ByVal sender As System Object ByVal e As System EventArgs) Handles Button Click 备份数据库 BACKUP DATABASE Test TO DISK= C:\Backup WITH Format Dim cn As SqlConnection Dim cm As SqlCommand cn = getDB() cm = New SqlCommand( BACKUP DATABASE JXbase TO DISK= C:\Backup WITH Format cn) cn Open() cm ExecuteNonQuery() MsgBox( 备份成功 ) cn Close()

End Sub

恢复 Private Sub Button _Click(ByVal sender As System Object ByVal e As System EventArgs) Handles Button Click Dim cn As New SqlConnection Dim cn As New SqlConnection Dim mydr As SqlDataReader Dim str As String

Dim tmpConnectionString As String = Data Source=JJKInitial Catalog=masterIntegrated Security=True cn ConnectionString = tmpConnectionString cn ConnectionString = tmpConnectionString cn Open() cn Open() Dim cm As SqlCommand = New SqlCommand( select spid from master sysprocesses where dbid=db_id( JXbase ) cn) mydr = cm ExecuteReader()

Dim cm As SqlCommand = New SqlCommand() cm Connection = cn

While (mydr Read()) 杀死进程

str = kill &mydr( spid ) ToString() cm CommandText = str cm CommandType = CommandType Text Application DoEvents() cm ExecuteNonQuery() 杀进程 End While mydr Close() cm = New SqlCommand( RESTORE DATABASE JXbase FROM DISK= C:\Backup cn) 恢复备份 cm ExecuteNonQuery() MsgBox( 恢复成功成功 ) cn Close() cn Close()

lishixinzhi/Article/program/net/201311/12901

我以前写过的一个代码，你参考下

酒店是我的数据库名

Public dbs As New ADODB.Connection

Public dbs1 As New ADODB.Connection ’这些放在模块里

‘以下是还原代码

If MsgBox("数据还原后需要重新启动本系统,继续吗?", 1 + 32, "提示") = vbOK Then

file = App.Path &"\酒店.bak"

If Dir(file) <>"" Then

dbs.Close

Set dbs = Nothing

dbs1.ConnectionString = "Provider=SQLOLEDB.1Persist Security Info=Falseuid=" &yh &"pwd=" &mm &"" _

&"Initial Catalog=master

Dim res1 As New ADODB.Recordset

dbs1.CursorLocation = adUseClient

dbs1.Open

res1.Open "select spid from sysprocesses where dbid=db_id('酒店')", dbs1, 1, 1

Do While Not res1.EOF

dbs1.Execute "kill " &res1("spid")

res1.MoveNext

Loop

dbs1.Execute "RESTORE Database 酒店 from disk ='" &file &"' WITH REPLACE"

dbs.Open cnn

dbs1.Close

Set dbs1 = Nothing

MsgBox "数据还原成功！", 0 + 48, "提示"

End

Else

MsgBox "所要还原的文件不存在", 0 + 48, "提示"

End If

Private Sub mnuDBBackUp_Click() '------------------备份数据库

Dim cnbak As New ADODB.Connection

If cnbak.State <>0 Then cnbak.Close

cnbak.Open "provider=sqloledbserver=" &strSQLServer &"user id=" &strSQLUser &"password=" &strSQLPW

Dim backupSQL As String

backupSQL = "USE master " + Chr(10) + Chr(13)

backupSQL = backupSQL &"exec sp_addumpdevice 'disk','bak1'," &"'" &App.Path &"\bak1.bak" &"'"'bak1为备份设备

backupSQL = backupSQL + Chr(10) + Chr(13) + "BACKUP DATABASE SuperMarketdb TO bak1 WITH INIT"

cnbak.Execute backupSQL

MsgBox "数据库备份成功!", vbInformation

cnbak.Close

Set cnbak = Nothing

End Sub

Private Sub mnuDBResume_Click() '-------------------还原数据库

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset

If Dir(App.Path &"\bak1.bak") <>vbNullString Then

If cnMain.State <>0 Then cnMain.Close

cn.ConnectionString = "Provider=SQLOLEDB.1Persist Security Info=FalseUser ID=" &strSQLUser &"Password=" &strSQLPW &"Initial Catalog=masterData Source=" &strSQLServer '借助master数据库来恢复strSQLUser是SQL登录名，Password是密码，strSQLServer是服务器名

cn.CursorLocation = adUseClient

cn.Open

rs.Open "select spid from sysprocesses where dbid=db_id('SuperMarketdb')", cn

Do While Not rs.EOF

cn.Execute "kill " &rs("spid") '杀掉sql服务器的所有连接，否则会出现：数据库正在使用，无法完成排它 *** 作等等，很重要

rs.MoveNext

Loop

cn.Execute ("restore database SuperMarketdb from disk='" &App.Path &"\bak1.bak" &"' with replace")

cn.Close

cnMain.Open "Provider=SQLOLEDB.1User ID=" &strSQLUser &"Password=" &strSQLPW &"Initial Catalog=masterData Source=" &strSQLServer

MsgBox "数据库恢复成功!", vbInformation

Else

MsgBox "请先备份数据库！", vbInformation, "提示:"

End If

Set cn = Nothing

End Sub

---