没有特定表的MSSQL数据库备份 [英] MSSQL DataBase Backup without a specific table

问题描述

我需要在没有sql特定表的情况下进行日程表备份。因为如果我对该表进行备份将花费很长时间。我需要从备份中排除一个表。可能吗？没有该表，所有表和数据都应该存在于数据库中。

I need to take a schedule backup without a specific table in sql. Because if I take a backup with that table it will take long time. I need to exclude one table from backup. Is it possible? Without that table all tables and data should be there in the database.

推荐答案

您可以为此表设置一个单独的文件组。 ，除了PRIMARY文件组。这将使您能够创建忽略大型表的备份。下面是一个逐步执行该过程的示例。

You can setup a separate file group for this one table, apart from the PRIMARY file group. This will give you the ability to create a backup that omits your large table. Below is an example that steps out the process.

1）为您的数据库创建一个新的文件组。

1) Create a new file group for your database.

USE [master]
GO
ALTER DATABASE [EXAMPLEDB] ADD FILEGROUP [EXAMPLEFG1]
GO

2）在新文件组上创建一个名称稍有不同的相同表。

2) Create an identical table with a slightly different name on the new file group.

CREATE TABLE [dbo].[example]
(
    [e] [int] NOT NULL
)
ON [EXAMPLEFG1]

GO

3）将记录从原始表插入新表。

3) Insert records from original table into new table.

4）删除旧表并更正新表的名称以匹配旧表的名称。

4) Delete the old table and correct the name of the new table to match the name of the old table.

5 ）备份PRIMARY，现在不包括文件组 EXAMPLEFG1上的表。

5) Backup PRIMARY which now excludes the table that is now on file group "EXAMPLEFG1".

BACKUP DATABASE EXAMPLE
   FILEGROUP = 'PRIMARY',
   TO DISK = '<Your Directory>'
GO

如果您决定备份EXAMPLEFG1
只需在上面的查询中将FILEGROUP值更改为 EXAMPLEFG1即可。

If you decide to do a backup of EXAMPLEFG1 simply change the FILEGROUP value to "EXAMPLEFG1" in the above query.

查看此 Microsoft网站以获取有关文件组备份的更多信息。

Check out this Microsoft site for more info on filegroup backups.

希望这会有所帮助！

这篇关于没有特定表的MSSQL数据库备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！