Backup and its strategy

 

I get lot of mail from my friends to write something about different tropics. But sometimes it is not possible due to my heavy work load. But I tried to answer them as I can.

In this article one of my friends ask me to write something about different types of backup. So in this article I am trying to illustrate some points related to it.  I am searching the search engine and find some useful tropic related to it. Here I am gathering some tropics to provide small but complete solutions related to backup and its strategy.  

When we are going to take some database backup, we find two options in backup type.

1.    Full database backup

2.    Differential database backup

3.    Transaction Log backups

 

 

Full database backup

 

A full backup makes a full copy of the database. This type of backup is most reliable, but it is also the most time and resource consuming. Even so, users can continue connecting to the database, reading and changing data while full backup is in progress. Full backups take longer than any other type of backup, but they're also easiest to recover from as long as no transactional activity has occurred since the last full backup.

Differential database backup

A differential backup records changes that have occurred in the database since the last full backup. Differential backups are faster and smaller than full backups. However, in case of a disaster the full backup must be restored prior to restoring any differential backups. Suppose you take a full backup every 12 hours (12AM and 12 PM) and a differential backup every 4 hours. Further suppose that the database gets corrupted due to a hardware failure at 4:15PM. You must restore the full backup from 12PM first, followed by the differential backup from 4PM. In this scenario, if you weren't taking transaction log backups, you would only lose transactions that occurred from 4PM to the time when database became corrupted.

 

Transaction Log backups

 

Transaction Log backups let you backup transactions that have occurred since the last full or differential backup, or since the last time a transaction log backup was taken. Transaction log backups are not supported by the SIMPLE recovery model. Transaction log backups can only be restored after a full backup and differential backups (if any) have been restored. Transaction log backups impose less overhead on the server than full and differential backups; therefore, transaction log backups should be taken more frequently. Although transaction log backups take the least amount of time to generate, the total time required for recovering a database using such backups is the longest; you must restore the full backup first, then differential backups (if any) followed by all transaction log backups taken since the last differential backup.

 

Backup Strategy

 

#	Item	Steps
1	Determine What is Needed	Before you begin implementing your backup strategy you need to better understand what you are trying to protect, how much data you need to recover and also whether the data can be recreated easily or not. To get started you need to ask yourself a few questions such as the following:  How much data can be lost? A day, one hour, a week, none... What kind of processing occurs? Transaction, batch loading, reporting, a combination... This data be easily recreated if there is a failure or it is not the only source of this data? Do you have very big batch loads that take considerable amount of time or load a lot of data? Based on the answers to these questions you can determine the proper recovery model for your database as well as the proper backup types and backup schedule.
2	Recovery Model	Based on the answers to the questions in item #1 you can determine the recovery model for your database. How much data can be lost? A day, one hour, a week, none... If your answer is less than a day or none you should consider the Full recovery model. If your answer is a more than a day then you could use the Simple recovery model (this is assuming you are doing full backups every day). What kind of processing occurs? Transaction, batch load, reporting only, a combination... If your answer is transaction or a combination then you should consider the Full recovery model. If your answer is reporting only you could use the Simple recovery model (this is assuming this data is loaded from another source and can be easily recreated). If your answer is batch load you could use the Simple recovery model (this is assuming that you can reload the batch data without losing any data). This data be easily recreated if there is a failure or it is not the only source of this data? If your answer is No than you should use the Full recovery model. If your answer is yes then you could use the Simple recovery model (assuming that it is not that hard to recreate the data). Do you have very big batch loads that take considerable amount of time as well as load a lot of data? If your answer is No than you should use the Full recovery model. If your answer is yes then you could use the Bulk-Logged recovery model.  Depending on how you load your data you can minimize the logging in the transaction log and therefore have smaller transaction log backups. The only drawback is that if there is a bulk-logged activity in a transaction log backup you are not able to do a point in time recovery for the transaction log backup file that has the bulk-logged operation. If your answers include Full for any of these questions you should use the Full recovery model.  The only difference would be the answer to the last question where you could use Bulk-Logged instead of Full. Note: it is also possible to change your recovery model based on different processing, but you need to ensure your backup process follows any of these changes so you do not potentially loosed important data.
3	Select Backup Types	SQL Server offers many different types of backups such as the following: Full - backups entire database Differential - backups all changes since last full backup Transaction Log - backups transaction log for all changes since last transaction log backup File - allows you to backup one data file Filegroup - allows you to backup an entire filegroup Based on the recovery model you selected you should follow a plan such as the following: Full or Bulk-Logged Recovery - Full backups and transaction log backups Simple Recovery - Full backups In addition to doing the above, you can also introduce Differential backups in between your Full backups.  This is helpful if you do a lot of transaction log backups during the day, so that when you need to restore you can just restore the full backup, the latest differential backup and any transaction log backups after the differential backup. Other options include doing file or filegroup backups for very large databases.
4	Backup Schedule	The next thing you need to decide is when to schedule your backups.  The most intense backup is the full backup since it needs to read the entire database and write out the entire database.  Since this activity is disk I/O intensive the best time to do this is at low peak times, therefore most people run full backups during off hours. Here is a sample schedule, again this would be based on what you determined to do in step 3: Full Backups - midnight Differential Backups - every 3 hours Transaction Log Backups - every 1 hour
5	Backup Process	SQL Server offers many built in options to backup your database such as: Maintenance Plans Using Enterprise Manager (2000) or Management Studio (2005) Using sqlmaint.exe Writing T-SQL code using the BACKUP command
6	Document	As with all administration activities you should document your backup procedures and the criteria you will use to determine the recovery model as well as the backup types and backup schedule.  It is much easier to have a defined plan, so when a new database is created you can just follow the steps that have been outlined instead of having to figure this out each time a new database is introduced.
7	Backup to Disk	The fastest way to do SQL Server backups is disk to disk.  I guess doing the backup to memory would be faster, but this doesn't give you a permanent copy that can be restored or archived, plus the option doesn't really exist.  There are a lot of backup tools that allow you to go directly to tape, but it is better to have the latest backup on disk so the restore process is faster instead of having to pull the file from tape.
8	Archive to Tape	Once the backup has been created on disk you should then archive to tape for long term storage. These archive copies are not used all that often, but they do come in handy when you are doing some research or an audit and you need to get the database back to the state it was at some point in the past.
9	Backup to Different Drives	As mentioned above the backup process is a disk I/O intensive activity.  Therefore you should try to separate your disk reads from your disk writes for faster I/O throughput. In addition, it is better to have the backups on a physically separate disk therefore if one of the disks dies you don't lose both your data file and backup file.
10	Secure Backup Files	In a previous tip we talked about how native SQL Server backups are written in plain text and can be opened with a text editor and therefore the data could be comprised.  Based on this you need to ensure that your backup files are written to a secure location where only the people that need to have access to the files have access. Also, this ensures that the files are not tampered with or accidentally deleted and therefore unavailable when you need them.

 

Hope you like that.

 

Posted by: MR. JOYDEEP DAS