1

This question is about the SQL Server 2019.

Scenario: I have a source table, which is partitioned by MonthId (YYYYMM). I also have an archive table of identical structure (except it lives in a different schema).

As the time goes by, the older records from the "source" table are moved to the "archive" table using Partition SWITCH. At the same time all of these records remain on the same filegroup because of the requirement that "Source and target tables must share the same filegroup" https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191160(v=sql.105)

This means that the "cold" records must continue to be backed and restored along with the "active" records. I.e. I cannot take advantage of the capability afforded by the filegroups in terms of being able to backup/restore only the desired filegroups rather than the entire database.

Is there any way to have your cake and eat it too? In other words, to archive "old" records to another "archive" table AND avoid having to repeatedly backup/restore these static records along with the rest of the database?

SQL_Guy
  • 333
  • 4
  • 15
  • Well you have to back up any changed filegroups at the same time, the only way to not bac them up is to set them readonly. But then you can't move any data into them, so what do you want to happen? Do you want to merge the archived partitons together, or just move them to a different filegroup? And how often are you backing up vs how often are you archiving? Maybe take a look at this https://dba.stackexchange.com/questions/106004/moving-a-partition-of-partitioned-table-to-other-file-group-sql-server – Charlieface May 19 '23 at 17:27
  • The archived partitions need to remain archived for some time (a few years) - we want to preserve the ability to "unarchive" data. After the expiration of what I would call "holding period", the data will be permanently removed (also conveniently done with partition SWITCH). The archiving will happen periodically, let's say monthly. The backups and restores happen much more frequently on a daily basis, but also on "as needed" basis. – SQL_Guy May 19 '23 at 17:39
  • 1
    FWIW, you don't need to do a SWITCH to permanently remove data - TRUNCATE TABLE supports partition-level truncation. – Ben Thul May 19 '23 at 20:54
  • Have you considered not using SWITCH? What's wrong with keeping the hot data in the main database and moving the cold data to a seperate archiving database that's backed up less frequently? – AlwaysLearning May 20 '23 at 01:59
  • @AlwaysLearning, well the point of the SWITCH is obviously to make the process fast. Our OLTP database is very busy and we often have issues with blocking and deadlocks. "Moving" data without the SWITCH effectively means doing INSERT/SELECT on an archiving database followed by a DELETE in the "main" database , which is a blocking DML operation. Therefore, we would like to avoid this. – SQL_Guy May 21 '23 at 18:38
  • 1
    I think you need to have a separate file group for each partition: https://www.sqlshack.com/how-to-automate-table-partitioning-in-sql-server/ – Alex May 23 '23 at 01:32

0 Answers0