VB备份与还原SQL数据库代码

最新推荐文章于 2025-05-12 15:46:43 发布

原创最新推荐文章于 2025-05-12 15:46:43 发布 · 2.5k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

SQL

本文介绍了一种使用VBA实现数据库备份与还原的方法。通过具体的代码示例展示了如何利用SQL Server进行数据库的备份操作，并提供了数据库还原的过程。此方法适用于需要定期备份数据库并能够快速恢复数据的应用场景。

Private Sub mnuDBBackUp_Click() '------------------备份数据库 Dim cnbak As New ADODB.Connection If cnbak.State <> 0 Then cnbak.Close cnbak.Open "provider=sqloledb;server=" & 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.1;Persist Security Info=False;User ID=" & strSQLUser & ";Password=" & strSQLPW & ";Initial Catalog=master;Data 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.1;User ID=" & strSQLUser & ";Password=" & strSQLPW & ";Initial Catalog=master;Data Source=" & strSQLServer MsgBox "数据库恢复成功!", vbInformation Else MsgBox "请先备份数据库！", vbInformation, "提示:" End If Set cn = Nothing End Sub

标签

#数据库 #sql #vb #database #security