使用MS Access VBA创建SQL Server数据库备份

Question

我已经通过VBA创建了一个备份Access后端数据库，而且非常容易。对于另一个Access项目，后端在SQL Server中创建，前端在Access中创建，现在我想知道是否可以直接从VBA创建SQL Server数据库备份？

重要的是，备份是在我之前指定的不同路径上创建的。

Answer 1

我建议您创建一个PT查询。然后你的代码可以看起来像这样：

Dim today As String, strSQL As String, strOutFile As String
strOutFile = "C:BackUpsmyDatabase_" & today & ".bak"
today = Format(Date, "YYYYY_MM_DD")
strSQL = "BACKUP DATABASE myDatabase " _
& "TO DISK = '" & strOutFile & "';"
With CurrentDb.QueryDefs("MyPT")
.SQL = strSQL
.ReturnsRecords = False
.ODBCTimeout = 300  ' 300 secoonds = 5 minutes - change if needed.
Debug.Print "staring backup"
strCon = .Connect
.Execute
End With
' now copy file from server to local
Dim strServerPath    As String
strServerPath = Split(strCon, ";")(2)
strServerPath = Split(strServerPath, "=")(1)
strServerPath = "\" & strServerPath & "" & strOutFile
' copy file to local computer location???
FileCopy strServerPath, "c:MyLocalBackUpsbackup.bak"

请记住，当您运行备份代码时，使用的路径名是sql server计算机路径名，而不是本地驱动器/路径名。所以，一旦备份完成，我概述一下，你可以说"；复制"；从服务器到其他位置的文件。这将假设服务器上的文件夹c:\BackUps存在，并且在理论上"；共享的"；如果您希望或需要将.bak文件从服务器移动到其他位置。因此，请记住，在sql命令中使用和指定的路径名是SERVER文件名和路径，而不是您的本地计算机。

Answer 2

创建逻辑备份是一个简单的SQL命令。因此，您可以使用ADO或DAO连接，甚至可以使用sqlcmd(SQL Server安装附带的命令行工具(运行。下面的备份路径会随着文件名中带后缀的日期而更改。相应地调整路径和连接字符串。

ADO

Dim conn As ADODB.Connection
Dim today As String, strConn As String, strSQL As String
today = Format(Date, "YYYYY_MM_DD")
strConn = "Driver={ODBC Driver 17 for SQL Server};" _
& "Server=localhost;" _
& "Database=myDatabase;" _
& "Trusted_Connections=yes"
strSQL = "BACKUP DATABASE myDatabase " _
& "TO DISK = 'C:DesiredPathmyDatabase_" & today &".bak';"
Set conn = New ADODB.Connection
conn.Open strConn
conn.Execute strSQL
conn.Close
Set conn = Nothing

DAO

Dim qdef As DAO.QueryDef
Dim today As String, strConn As String, strSQL As String
today = Format(Date, "YYYYY_MM_DD")
strConn = "Driver={ODBC Driver 17 for SQL Server};" _
& "Server=localhost;" _
& "Database=myDatabase;" _
& "Trusted_Connections=yes"
strSQL = "BACKUP DATABASE myDatabase " _
& "TO DISK = 'C:DesiredPathmyDatabase_" & today &".bak';"
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = "ODBC;" & strConn
qdef.SQL = strSQL
qdef.ReturnsRecords = False
qdef.Execute
Set qdef = Nothing

Shell(调用sqlcmd(

Dim retVal As Variant
Dim today As String, strCMD As String, strSQL
today = Format(Date, "YYYYY_MM_DD")
strSQL = "BACKUP DATABASE myDatabase " _
& "TO DISK = 'C:DesiredPathmyDatabase_" & today &".bak';"
strCMD = "sqlcmd -S localhost -d myDatabase -E -q """ & strSQL & """"
retVal = Shell(strCMD, vbNormalFocus)
Set shell = Nothing