在vb中如何备份还原sql数据库?

[上官云峰]

我想在vb程序中点击'恢复'按钮就可以恢复sql数据库,点击'备份'按钮可以备份sql数据库,请各位大哥给出源代码,只要能够实现,马上结帐,送100分

[vicenttuliangbo]

你装SQl2000 有关于sqlDMO 的VB原码的使用,去COPY 吧,有好多好东东

[gzhiceberg]

up

[AKillGodKillBuddha]

http://expert.csdn.net/Expert/topic/2885/2885337.xml?temp=.3095667

[lqtflwg718]

'*************************************************************************
'**模 块 名：fBackupDatabase_a
'**描 述：备份数据库,返回出错信息,正常恢复,返回""
'**调 用：fBackupDatabase_a "备份文件名","数据库名"
'**参数说明：
'** sBackUpfileName 恢复后的数据库存放目录
'** sDataBaseName 备份的数据名
'** sIsAddBackup 是否追加到备份文件中
'**说 明：引用Microsoft ActiveX Data Objects 2.x Library
'**创 建 人：邹建
'**日 期：2003年12月09日
'*************************************************************************
Public Function fBackupDatabase_a(ByVal sBackUpfileName$ _
, ByVal sDataBaseName$ _
, Optional ByVal sIsAddBackup As Boolean = False _
) As String

Dim iDb As ADODB.Connection
Dim iConcStr$, iSql$, iReturn$

On Error GoTo lbErr

'创建对象
Set iDb = New ADODB.Connection

'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj"
iDb.Open iConcStr

'生成数据库备份语句
iSql = "backup database [" & sDataBaseName & "]" & vbCrLf & _
"to disk='" & sBackUpfileName & "'" & vbCrLf & _
"with description='" & "zj-backup at:" & Date & "(" & Time & ")'" & vbCrLf & _
IIf(sIsAddBackup, "", ",init")

iDb.Execute iSql
GoTo lbExit

lbErr:
iReturn = Error
lbExit:
fBackupDatabase_a = iReturn
End Function

'*************************************************************************
'**模 块 名：frestoredatabase_a
'**描 述：恢复数据库,返回出错信息,正常恢复,返回""
'**调 用：frestoredatabase_a "备份文件名","数据库名"
'**参数说明：
'** sDataBasePath 恢复后的数据库存放目录
'** sBackupNumber 是从那个备份号恢复
'** sReplaceExist 指定是否覆盖已经存在的数据
'**说 明：引用Microsoft ActiveX Data Objects 2.x Library
'**创 建 人：邹建
'**日 期：2003年12月09日
'*************************************************************************
Public Function fRestoreDatabase_a(ByVal sBackUpfileName$ _
, ByVal sDataBaseName$ _
, Optional ByVal sDataBasePath$ = "" _
, Optional ByVal sBackupNumber& = 1 _
, Optional ByVal sReplaceExist As Boolean = False _
) As String

Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
Dim iConcStr$, iSql$, iReturn$, iI&

On Error GoTo lbErr

'创建对象
Set iDb = New ADODB.Connection
Set iRe = New ADODB.Recordset

'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj"
iDb.Open iConcStr

'得到还原后的数据库存放目录,如果没有指定,存放到SQL SERVER的DATA目录
If sDataBasePath = "" Then
iSql = "select filename from master..sysfiles"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
iSql = iRe(0)
iRe.Close
sDataBasePath = Left(iSql, InStrRev(iSql, "\"))
End If

'检查数据库是否存在
If sReplaceExist = False Then
iSql = "select 1 from master..sysdatabases where name='" & sDataBaseName & "'"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
If iRe.EOF = False Then
iReturn = "数据库已经存在!"
iRe.Close
GoTo lbExit
End If
iRe.Close
End If

'关闭用户进程,防止其它用户正在使用数据库,导致数据恢复失败
iSql = "select spid from master..sysprocesses where dbid=db_id('" & sDataBaseName & "')"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
While iRe.EOF = False
iSql = "kill " & iRe(0)
iDb.Execute iSql
iRe.MoveNext
Wend
iRe.Close

'获取数据库恢复信息
iSql = "restore filelistonly from disk='" & sBackUpfileName & "'" & vbCrLf & _
"with file=" & sBackupNumber
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly

'生成数据库恢复语句
iSql = "restore database [" & sDataBaseName & "]" & vbCrLf & _
"from disk='" & sBackUpfileName & "'" & vbCrLf & _
"with file=" & sBackupNumber & vbCrLf
With iRe
While Not .EOF
iReturn = iRe("PhysicalName")
iI = InStrRev(iReturn, ".")
iReturn = IIf(iI = 0, "", Mid(iReturn, iI)) & "'"
iSql = iSql & ",move '" & iRe("LogicalName") & _
"' to '" & sDataBasePath & sDataBaseName & iReturn & vbCrLf
.MoveNext
Wend
.Close
End With
iSql = iSql & IIf(sReplaceExist, ",replace", "")

iDb.Execute iSql
iReturn = ""
GoTo lbExit

lbErr:
iReturn = Error
lbExit:
fRestoreDatabase_a = iReturn
End Function
要给分啊！

[AKillGodKillBuddha]

*********************************************************
'* 名称：BackupDatabase
'* 功能：备份数据库
'* 控件：一个文本框和两个按钮（备份到和确定）
'*********************************************************
Public Sub BackupDatabase()
Dim cn As New ADODB.Connection
Dim s_path, s_dataexport As String
s_path = App.Path
Me.MousePointer = 11 '设置鼠标指针形状
'student1是需要备份的数据库名称
s_dataexport = "backup database student1 to disk='" + CommonDialog1.FileName + "'"
cn.Open "driver={sql server};server=" & d1 & ";database=student1;persist security info=false; userid=sa" '数据库连接字符串
'这里不需要连接master数据库，即可完成备份
cn.BeginTrans
cn.Execute s_dataexport
Err.Number = 0
If Err.Number = 0 Then
cn.CommitTrans
MsgBox "数据备份成功！", vbInformation, "提示"
MsgBox "数据备份文件存放路径:" & CommonDialog1.FileName, vbOKOnly, "提示"
Unload Me
Else
cn.RollbackTrans
MsgBox "数据备份失败！请检查数据库是否正在打开！", vbCritical, "提示"
End If
cn.Close
Set cn = Nothing
Me.MousePointer = 1
End Sub

[AKillGodKillBuddha]

'*********************************************************
'* 名称：RestoreDataBase
'* 功能：还原数据库
'* 控件：一个文本框和两个按钮（ 打开和确定）
'*********************************************************
Public Sub RestoreDataBase()
If Text1.Text = "" Then
MsgBox "请选择要恢复的数据文件！", vbInformation, "提示"
Exit Sub
Else
ret = MsgBox("数据恢复操作将会覆盖以前的所有数据并且覆盖后无法恢复，您确定要进行恢复操作吗？", vbQuestion + vbOKCancel, "提示")
If ret = vbOK Then
Dim cn As New ADODB.Connection
Dim sn As New ADODB.Recordset
Dim s_restore As String
Me.MousePointer = 11
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;server=" & d1 & ";Initial Catalog=master;Data Source=127.0.0.1;user id=sa;password=" & d3 & ""
sn.Open "select spid from sysprocesses where dbid=db_id('student1')", cn
Do While Not sn.EOF
cn.Execute "kill " & sn("spid")
sn.MoveNext
Loop
sn.Close
s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "' with REPLACE"
cn.Execute s_restore
'Debug.Print gs_conn_string
'此时需要连接master数据库才能完成数据恢复操作
'同上student1为需要恢复的数据库
s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "'"
'text1一个用于记录需要恢复文件的地址的textbox
cn.Execute s_restore
cn.BeginTrans
If Err.Number = 0 Then
cn.CommitTrans
MsgBox "数据恢复成功！", vbInformation, "提示"
Command1.Enabled = True
Label1.Visible = False
Else
cn.RollbackTrans
MsgBox "数据恢复失败！", vbCritical, "提示"
Command1.Enabled = True
End If
cn.Close
Set cn = Nothing
Me.MousePointer = 1
Else
Exit Sub
End If '''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
Dim DBC As New DataBaseConnection
If db.State = 1 Then
db.Close
End If
db.ConnectionString = DBC.SqlConnectString(d1, d2, d3)
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
db.CursorLocation = adUseClient
db.Open
Set cmd.ActiveConnection = db
If Err.Number Then
MsgBox Err.Description, 16 + vbOKOnly, Err.Number
Exit Sub
End If
db.DefaultDatabase = "student1"
If Err.Number Then
MsgBox Err.Description, 16 + vbOKOnly, Err.Number
Exit Sub
End If
End If
End Sub '''''''''''''''''''''''''''''''''''''''''''''

如果当前没有与要恢复的数据库立连接，则不需要加单引号中的内容。

如果希望恢复数据库之后继续建立连接，则需要写这部分代码。

我要恢复数据库名称为student1，备份数据库的时候是在连接状态下进行的，但是恢复数据库不可以在数据库存在连接的状态下进行操作！代码的解决方法是：先连接SQL Server中主库master 库，在该库中的sysprocesses表中存放着所有连接到此数据库的连接信息，将这些连接信息用Kill语句删除。然后再恢复数据库student1，由于用Kill语句后，数据库已经被断开，所以在恢复完成后，再用系统最初的连接数据库代码连接上数据库student1

[fxy_2002]

我做过一个备份的，恢复的就没有了，不过原理都一样，利用 SQLDMO 对象，怎么玩都行。

[fxy_2002]

刚准备贴，才发现楼上已加了新贴了。我这个是用DMO备份的，还是有点差别：

Sub BackupDatabase(SQL_SERVER_NAME As String, USER_ID As String, USER_PASSWORD As String, DATABASE_NAME As String)

On Error GoTo BackupError

Dim strBackupFile As String
strBackupFile = Replace(CurDir() & "\BackupFiles\", "\\", "\") & DATABASE_NAME & GetRndNo() & ".BAK"
Do While Dir(strBackupFile) <> ""
strBackupFile = Replace(CurDir() & "\BackupFiles\", "\\", "\") & DATABASE_NAME & GetRndNo() & ".BAK"
Loop

Dim OBJ_SQL_SERVER As New SQLDMO.SQLServer
OBJ_SQL_SERVER.Connect SQL_SERVER_NAME, USER_ID, USER_PASSWORD

Dim OBJ_SQL_DEVICE As New SQLDMO.BackupDevice
OBJ_SQL_DEVICE.Name = "Backup_Device_" & GetRndNo()
OBJ_SQL_DEVICE.Type = SQLDMODevice_DiskDump
OBJ_SQL_DEVICE.PhysicalLocation = strBackupFile

OBJ_SQL_SERVER.BackupDevices.Add OBJ_SQL_DEVICE

Dim OBJ_SQL_BACKUP As New SQLDMO.Backup
OBJ_SQL_BACKUP.Database = DATABASE_NAME
OBJ_SQL_BACKUP.Devices = OBJ_SQL_DEVICE.Name
OBJ_SQL_BACKUP.SQLBackup OBJ_SQL_SERVER

OBJ_SQL_SERVER.BackupDevices.Remove (1)

Set OBJ_SQL_BACKUP = Nothing
Set OBJ_SQL_DEVICE = Nothing

OBJ_SQL_SERVER.DisConnect
Set OBJ_SQL_SERVER = Nothing

msgbox "备份成功！"

Exit Sub

BackupError:
msgbox "备份失败！"

End Sub

Private Function GetRndNo() As String
Randomize
GetRndNo = CStr(Int(99999999 * Rnd))
End Function

[frankwong]

收藏

[sunmoonn]

sql安装盘里有OEM
X:\DEVTOOLS\SAMPLES\BACKUP\UNZIP_BACKUP.EXE
把这个解压出来,里面有一个很不错的源码~！
楼主可以看看.............