[RESOLVED] How to - Auto DB backup, partial?

[HongKongCV]

Okay, we're using Sql Server 2008 R2, and want to setup an automatic backup of our database. Not the whole database, though, just some of the tables in the database.

Ideally, we should be able to schedule this as an automatic process that extracts the tables we want and puts them into another database that we can give to a customer and have them just mount the thing on their system.

I found this article, and it looks a lot like what we want, but it backs up the entire database.
http://www.securebackup.com/online-b...server-backup/

Any ideas as to the best way to do this?

[GaryMazzone]

That is not possible using the standard backup functions that ship with SQL Server. The whole thing or nothing. You could look into various third party tools to see if this is avail from them.

But if you do this then the client needs to buy the same backup system that you use. Did you think about doing this in SSIS and export to a flatfile then have a process on the client side that reads the file (again SSIS) and imports to the database.

[HongKongCV]

Okay, I installed SSIS and am looking at building the process using BDIS... a Business Intelligence Projects project. I even got it working... for one table. I can successfully fully send one table over to the specified Sql Server instance.

But how do I do two? It will only let me create one connection manager for my source database, and each connection manager is associated with a table or query. Is there a way around this, or must I have one app for each table that needs exporting?

[GaryMazzone]

No the connection manager this is a database is connected to 1 database. The source is different for each data flow. I would just use a seperate data flow task for each table

[HongKongCV]

That's what I ended up doing, and it is working well. Thanks.