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.

is the data in this one table unimportant? Why have so much unimportant data? The purpose of a backup (generally speaking) is to be used in the case of a problem with the db, so that you can recover. If you aren't backing up the entire DB, you can't recover the entire DB. If it were a big enough table to have that much data, I would think it would be important to some process. — Kritner, Nov 19 '15 at 18:00

Mike Zalansky · Accepted Answer · 2015-11-20T14:53:35.290

25

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!

edited Nov 20 '15 at 14:53

answered Nov 19 '15 at 17:59

Mike Zalansky

796
7
14

I need to Back up the whole data base without a one table is it possible with this? – Jude Nanayakkara Nov 20 '15 at 04:21
1

Yes, you are placing the large table outside of the PRIMARY file group onto a secondary file group and only backing up the PRIMARY file group which omits the large table. Here's my recommendation, if you are unsure and want to make sure you do it right, I would take your most recent backup of this database and restore it to a different instance; that way, you can practice the steps and make sure everything is working as expected. Also, be sure to read the links I provided, especially the "Microsoft Link" site. Hope this helps! – Mike Zalansky Nov 20 '15 at 14:47
@MikeZalansky great, helped me a lot. but you missed, after creating a filegroup, you need to add a file into that filegroup. :) – Matthias Burger Jul 08 '20 at 08:29

score 12 · Answer 2 · edited May 23 '17 at 12:17

Mike's answer is almost correct, except that you don't need to create another table to move the data.

According to this answer, which I tested myself, after creating a filegroup, you just need to move the clustered index to the other filegroup. If you don't have a clustered index, you can create one just to help you in this process. If you also have nonclustered indexes, move them too. After you finish, all data will have been moved between filegroups automatically.

So, if you want to exclude this big table from your backup routine, do the following:

Create a new file group.

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

Add a new file to this file group.

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

Move the clustered index by creating another with DROP_EXISTING the old one.

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

Move the other indexes.

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

Backup the database referencing the PRIMARY file group.

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

MSSQL DataBase Backup without a specific table

2 Answers2

Linked