0

I am working a database with a size of 325 GB total. Out of this a single table Documents is occupying 321 GB storage and the rest of the 84 table occupy the remaining space. As the database is growing rapidly, taking backups is consuming a lot of space. In most of the scenarios Documents table is not required to be backed up.

So we created a new filegroup and moved the Clustered Index with primary key of the Documents Table only to the secondary filegroup. Despite this when we take the PRIMARY filegroup backup with Backup Type set as FULL, we are also getting all the SECONDARY filegroup records.

How can we take backups of the PRIMARY filegroup without including the Document table? Also I noticed that despite creating the new index in SECONDARY filegroup, it's file size is still the same when it was created, and it is not reflecting the size of the Documents table.

2
  • 2
    can you write the backup / restore commands (anonymized if you need) that you're issuing? and also table schema with the filegroups. Otherwise it's not easy to know
    – siggemannen
    Feb 10, 2023 at 14:04
  • Are there nonclustered indexes on the Documents table that were also moved to the secondary filegroup? Feb 10, 2023 at 15:42

1 Answer 1

0

The backup command to backup single filegroup is this one:

--Back up the files in Myfilegroup1.  
BACKUP DATABASE MyDB
   FILEGROUP = 'Myfilegroup1'
   TO DISK = 'C:\MySQLServer\Backups\MyDB\Myfilegroup1.bck'

Add this for differential backup:

WITH   
  DIFFERENTIAL; 
GO

To have a good segregation of the contents be sure to have each table (clustered index or heap) in the same Filegroup of the secondaries indexes.

Also consider that the restore in quite complicated as you have to:

  1. After you restore the filegroup, you need to take a tail log backup of the restored database.
  2. Restore the lastest differential you may have to with norecovery.
  3. Restore all the tlog backups to with norecovery.
  4. Restore the tail log backup from step 1 to with recovery.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.