Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rohans
Posting Yak Master
194 Posts |
 Posted - 2004-04-06 : 11:37:04
|
| At my shop we currently run various databases in house using mostly SQL servers. I want to know how I may backup my MS ACCESS database using MS SQL. I would like setup schedule backups for the MS Access application.All help appreciated. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-04-06 : 14:35:18
|
| Backing up Access by way of SQL Server? That seems overly complex (and sounds like a real example of the adage "When the only tool you have is a hammer, everything looks like a nail.")I suppose you could create a SQL Job to use xp_cmdshell to make a copy of the .mdb file, but that seems way too complicated. How about using a regular tabe backup system to backup the Access .mdb file? Or perhaps a batch or script to do the copy that is scheduled in the Windows scheduler and leave SQL Server out of this?Or better yet, upsize the database into SQL Server, dump Access, and carry on like you do with the other databases.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
 |
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-04-06 : 17:34:09
|
| You have a point but the tape not cutting it as my boss wants 10 minute backups. that mean transaction log backup. How do I achieve this with access?All help appreciated. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-04-06 : 17:38:40
|
| So, just to verify, your data is stored in Access? I don't think Access HAS transaction logs like SQL Server does.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-04-06 : 23:33:35
|
| It doesn't. You'd have to set up a job to copy the Access database file (.MDB) every 10 minutes. And if people are using it, it may not copy correctly or at all.Regardless of what your boss may want, it is not feasible with MS Access. As Mark suggested, upsize the Access tables to SQL Server and you can back it up as often as you like. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-04-07 : 09:05:56
|
| To use a "Rob Volk inspired" analogy, that is like saying "I need to know how to set fonts and do paragraph formatting in Notepad". The answer is: if you need that, you are using the wrong tool. By definition, if you need 10 minute backups, then your data must be constantly changing and mission criticial; and if that is true, then you have proven that you should use SQL Server and NOT Access in this situation.- Jeff |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-04-07 : 10:28:11
|
| Well I know it not changing that often but the boss got a bit jumpy after 2 disks in a server crashed some weeks ago. Now he wants backup like crazy as I save his data the last time so we could get back on our feet for the MS SQL systems on the box.I guess I cannot fight with the access application. It just not meant for them things. But if you have any grand suggestions I welcome them.All help appreciated. |
|
|
|
|
|
|
|
|
Backing up access