0

I am running a daily Job that updates a fact table. Before copying the new data I create a backup in a table that is identically and only creates a timestamp additionally.

The main purpose for the backup table is to identify potential issues and to see an earlier version.

Now, I want to limit the number of backups available so I was considering deleting older backups that I don't need any longer.

I would like to keep the following backups:

  • Everything in the last 14 days
  • The last one before 21 days, 28 days
  • Afterwards monthly
  • Afterwards yearly

What is the most elegant way to do this in t-sql?

A challenge I forsee is also that for whatever reason not a backup on a specific day is available but for example for -1 or -2 days.

Thanks for any advices.

I tried a delete script that would delete the records between the dates that I'd like to keep.

Peter Jackson
  • 23
  • 2
  • 1
    Please update your question with the script that you have managed to write and ask a specific question about that script. “Most elegant” doesn’t really mean anything - what are your actual parameters for determining an optimal solution (speed, reliability, logging, etc)? – NickW Feb 23 '23 at 13:16

1 Answers1

0

According to your description and without more information, it seems in first glance that you architecture is not good.

The main purpose for the backup table is to identify potential issues and to see an earlier version.

option 1 (best option probably)

This is why SQL Server provide build-in solution for such requirement.

You should simply use Temporal table which will let you support for providing information about data stored in the table at any point in time.

https://learn.microsoft.com/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

Option 2

Instead of creating multiple tables you should create a single table and attach the new data each time that you want to backup. Your new data can be added as a new partition which make it very simple to manage.

In this case, you can add column with the date of the backup action which will be used for the partition function.

Option 3 (worse option)

Keep the same architecture you do now. In the same job that you backup, add a step to DROP old tables. Use a name for the table which include the the date of the backup (for example format like Table_yyyymmdd), so you will be able to find the table fast for the DROP action (simple calculation of the current date in order to find the string for the names of older tables o drop)

Ronen Ariely
  • 2,336
  • 12
  • 21