April 20, 2009 at 3:25 am
Hi all
I am being asked for a backup of an SQL database to Access mdb format?
Is there a built-in way to do this or do I require some extra software?
Many thanks
Farren Minns
April 20, 2009 at 3:34 am
You can not directly backup from SQL server to Access.
To do this, you will need to export all your DB objects from SQL server to Access, The tables can easily be exported by using the export wizard but the Stored Procedures, View and Functions may need to be re-written.
April 20, 2009 at 3:35 am
You could try using the SSIS Import \ Export Wizard.
In Management Studio right click on the database in question, "Tasks", "Export Data".
In the resulting wizard check that the source server and database are correct, then click "Next" and in the "Destination" drop down select "Microsoft Access" and point the wizard at an existing empty Access database (it will not create one for you).
Select the tables and views you wish to export (choose all of them if you like) and then execute the package.
That will copy tables and views into the Access database, which may be sufficient for your needs.
Stored procs, functions, assemblies etc. will not be copied across. All you're essentially getting is the data.
April 20, 2009 at 4:32 am
Well, from what I can gather the last time this was done someone created a backup in mdb format that was then just sent to the user in question so they could have a look at the data. I assume they have MS Access on thier desktop, but I don't have it available on the server in question.
Would it them be a case of just exporting to a flat file and letting them import it?
April 20, 2009 at 4:45 am
No that's fine, you won't need Access on the server in question.
Use Access from your PC to create (or if you don't have Access, have the user create for you) an empty Access MDB.
Move this to a network share (or copy it to the SQL Server itself).
Proceed with the Export wizard method described above, and on the "Destination" tab of the wizard, point it at the empty MDB.
That should do the trick for you.
April 20, 2009 at 5:01 am
Ah, ok cool. I shall ask the user to create a file for me.
Many thanks
Farren
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply