Backup

[Simon Shaw]

Can't find any mention of backup and restore in this group.
What is the normal method for giving the user of an ADE the ability to
backup and restore the SQl database.
In an MSDE context of course they don't have the SQL Enterprise manager...
....and for deployed software they may only have the Access runtime.
There must be a standard solution - any pointers please?
TIA
Simon

 

[aaron.kempf]

I believe that there are methods within the MDE that allow you to only
backup the file.. I thought that there was an option to backup the
database itself..

if i were you; i would just craft a TSQL solution

lookup BACKUP in books online

-aaron

 

[Philipp Stiefel]

Can't find any mention of backup and restore in this group.
What is the normal method for giving the user of an ADE the ability to
backup and restore the SQl database.
In an MSDE context of course they don't have the SQL Enterprise manager...
...and for deployed software they may only have the Access runtime.
There must be a standard solution - any pointers please?

With a local MSDE-Installation it's pretty straight forward
to backup an restore the Database using T-SQL procedures.
Look for the keywords Backup/Restore in the SQL-Server-Help.

Even with a local MSDE-Installation I strongly advise to
use automated backups with SQL-Agent. You can schedule a
backup job by running an T-SQL-Script upon the installation
of your app.
Users never want to backup, they just want to restore. ;-)

Cheers
Phil

 

[Simon Shaw]

Thanks Philipp,

....but that's not quite the whole picture.

Yes I can schedule a backup, but I want to give the user the ability to do
it when they want. And I know the T-SQL syntax, that's not the issue either.
Yes, if I run a backup from within the application, that works fine.

But when restoring a backup of the whole database it is necessary to have
exclusive access to the SQL database, and having the ADE open seems to
constitute the database already being in use, even if no data-bound objects
are open. So if I run T-SQL from within the application, it fails.

I was hoping there was some combined backup/restore standard code which
would get round this problem.

Rgds,
Simon

 

[Norman Yuan]

When it comes to restore from inside your ADP, you probably want to try to
disconnect the CurrentProjct.Connection from the database you want to
restore and point the connection to, say, Mater DB. Obviously, the user must
have permmission to change connection to different database (master) and
permission to restore database. Use
CurrentProject.OpenConnection()/CloseConnection() in code to switch
connection at runtime. Note, I did not do what I suggested, just an idea.