Creating SQL Server database backup with MS Access VBA-CodePudding

I already created a backup Access backend database via VBA, and it was so easy. With another Access project, backend is created in SQL Server and frontend in Access, now I want to know if it's possible to create a SQL Server database backup directly from VBA?

It's important that the backup is created on a different path that I specified it before.

CodePudding user response：

I would suggest you create a PT query. Then you code can look like this:

Dim today As String, strSQL As String, strOutFile As String

strOutFile = "C:\BackUps\myDatabase_" & 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:\MyLocalBackUps\backup.bak"

Remember, WHEN you run the backup code, the path name used is the sql server computer path name - not your local drive/path names. So, once that backup is made, then I outline that you could say "copy" the file from the server to some other place. And this would assume that the folder c:\BackUps on the server exists, and in theory "shared" if you want or need to move the .bak file from the server to some other place. So, do keep in mind that the path name used and specified in that sql command is SERVER file name and path - not your local computer.

CodePudding user response：

To create a logical backup is a simple SQL command. So you can use ADO or DAO connections or even run with sqlcmd (command line tool that ships with SQL Server installation). Backup paths below change by the date with suffix in filename. Adjust paths and connection strings accordingly.

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:\Desired\Path\myDatabase_" & 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:\Desired\Path\myDatabase_" & today &".bak';"

Set qdef = CurrentDb.CreateQueryDef("")

qdef.Connect = "ODBC;" & strConn
qdef.SQL = strSQL
qdef.ReturnsRecords = False
qdef.Execute

Set qdef = Nothing

Shell (calling 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:\Desired\Path\myDatabase_" & today &".bak';"

strCMD = "sqlcmd -S localhost -d myDatabase -E -q """ & strSQL & """"

retVal = Shell(strCMD, vbNormalFocus)

Set shell = Nothing