"What I'm trying to say is when you buy a box marked guranteed, all your getting is a guranteed piece of shit. Hey if you want me to take a dump in a box and mark it guaranteed, I will, I have spare time. But for your daughter's sake, for your company's sake, you might wanna buy a quality product from me." - Tommy Boy
The reason warranties were invented is because its a fact of life that stuff breaks. The important thing for you, as a DBA, is to have a plan to make sure that when something breaks, like you lose a RAID array, you can make your company's data accessible as quickly as possible while returning it to a state as consistent as possible to just prior to the failure. You can achieve this by having your database set to the appropriate recovery model and making sure, not only, that you are preforming routine backups but that those backups are good backups.
Recovery Model
There are three available recovery models in SQL Server: Full, Simple, and Bulk-Logged. The Model you use will determine how the transaction log is used and what options you have for backup and restore.
Full Recovery Model - Using the full recovery model, SQL server logs all database transactions to the transaction log. This is the model you want to choose if your data recovery is a high priority for you because it offers you the most options for backup and restore. Since every transaction is logged in the transaction log, not only can you restore to a full backup but you can restore to any point in time since a full backup.
Simple Recovery Model - Like it's name this is the simplest option. It only gives you a few backup options and the transaction log is truncated whenever a checkpoint is issued. This model is useful if you have a read-only database or the database is a backup or dev database.
Bulk-Logged Recovery Model - This model is very similar to the full recovery model except that bulk operations aren't logged to the transaction log and you can't do a point-in-time restore.
Backing up Your Database
Now that you have your recovery model set you need to know about the different backup operations that you can perform with SQL Server. If you want to backup a database you need to be a member of one of three fixed roles: sysadmin, db_owner, or db_backupoperator. Remember, it is always best practice to give each user the minimum amount of permissions to do their job. If you have someone whose job it is to only backup your database you don't want to give them owner or sysadmin rights. There are three different backup methods that we are going to talk about: Full, Differential and Transaction Log.
Full Backup - A full backup does just what you think it would, it backs up everything you would need to make a full restore of the backup to the point in time that you took the backup. This includes: the database files, objects, data, and a portion of the transaction log. The backup causes a database checkpoint to occur so that the committed transactions in the transaction log get written to the database file. It also makes a note of the current log sequence number(LSN) so that it can grab any activity that was committed after the backup started.
Differential Backup - If you have ever done file system backups you are familiar with the term differential. it means all the changes since the last full backup. Differential backups only backup the changes that have been made since the last full backup. Since there is less data to backup the process runs much faster. This would be useful if you wanted to make a backup in the middle of the work day with minimal impact on the system. You must have the prior full backup to make use of the differential backup.
Transaction Log Backup - The transaction log backup will backup up the transaction log activity that has occurred since the last backup of any of the three kinds (Full, Differential or Transaction). When it backs up the transaction log it truncates the inactive portion of the log reclaiming the disk space that was taken up. If you are familiar with file system backups these are like incremental backups in that they are useless unless you have the last full backup AND all the transaction log backups since the last full backup.
You should become familiar with all three backup methods so that you can figure out the best way to provide your environment with maximum data reliability while keeping performance as high as possible and minimizing your restore time. A good backup plan could involve all three methods being used together. For example, you could do a nightly full backup and then do transaction log backups every 15 minutes and differential backups every four hours. Then if you had a failure at 3:45 you would only have to restore a maximum of nine backup files (Nightly full, 12pm incremental and the 7 transaction log backups since noon) and a tail log backup to get the server back up and running.
I final thought, I once overheard someone say "It's not a good backup until you have restored it". So not only should you be backing up your databases you should be testing those backups on a regular basis to make sure that you can recover if you have a failure. Backups are just one half of a good disaster recovery plan.
Hopefully I have given you a good overview of SQL Server backup. Next time I am going to write a post about server and database security, it's a subject that I am a bit week in and I think doing the research for the article will really help me. Once again I am going to make the claim that I will stick to a two week schedule but we all know how that is going so far.
Hasta Luego,
RutherfordTX
