Backing up an MSSQL database without a specific table

Question

I need to back up a schedule without a specific table in sql. Because if I take a backup with this table, it will take a lot of time. I need to exclude one table from the backup. Is it possible? Without this table, all tables and data must be in the database.

Answer 1

You can configure a separate file group for this table, except for the PRIMARY file group. This will give you the opportunity to create a backup that omits your large table. The following is an example that fixes this process.

1) .

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

2) .

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

GO

3) .

4) .

5) PRIMARY, , "EXAMPLEFG1".

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

EXAMPLEFG1 FILEGROUP "EXAMPLEFG1" .

Microsoft .

, !

Answer 2

, , .

, , . , , . , . , , .

, , :

• .

  USE [master]  
  ALTER DATABASE [MyDatabase] ADD FILEGROUP [FG_MYBIGTABLE]
  

• .

  ALTER DATABASE [MyDatabase]
  ADD FILE
  (
      name = MyDatabase_MyBigTable,
      filename = 'C:\DB_Files\MyDatabase_MyBigTable.ndf',
      size = 1024MB,
      maxsize = unlimited,
      filegrowth = 100MB
  ) 
  TO FILEGROUP FG_MYBIGTABLE
  

• , DROP_EXISTING .

  CREATE UNIQUE CLUSTERED INDEX CIX_MyBigTable
  ON MyDatabase.dbo.MyBigTable (ID)
  WITH DROP_EXISTING, ONLINE
  ON FG_MYBIGTABLE
  

• .

  CREATE NONCLUSTERED INDEX [IX_OtherTable] 
  ON MyDatabase.dbo.MyBigTable (OtherTable_ID)
  WITH DROP_EXISTING, ONLINE
  ON FG_MYBIGTABLE
  

• , PRIMARY.

  BACKUP DATABASE MyDatabase
  FILEGROUP = 'PRIMARY',
  TO DISK = 'C:\Backups\MyBackup.bak'
  

Answer 3

. , , . , . : , , , . . , - .