Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Also, when does the clock starts for figuring retention period? Is it 12.01 am? And any change tracking info logged at 11:59 PM two days ago will be deleted at 12:01 AM today if retention period is 2 days? –  kudlur Jul 8 '11 at 21:15
    
Test results inconclusive... sorry. See my updated post below. –  RThomas Jul 15 '11 at 23:17
    
Changing tracking and doing a data restore: msdn.microsoft.com/en-us/library/cc280447.aspx –  Shawn Melton Jul 16 '11 at 1:51
add comment

migrated from stackoverflow.com Jul 11 '11 at 0:45

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

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.

share|improve this answer
    
Great article on change tracking and features: databasejournal.com/features/mssql/article.php/3824196/… –  RThomas Jul 12 '11 at 21:53
    
Part two of that article: databasejournal.com/features/mssql/article.php/3829861/… –  RThomas Jul 12 '11 at 21:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.