Subscribe To This Site

The Importance of SQL Server Database Backup

You may wonder why some much emphasis and importance is placed on SQL server database backup especially if you implement a high availability system with a great deal of redundancy built in or make use of a SAN? The redundancy built into a high availability system may provide protection or failover capabilities in the event of say a power failure (for one node in a cluster) or a single disk failure, they do not cover all possibilities of data loss. User deletion of data (accidental or otherwise), database corruption for a software or hardware failure and Natural disasters such as hurricanes can destroy entire data centres which can include all of your data. In any of these cases SQL Server database backups are needed to be able to recover the data. In a natural disaster scenario as described above you will need to have a backup in an offsite location.

Online, offsite backups for your IT infrastructure

System Failures

To help understand the need for backups will discuss specific examples where data can only be restored from a specific backup. I will then provide a real life example that experienced with a high availability 24/7 mission critical system in a previous job.

Hardware Failure

Although high availability systems cover many hardware failures, high availability will not cover all aspects of hardware failures.

Disk failure with no RAID � if you do not have RAID fault tolerance or you use RAID-0 disks to house your SQL Server data or log file then the failure of any disk drive will cause data loss. In this situation the hardware fault must be �fixed� and the database restored using a good backup
Physical Event � such as flooding or hurricane etc at the data centre which results in the hardware becoming damaged or destroyed, then an it is likely a completely new system will have to built and the database would have to restored from an offsite backup
Multiple component failures � if you are using raid and more than one disk fails at a given time then it is likely that a backup will be needed to restore the data.
User Error � a user (accidentally or otherwise) could damage/delete/destroy data in your database.

Software Failures

Although not as common as hardware failures there might be occasions when you need to restore your database as result of a software failure, this type of failures can be more disastrous than hardware failures. In the following scenarios you would need a good backup to recover the database:

Operating system failure: OS failure can affect the I/O system which can lead to data corruption on the disk. NOT ALL OS FAILURE CAUSE CORRUPTION � this is quite rare.
SQL Server Failure � as above if this happens then there is a possibility of data corruption � again this is very rare.
Accidental deletion or modification � wrong updates script run against the database.

The above scenarios are not common but definitely possible.

Although I have not experienced all of the above I have been in several situations where I have needed to restore a SQL Server database from a good backup. Unfortunately for many people it is not until you need to restore that you realise you do not have good backup at which point your data is lost! Could you afford to lose all of your company�s data?

The real life example below details one of the most important SQL Server restores I have undertaken.

Real Life Example � Database Restore

Background

In a previous SQL Server DBA position we were running one of several mission database applications on Windows 2003 cluster with SQL Server 2000 SP4. This was a two node active/passive setup. The disks were Direct Attached Storage on each node, the data file resided on a 500GB RAID 5 array and the log file was on 100GB RAID10 array this was identical for both nodes and data and log file was replicated between both nodes using geo cluster replication. (This was a temporary measure that was put in place until a new SAN was implemented)

The database was about 80GB at the time.

The Backup Strategy:

Full backup daily at 20.30 written to diskTransaction log backups on the hour every hour also written to disk

Nightly the full backup and the transaction logs were written to tape as an extra precaution the tapes were stored offsite at a separate location.

We put the backup strategy together after discussions with the business who indicated that an hours worth of data loss was acceptable.

The Disaster � multiple power failure

We suffered a multiple power failure to both data centres. The power failed in both data centres the power failed to both nodes in the cluster. Without going into detail about the electrical problem when the power was restored about 2 hours later the SQL Server cluster was brought back online but was marked as suspect.

The database was marked as suspect because the log file has become corrupt, a restore was needed.

The Restore

Microsoft�s SQL Server Books online suggests the following process to carryout a point in time restore:

You can restore a database to the state it was in at the point of failure if the current transaction log file for the database is available and undamaged. To restore the database to the point of failure:

1. Back up the currently active transaction log. For more information, see Transaction Log Backups.
2. Restore the most recent database backup without recovering the database
3. If differential backups exist, restore the most recent one.
4. Restore each transaction log backup created since the database or differential backup in the same sequence in which they were created without recovering the database.
5. Apply the most recent log backup (created in Step 1), and recover the database.

Unfortunately because of the log file corruption we were unable to carry out step one only able to restore to 1000, we lost 10 minutes of data � which was deemed acceptable by the business.

We restored the full backup taken at 2030 we then restored all transaction log backups taken since that time (14 in total) recovering the database once the 1000 transaction log backup had been applied.

I hope this article has provided you with enough valid reasons to consider implementing a suitable backup strategy for your SQL Server databases. If you would like gre-sqlserver-solutions.com to assist you in implementing a backup strategy then please contact us. Contact Us

ASK US A QUESTION - QUESTIONS ARE FREE AND WE'LL BE HAPPY TO HELP

SQL Server Recovery Models

footer for Backup page