Most efficient and easiest way to back up a portion of specific tables hourly

Question

I need to create an hourly .SQB backup file of some specific tables, each filtered with a WHERE clause, from a SQL Server database. As an example, I need this data:

SELECT * FROM table1 WHERE pk_id IN (2,5,7)
SELECT * FROM table2 WHERE pk_id IN (2,5,7)
SELECT * FROM table3 WHERE pk_id IN (2,5,7)
SELECT * FROM table4 WHERE pk_id IN (2,5,7)

The structure of the tables on the source database may change over time, e.g. columns may be added or removed, indexes added, etc.

One option is to do some kind of export, script generation, etc. into a staging database on the same instance of SQL Server. Efficiency aside, I have no problem dropping or truncating the tables on the destination database each time. In short, I'm looking to have both the schema and data of the tables duplicated to the destination database. That's completely acceptable.

Another is to just create a .SQB backup from the source database. Being that the .SQB file is all that I really need (it's going to be sent SFTP) - that would be fine, too.

What's the recommended approach in this scenario?

Answer 1

Well if I understand your requirement correctly, you want data from some tables from your database to be shipped over to somewhere else periodically.

1. Thing that is not possible in SQL server is taking a backup of a subset of tables from your database. So, this is not an option.

2. Since you have mentioned you will be using SFTP to send the data, using BCP command to extract data is one option, but BCP command may or may not perform very well and it definitely will not scale-out very well.

3. Instead of using BCP, I would prefer an SSIS package, you will be able to do all (extract files, add where clauses, drop files on SFTP, tune your queries, logging, monitoring etc) in your SSIS package.
4. Finally, SQL Server Replication can be used to create a subscriber, only publish the articles (tables) you are interested in, you can also add where clauses in your publications.

Again there are a few options with the replication subscriber database.

• Give access to your data clients to your subscriber database, no need for extracts.
• Use BCP on the subscriber database to extract data, without putting load on your production server.
• Use SSIS Package to extract data from the subscriber database.
• Finally create a backup of this subscriber database and ship the whole backup (.bak) file to SFPT.

I short there is more than one way to skin the cat, now you have to decide which one suits your requirements best.