I have a MSDE (aka Desktop SQL Server) database, with a MS Access
Project front-end (*.adp). This is a server for a standalone PC which
mimics a full client-server setup but on one PC for one user. The
front-end has been connected using the option in the
File>Connection... command.

I have been able to backup the database using the SQL command "BACKUP
DATABASE DBNAME1 TO DISK = 'C:\Files\Database\Backup\Backup1.bak'"

However, I am having difficulty in restoring the backups. I have tried
the following:

1. Using SQL Command "RESTORE DATABASE DBNAME1 FROM DISK =
'C:\Files\Database\Backup\Backup1.bak'"
But this gives me problems; I am warned that "Exclusive access could
not be ontained because the database is in use." I can go no further.
This makes sense, I think, because the database is still live.

2. I therefore tried to turn off the database server using the only
tool I have: SQL Service Manager, a tool which simply swithces on/off
and pauses the database server. It has no other function, and I do not
have full SQL Server tools or access to such. However, I get other
errors when  trying the SQL RESTORE command, e.g.
"[DBNETLIB][Connection (WrapperWrite()).]General network error. Check
your network documentation."

All I am after is a simple solution on how to backup/restore my MSDE
backups at the click of a button in an Access Project form (*.adp)
without any errors.

Thank you

---

Clarification of Question by razwan1979-ga on 22 Jun 2004 05:05 PDT

Just to clarify my question: I can use the BACKUP statement in SQL
without any problems. However, if a RESTORE solution requires me to
have a different BACKUP solution, then I am more than willing to
change. My main question, however, is on how to complete a RESTORe
operation in for a MSDE database with a MS Access Project front-end.

Thanks

---

Request for Question Clarification by mathtalk-ga on 22 Jun 2004 08:12 PDT

Have you made sure that the Access front-end is not connected to the
database when you are trying to RESTORE the MSDE backup?

regards, mathtalk-ga

You could just create a separate Access Project, with the connection
set to the same server but a different database that your not backing
up e.g. Master, then create stored procedures to execute your "BACKUP
DATABASE DBNAME1 TO DISK = 'C:\Files\Database\Backup\Backup1.bak'" and
"RESTORE DATABASE DBNAME1 FROM DISK =
'C:\Files\Database\Backup\Backup1.bak'" commands

Regards,
Ian

Try something like this

Sub RestoreDB()
    On Error GoTo testError
       
    Dim sql As String      
    Dim f As Form

    For Each f In Forms
        Unload f
    Next
    

    sql = "Use Master restore database DBNAME1 " & _ 
          "from disk = ''C:\Files\Database\Backup\Backup1.bak'"

    CurrentProject.AccessConnection.Execute (sql)
    MsgBox "Restored"
    
    Exit Sub
testError:
    MsgBox Err.Description
End Sub

'---
Make sure all the forms are closed. and makes sure to include the "Use
Master" to get the current connection out of the database.

Oops watch out for the 2 single quotes in the SQL string. Should only be 1

Dont try to connect the database, that you are going to restore. It
seems you are tring to restore database while some connection opened!
. make sure close all the conns to be closed before restore db. you
can also connect to the master database to doing this.
hth
Dishan