Home > sql server 2008 > Change Tracking Retention from a backup?

Change Tracking Retention from a backup?

July 8Hits:1

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?


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.

Related Articles

  • Change Tracking Retention from a backup?July 8

    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

  • SNES Bung Game Doctor, add RAM retention modification (battery backup) August 18

    Firstly I'd like to say that this doesn't seem like the right "forum" for this question as it actually doesn't relate directly to the development of games. As this is my first post on 'game development', I cannot include more links - sorry. I re

  • Backup policy for these parts of infrastructure?August 16

    HI, I am defining a backup policy for the following. Given the characteristics of the following (in my scenario), what would be the ideal backup plan for each (retention rate and backup frequency)?: Active Directory (including the secondary DC) - AD

  • What is the best backup solution for VMware Infrastructure system that hosts a wide variety of VMs?February 23

    In a situation where you are running: VMware Infrastructure 4.x with multiple hosts Over 150 VMs with a wide variety of operating systems (Linux in a half dozen distros, Solaris, every MS version, etc.) in multiple languages with almost every mix of

  • Small Business (70GB) Backup SolutionsAugust 24

    I'm helping out a small business with their IT needs and a few questions have been raised over the quality/dependability of their backup system. Currently they have two servers in use with one DAT72 tape drive for both servers. A total of 14 backup t

  • How to backup an HFS+ partition using UbuntuMarch 27

    i need to mount via AFP an HFS+ partition (Mac) and Backup it to an external drive, using Ubuntu. The problem is that in Nautilus i can 'view' the drive using afp:\myserver_ip but when i launch a backup tool, like gRsync or LuckyBackup or anyothers,

  • Using Windows Azure storage for backupOctober 8

    I am currently looking at Windows Azure blobs as an option for backing up archive data. I want to be able to upload files from an external windows machine via the internet but I don't know enough about Windows Azure storage to make a decision. Some o

  • Replace large file with placeholder so backup program won't believe it's changed and will keep it uploadedDecember 5

    I'm using a program which will back everything up, but if you delete a file, it'll only store it on their servers for 30 days. I'm just wondering if it would be possible to replace a file with a 1kb version, but still have the computer believe it's t

  • changing iSCSI block storage backup solutions, does CBT need to be reset?April 2

    VMware vSphere 5.1, iSCSI block storage, old backup solution uses Changed Block Tracking (CBT) for incremental backups. New backup solution also uses Changed Block Tracking (CBT), but the current CBT info pertains to the old backup solution as the ne

  • Change SQL Server backup destination while retaining server's knowledge of locationSeptember 19

    I currently have a number of SQL Server (2008 R2) databases that are receiving tlog and differential backups. The backups are currently being stored on a NAS that is going to be repurposed. I need to start sending the backups to a new NAS that has a

  • Designing an aggregation schemaAugust 12

    The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it might give me some insight on how to solve my own si

  • Mounting truecrypt system partition copied to USB driveNovember 22

    I'm rather baffled by this one. I am using Windows Vista and am running truecrypt version 6.2a system encryption with pre-boot authentication for the C: only. I have a D: which is not encrypted. Using knoppix I have booted my machine into Linux and u

  • What is the best place to back up files for disaster recovery?November 23

    In my case, its about 1 or 2 terrabytes of images, etc. for my design firm. Any ideas? Thanks! --------------Solutions------------- At the agency where I last worked, we setup the art department with a four tier backup solution: NAS Device - Hot Back

  • SQL Server: proof of sensitive data really deleted by table dropMay 28

    I was asked to drop some tables on a Company SQL 2005 Server and provide proof of deletion because of these tables contains sensitive data. Is it possible? Thank you all for help --------------Solutions------------- No - especialy as a drop does NOT

  • Backing up a SQL Server 2008 R2 Database without stored proceduresMarch 29

    Is it possible to back up a SQL Server database, but only back up the Table structures and content (i.e. not include the stored procedures, views etc?) The scenario: We have a data centre hosted product with one unique database per customer. For many

  • Electricity off while shrinking database, how can I recover my database?June 3

    While i execute shrink database, the electricity has been gone down, I can't access my database now, it is (In Recovery), how can i recover it?? Note: it is a very huge database: 25 GB. EDIT: My question on serverfault.com: http://serverfault.com/que

  • 30 Innovative Cloud Web Apps

    30 Innovative Cloud Web AppsJune 6

    The cloud has forever changed how users store data, use apps and collaborate on projects. No longer do you have to risk data loss, or require all users to have the same software installed on their local machines. Instead, using web apps with cloud pr

  • ispconfig in xen domU

    ispconfig in xen domUAugust 27

    I would like to create a centos as domU and install ispconfig3. however, i do not want store mails, web files, etc in domU but in nfs server (if domU crashed, it would be rather difficult to recover them). pls suggest which directories should be resi

  • How to deploy new instances of the same application (on 1 server) automatically? November 17

    I'm working on a SaaS application where each customer runs its own version of the application. All the application instances currently run on a single server. This works quite well for us (we need less resources in total). The application doesn't use

  • Greyed out songs when trying to add from itunes to iphone?May 2

    I just got an iphone 4 and downloaded the most recent version of itunes onto my computer because i had a version that was too old. It told me i needed to convert the songs to create an AAC version of my library in order to add them onto my iphone, wh

Copyright (C) 2017 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.517 s.