Creating SQL Server database backup with MS Access VBA

Question

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.

Does this answer your question? [Copy SQL server database with .bak](https://stackoverflow.com/questions/16322335/copy-sql-server-database-with-bak) — June7, Mar 19 '22 at 22:45
No, it's how to copy a database to sqlserver or import a database into sqlserver with vba, it's actually inverse of my request, my goal is to export a DB from sqlserver — bahman jafari, Mar 19 '22 at 23:23
Please clarify-- input is a DB from sqlserver, What is output, a file C:\somefolder\file.BAK ?? or is the output C:\somefolder\file.MDB or file.ACCDB ?? — donPablo, Mar 20 '22 at 01:19

score 2 · Answer 1 · answered Mar 20 '22 at 18:26

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.

Parfait · Answer 2 · 2022-03-20T13:00:50.900

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

DAO method returns this error : "Cannot execute a select query" — bahman jafari, Mar 20 '22 at 05:56
Try adding `qdef.ReturnsRecords = False` before Execute. See edit. — Parfait, Mar 20 '22 at 13:01
In fact, I would recommend that a pass-though query be made. Just replace the output file name in the command - and you run it with currentdb.QueryDefs("ptquery").Execute — Albert D. Kallal, Mar 20 '22 at 17:50
That was great, can we also use DAO for connectting tables to SQLServer instead of ADO method? — bahman jafari, Mar 21 '22 at 23:12
You may be asking a different question. Be sure to [close this question out](https://stackoverflow.com/help/someone-answers). Happy coding! — Parfait, Mar 22 '22 at 00:04

Creating SQL Server database backup with MS Access VBA

2 Answers2