用巨大的表发布脚本(Publish script with huge tables)

用巨大的表发布脚本(Publish script with huge tables)

我试图在SQL中备份特定的表，但是当发布包含数据的脚本时，脚本太大了。 它已经运行了一个多小时，脚本大小已超过1TB。 即使执行这么大的脚本也是不可能的。

我在SQL中重新创建了这些表的备份，但它只是太大了，我的数据库目录上没有足够的空间。

有没有人对如何备份这些表有任何建议？

Table 1 : 56,370,203 records Table 2 : 35,528,469 records Table 3 : 7,965,568 records

先谢谢你。

I am trying to make a backup of specific tables within SQL but when publishing the script with data included the script is just too big. It has been running now for more that an hour and the script size is already more than a terabyte. Even executing a script this big would be impossible.

I have recreated backups for these tables within SQL but it is just simply too big and I do not have enough space on my DB directory.

Does anyone have any advice on how to make backups of these tables?

Table 1 : 56,370,203 records Table 2 : 35,528,469 records Table 3 : 7,965,568 records

Thank you in advance.

最满意答案

所以，如果你只想保留某些表的状态然后稍后恢复它，那就是我要做的：

1）创建另一个数据库[TableBackup]。

2）要备份表，请为每个表执行此操作：

USE [TableBackup] GO IF OBJECT_ID('<<yourTable>>') IS NOT NULL DROP <<yourTable>>; SELECT * INTO <<yourTable>> FROM <<yourDB>>.dbo.<<yourTable>>; GO

3）要恢复表，请为每个表执行此操作：

USE [<<yourDB>>] GO TRUNCATE <<yourTable>>; INSERT INTO <<yourTable>> FROM [TableBackup].dbo.<<yourTable>>; GO

未经测试。 您可能必须为表上的外键和/或标识列调整此值。

So, if you only want to preserve that state of certain tables and then restore them later, this is what I would do:

1) Make another database [TableBackup].

2) To backup the tables, do this for each table:

USE [TableBackup] GO IF OBJECT_ID('<<yourTable>>') IS NOT NULL DROP <<yourTable>>; SELECT * INTO <<yourTable>> FROM <<yourDB>>.dbo.<<yourTable>>; GO

3) To restore the tables, do this for each table:

USE [<<yourDB>>] GO TRUNCATE <<yourTable>>; INSERT INTO <<yourTable>> FROM [TableBackup].dbo.<<yourTable>>; GO

Untested. You may have to adjust this for Foreign Keys and/or Identity columns on the tables.