每小时备份部分特定表的最有效，最简单的方法 [英] Most efficient and easiest way to back up a portion of specific tables hourly

问题描述

我需要从SQL Server数据库中创建一些特定表的每小时.SQB备份文件，每个表都使用WHERE子句过滤.例如，我需要以下数据:

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.

一种选择是对同一SQL Server实例上的登台数据库进行某种形式的导出，脚本生成等操作.除了效率，我没有问题每次都删除或截断目标数据库上的表.简而言之，我希望将表的模式和数据都复制到目标数据库.完全可以接受.

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.

另一种方法是仅从源数据库创建.SQB备份.我真正需要的只是.SQB文件(它将发送到SFTP)-也可以.

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?

推荐答案

好吧，如果我正确理解您的要求，则希望将数据库中某些表中的数据定期传送到其他地方.

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. SQL Server中不可能的事情是从数据库中备份表的子集.因此，这不是一个选择.

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.

由于您已经提到过，您将使用SFTP发送数据，因此，使用BCP命令提取数据是一种选择，但是BCP命令的执行效果可能会很好，也可能不会很好，并且绝对无法很好地进行横向扩展.

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.

同样，复制订户数据库也有一些选择.

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

• 无需访问订户数据库即可访问数据客户端 用于提取物.
• 在订阅者数据库上使用BCP提取数据， 而不会对您的生产服务器造成负担.
• 使用SSIS包来 从订户数据库中提取数据.
• 最后创建一个备份 此订户数据库并将整个备份(.bak)文件发送到 SFPT.

• 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.

这篇关于每小时备份部分特定表的最有效，最简单的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！