[cisco-voip] Restore CDR records off backup

[Robert]

On Thu, 2009-04-16 at 13:24 -0500, Darren Ducote wrote:
> Currently we only keep 30 days worth of CDR records, however is there
> a way to restore CDR records and look at them from a backup without
> interrupting a production system? What is the best way to go about
> this if I can get the backup.

This may not be the solution for everyone, but I've done this for quite
a while now...

I have a script that runs daily (around 4AM by default) that reads in
all the CDR records for the previous date and writes them to an offline
MySQL database. As of this writing, the live CallManager database goes
back to Feb 23, 2009, but the archive database goes back to Apr 18,
2007. The live database has just over 1.5M records, whereas the archive
database has 21.7M records and I never have to worry about finding the
data range and restoring a particular backup or any of that. I just
query the database. 

In practice, almost everything I have to search for routinely is recent
enough to be on the live database, but the archive is excellent for
historic traffic reporting, such as how many calls were terminated to a
given provider in 2008 or various on-net call counts, such as number of
LD minutes *not* spent in a given quarter, etc, all of which would not
be possible on the live system.

One pseudo-interesting caveat has to do with DNs and history. The person
sitting at 2004701 can change over the course of a few years *and* a
give person may changed duties, branch offices, etc. Without the context
of DN assignment history, any personal calling history must be analyzed
carefully.

As my final aside (on this subject, anyway), the start date of April 18
2007 was precipitated by the relaxed dismissal of a temporary assistant.
He was told early in the day that it would be his last day, though
nobody told me, so I had no cause to revoke his access. During the day
sometime, he logged on to the publisher and wrote a query that deleted
all CDRs up to midnight of the previous day. He was smart enough to
assume that most CDR searches I might need to do would probably be from
the same day. By the time I'd discovered it, there was a huge gap of
history missing between the quarterly (or so) offsite backup and the
current rotating daily backups.

Sluggy!