A different way to back up databases

antic

antic

Perch
If there's an SQL database one wants, for arguments sake, to back up... is it possible to copy all the data into a file-based format - Access comes to mind of course - and then make that available for a client to download whenever they want to.

A script can go thru the SQL schema and create an appropriate Access db, using simple numeric fields for idents, etc... no relationships or non-primary indexes - keeping it simple, just to store the data.

Then it could be zipped up and downloaded, minimising traffic and eliminating all the EM mucking about with DTS packages or whatever just to get a backup of your data.

Anyone tried this before? See any pitfalls, anything which might make it not worth the bother? If I get a good generic script going, I'll pass it around.
 
This is a pretty slick idea. You would be potentially doubling the amount of disk space you're using since all SQL data would also be in the MDB on disk. I also wonder about performance or sucking down the servers during your export process.

Not knowing SQL real well this would be a challenge for me. But I'm sure it can be done. I know you can use system stored procedures to get lists of tables in a database and other info like that, so you could inspect the DB and pull out the data.
 
You can use ADO to loop thru tables and fields in a database. It won't require a full schema or anything, just the basics. After the MDB is downloaded, it can be deleted until the next backup. The client may only want it done weekly in most cases, as it's a job for them. :)
 
You must log in or register to reply here.
Back
Top