0

I am looking into filegroup backups for a database. I want to have all the tables in the PRIMARY filegroup to be backed up and restored, while the tables in the SECONDARY filegroup are not. My queries work fine when the tables are just being edited, but when they are truncated, the table in the SECONDARY filegroup is restored.

When I do a "DELETE FROM" modification to the table in SECONDARY filegroup, it stays that way after restoring. But when I do a "TRUNCATE TABLE" on the same table, all the content comes back after the restore. I'm suspecting the table in the SECONDARY filegroup is still backed up by the PRIMARY one, but that doesn't make too much sense, as the delete line didn't get restored.

--- Create PRIMARY file group backup ---

Use master

BACKUP DATABASE database_name   
    FILEGROUP = 'PRIMARY'
    TO DISK = 'some_location' 

--- Restore PRIMARY file group --- 
 USE master
 RESTORE DATABASE database_name
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'some_location'
    WITH PARTIAL, RECOVERY, REPLACE

 --- Restore the other filegroup using a recovery-only database restore --- 
 USE master
 RESTORE DATABASE database_name
    FILEGROUP = 'SECONDARY'
    WITH RECOVERY
Roxanne Yang
  • 13
  • 4
  • I suspect that this is a function of truncation only modifying IAM/GAM/SGAM pages which live in the PRIMARY file group. Out of curiosity, where does the `truncate table` fit in the sequence of events above? – Ben Thul May 17 '19 at 19:41
  • @BenThul It's after the primary backup and before the primary restore. Could you share how I can move the pages you mentioned to the SECONDARY file group? Thanks! – Roxanne Yang May 20 '19 at 13:01
  • Being system pages, there is no way to move them out of the PRIMARY filegroup. When doing a piecemeal restore, you *can* apply transaction logs restores though which will bring those system pages to point-in-time to those restores – Ben Thul May 20 '19 at 15:20

0 Answers0