MSSQL DataBase Backup without a specific table

Question

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.

Answer 1

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) Create a new file group for your database.

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

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) Insert records from original table into new table.

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

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

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

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

Hope this helps!