Most backup systems copy files or replicate low level data clusters. Only dedicated SQL backup tools are designed primarily to make backups of SQL databases. The traditional method involves mounting and unmounting SQL databases, to make a file copy of the database, a procedure which can be time consuming and requires advanced IT skills. More sophisticated solutions exist such as the internal backup and restore options contained in database management tools. Or even dedicated SQL backup tools such as the bcp utility for SQL Server 2008 R2 from Microsoft, or the independent SQL BackupandFTP utility. In any event, SQL data requires special care to be taken when the actual backup is made.
Once a SQL backup has been created, the next logical step is to secure this data image. This can be done with a file copy&paste onto a thumb drive, or burnt onto a CDROM or other portable media.
Obviously, the fastest and safest option is an automated off site data backup solution such as MrBackup which can deal with the SQL data management tools and then process the off site storage in one go.
The advantage of having a one-touch backup solution for SQL data is that the database management and off site data storage is handled in one go, without human intervention, which may result in failed backups due to finger trouble, forgetfulness or even simple, down right honest mistakes.
The risk associated with SQL database backups have been discussed previously on this site. An additional risk is typically associated with RAID systems: RAID will not necessarily achieve a usable SQL backup. In addition, RAID systems typically keep only one mirrored set of data, due to the sheer size of the data being RAIDed – which is contrary to the definition of backup.
Imagine a scenario where a RAID system runs on a server for a small business.
Data is RAIDed from the live server Hard Disk Drive to the RAID device continuously. In theory, as changes are made to the live data, every single data cluster is mirror to the RAID disk. On a Friday night in autumn, disaster strikes! The office is hit by a lighting strike. If the lighting had fried the server HDD, the system would have crashed and the RAID disk replaced in order to maintain the functionality of the system.
But, instead a brownout occurred – the server was damaged sufficiently to cause rogue processes to initiate on the server, but not to cause its collapse. These rogue processes then result in damage to physical hardware, and/or the data stored on disk is corrupted by such rogue processes – uncontrolled data read and write processes which damage and destroy the data. In the meanwhile, the RAID system keeps on capturing data from the server and replicating it to the RAID system, writing bad data to the RAID system.
Monday, the office resumes activities and capture new valid data to the system. Due to damage to the existing data, problems start occurring and reports do not add up. By Tuesday morning the situation is justifiable severe enough that end-users inform management that the system is presenting inaccurate and generally unusable reports.
Normal business activities continue unabated while management first dismiss, and then investigate user error reports.
By Wednesday the situation has become so severe that management call in IT technical support to investigate the discrepancies displayed.
By Thursday morning IT advises management that an incident has occurred, which has a detrimental impact on business critical activities and should probably be classified as a disaster. Disaster recovery procedure is implemented.
IT proceeds to remove the server HDD and restore the RAID disk in an attempt to return the system to full functional capacity.
By Friday morning it is clear that the restored system is still faulty and an investigation begins to determine if any true backups exist which can be used to restore the main system to full operational capacity.
At this time it is determined that the server can not be restored. The RAID system contains only a copy of the damaged data as it appears in the current damaged system. The RAID system had continue functioning, capturing ‘bad’ data from the live system and writing it to the RAID device.
Damaged data was written to the RAID disk from the moment that the main system was compromised, until all good data on the RAID device was replaced by bad data from the live system.
The RAID system, even if it accommodated a SQL database, contains typically only one version of the data: in this case, a damaged set of data. the result being that the main system is damaged and the data on the RAID device corrupted – this a simple replication of the bad data on the the main system – which leads to the ultimate question – what is available for restoration?
Ironically, if the lightning strike had fried the main server completely, the RAID system would have contained usable data to the point-in-time directly prior to the incident, and the RAID system would have saved the day.
A true backup system should contain multiple, discreet sets of the original data, from various points in time, allowing the main system to be restored almost instantaneously to a defined point-in-time.
The MrBackup default data storage scheme allows for at least the eight (8) copies of the live data, as well as three (3) more month end data sets, plus a one (1) year data set.
If MrBackup had been in place, and accepting the scenario above, the first suitable backup set prior to corruption which would have been used, would be the Thursday prior to the incident. If for any reason this set would not be usable, the backup set from the Wednesday prior to the incident would have been used, and so on. Failing which, data from the previous month end.
A true backup system, like MrBackup will allow for multiple, discrete backup sets, each one of which may be used to achieve a complete system restore to a predeterminable point-in-time: in this case, the Thursday prior to the incident.
In conclusion, even if a RAID system is in place, this does not constitute a backup system, and does not obviate the need for a true backup solution.
You may have RAID, but do you have a backup?