我需要在没有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] GO2)在新文件组上创建一个名称稍有不同的相同表。
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] GO3)将记录从原始表插入新表。
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数据库备份
发布评论