patternMinor
Change Tracking Retention from a backup?
Viewed 0 times
trackingretentionfromchangebackup
Problem
I have a backup of a SQL Server 2008 database with change tracking enabled on it. The retention period is 2 days. AutoCleanUp is set to true.
The database backup is 5 days old and has 2 days worth of change tracking information when it was backed up 5 days ago.
If I restore the database today, would I lose all change tracking information from the database?
The database backup is 5 days old and has 2 days worth of change tracking information when it was backed up 5 days ago.
If I restore the database today, would I lose all change tracking information from the database?
Solution
According to TechNet Change Tracking is not supported across a data restore - for not only the complexities you reveal in your question but more specifically because clients who might be keeping track of version info separate from the server will now be out of sync. Read the technet article for more info.
If client version tracking doesn't apply to your scenario and you really need the change history you can most likely keep the server from auto-cleaning it after a restore. The reason I say this is that the auto_cleanup task is a background thread that runs every 30 minutes (according to Microsoft). So immediately after a restore you should have a small window of time to turn the auto_cleanup setting off and maintain any change tracking retained within the table. But yes, if you leave auto_cleanup on it will be gone the next time the task runs.
As for your follow up question.
I can find nothing official but my gut says it's got to be a 24 hour period. Making a change at 11:30PM shouldn't disappear an hour later (12:30AM) if retention is set to 1 day.
I have no documentation to back this up - just my gut on that one. But I will have proof in 24 hours as I am testing it as I type :)
Follow up question test: UPDATE
My test has proven inconclusive. I set retention for 1 day and 48 hours later and the change tracking info has still not been removed. I tested a different table with a 1 hour retention period and it was removed 86 minutes later. So I can not definitively answer if 1 day period is 24 hours or a full calendar day.
Microsoft documentation says that retention period refers to the minimum amount of time it should keep the data. So the one thing my testing has proven is it doesn't mean it will be deleted the very moment that time period elapses.
If client version tracking doesn't apply to your scenario and you really need the change history you can most likely keep the server from auto-cleaning it after a restore. The reason I say this is that the auto_cleanup task is a background thread that runs every 30 minutes (according to Microsoft). So immediately after a restore you should have a small window of time to turn the auto_cleanup setting off and maintain any change tracking retained within the table. But yes, if you leave auto_cleanup on it will be gone the next time the task runs.
As for your follow up question.
Paraphrase: Is retention period of 1 day a 24 hour period or a calendar day?I can find nothing official but my gut says it's got to be a 24 hour period. Making a change at 11:30PM shouldn't disappear an hour later (12:30AM) if retention is set to 1 day.
I have no documentation to back this up - just my gut on that one. But I will have proof in 24 hours as I am testing it as I type :)
Follow up question test: UPDATE
My test has proven inconclusive. I set retention for 1 day and 48 hours later and the change tracking info has still not been removed. I tested a different table with a 1 hour retention period and it was removed 86 minutes later. So I can not definitively answer if 1 day period is 24 hours or a full calendar day.
Microsoft documentation says that retention period refers to the minimum amount of time it should keep the data. So the one thing my testing has proven is it doesn't mean it will be deleted the very moment that time period elapses.
Code Snippets
Paraphrase: Is retention period of 1 day a 24 hour period or a calendar day?Context
StackExchange Database Administrators Q#3755, answer score: 4
Revisions (0)
No revisions yet.