没有特定表的MSSQL数据库备份

本文介绍了没有特定表的MSSQL数据库备份的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要在没有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.

希望这会有所帮助！