I am connecting to a MSSQL Database that my web host is managing.
Under the tools menu, Database Utilities, when I choose Back Up SQL
Database I get this error:
Backup, restore, and drop database operations are only available for
Microsoft SQL Server version 7.0 or later running on your local
computer.
I just installed MSDE on my computer, and was surprised when I still got
the error.
Is there any way for me to back up my database? They are backing it up,
but charge to restore it if I do something stupid ;-)
Matthew
I wrote a small module that backs up my sql data to a jet db file because I
was asked to pay a fee I felt was too high for a backup copy of my db.
Windows Scheduler runs it every morning at 04:00. It has run for several
months now, without any problem.
It would not be suitable for a large amount of data. I am am backing up
only the accounts for two small businesses with it. Some of the functions
and properties it uses may be be undocumented and others may not be fully
understood. I'll be happy to answer any questions about it. I open the db
minimized, with the form set as the startup form. After the code does its
thing, it closes the db. E drive is an external hard drive. Depending on
Relationships, one might have to modify the DROP TABLE procedure to resume
next on error loop and until there are not tables. For a dozen tables and a
few thousand records it takes about 30 seconds to do its thing.
Here is is.
Option Compare Database
Option Explicit
Const ADPFile As String = "F:\Lyle Fairfield's Documents\Access
\FFDBABooks.adp"
Const Backupfile As String = "E:\FFDBABooks.mdb"
Const b As String = "PROVIDER=SQLOLEDB.1;" _
& "PERSIST SECURITY INFO=FALSE;" _
& "INITIAL CATALOG=A DATABASE NAME;" _
& "DATA SOURCE=AN INTERNET ENABLED MS-SQL SERVER"
Const p As String = "Password"
Const u As String = "UserName"
Private Sub BackupSQLTablesAsJET()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
' zap old tables
Set c = New ADODB.Connection
With c
.Open CurrentProject.BaseConnectionString
End With
Set r = c.OpenSchema( _
adSchemaTables, Array(Empty, Empty, Empty, "Table"))
With r
Do While Not .EOF
CurrentProject.Connection.Execute ("DROP TABLE " & !TABLE_NAME)
.MoveNext
Loop
End With
' refresh TableDefs
DBEngine(0)(0).TableDefs.Refresh
' set persist security information
' in the adp file to true
SecurityInformation "TRUE"
' connect to the ADP file
With c
.Close
.Open b & ";USER ID=" & u & ";PASSWORD=" & p
End With
Set r = c.OpenSchema( _
adSchemaTables, Array(Empty, Empty, Empty, "Table"))
' import the SQL tables (as JET)
With r
Do While Not .EOF
If Left(!TABLE_NAME, 2) <> "dt" Then _
DoCmd.TransferDatabase acImport, "Microsoft Access", _
ADPFile, acTable, !TABLE_NAME, !TABLE_NAME, False
.MoveNext
Loop
.Close
End With
' set persist security information
' in the adp file to false
SecurityInformation "FALSE"
' copy the tables to the BackUp Device
SaveAsText 6, "", Backupfile
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Close()
Application.Quit
End Sub
Private Sub Form_Open(Cancel As Integer)
BackupSQLTablesAsJET
End Sub
Private Sub SecurityInformation(ByVal vPERSIST As String)
Dim a As Access.Application
Set a = New Access.Application
With a
.OpenAccessProject ADPFile
With .CurrentProject
If .IsConnected Then .CloseConnection
.OpenConnection Replace(b, "FALSE", vPERSIST), u, p
End With
.Quit
End With
End Sub
