I would like to create a job in SQL that does the following.
I would like to run stored proc.
It would create an archive table if it doesnt already exist using the same structure as the table I will be truncating from.
Then the table I am truncating would pull data from the last 3 months if there was 3 months worth of data and move it to the archive table.
It would truncate the archive table as I only want it to have 3 months worth of data.
Then it would truncate the table.
Then I think I would want to run some type of shrink and potentially something to fix fragmentation as well.
I am guessing this would just execute from a stored proc and run on a SQL agent...I guess every 3 months....since that would be the timing I am looking for.
Please help. I would run this at night and am assuming once this was in place it would be a fairly quick job.