http://www.mssqltips.com/tip.asp?tip=1497

I think, just taking a differential backup is sufficient, after changing the recovery model from Simple to Full.

It all depends on your business needs (what is the amount of data loss your users will accept??) or SLA.I have some databases that the users will allow for one full day of data loss. Another environment will allow one hour of loss and yet another environment will tolerate 30 minutes of data loss. I perform full backups on weekends, differentials each night and log backups throughout the day. A nice mix that works for my environment.

Edit - If you are in full recovery mode, log backups are essential.

Hi,

As you will most likely be able to tell, I am a complete newbie at this...BUT I am a little confused about when you would even want to make the choice to use the SIMPLE Recovery model.  If FULL is the default and the recommended choice, and you would have to actually physically make the change to SIMPLE (and then back again), why not just leave it at FULL?  The logic escapes me.

SIMPLE is good for development and QA databases - there's less work on the DBA's part because the db engine will automatically truncate the transaction log based on database CHECKPOINTs.

If you have a production database, then you may want to consider FULL with log backups taken at periodic intervals. The amount of data loss that is acceptable is taken into consideration. At one site, I took them once an hour. At a another, I took them every 30 minutes.  In addition, you'll have to monitor disk space as the log grows.  If you don't take periodic trans log backups, the trans log will grow.

What is an acceptable amount of data loss for the user? What does your SLA state? I have some databases that I do a full backup each night and logs every 30 minutes. Other databases, I have a full each night ant differentials every 4 hours. The key point is, what is an acceptable amount of data loss?

Hi,

Okay, that makes sense.  The recovery model you use as your default depends on the type of database you are working with. 

As to the rest of your statement, if you are performing a trans log backup every hour then you must also be performing a FULL along with it.  If I am understanding this correctly, before you can perform a differential or trans log backup, you must first perform a FULL.  If you had a situation like this: FULL taken at midnight, differentials taken every three hours during the day, you would only be able to execute a trans log backup once per day (after the FULL at midnight).  Do you not need to perform as many trans log backups if you are executing differentials during the day?

Here is some clarification.

You have to always start with a FULL backup.  Once you have done at least one of full backup you really do not need to ever do a full backup again, but this is not a good practice.

Once you have completed a FULL backup you can then do either DIFFERENTIAL or TRANSACTION LOG backups.  Again transaction log backups can only be issued if the database is in FULL or BULK-LOGGED recovery.

The key here is the restore process.  To minimize the number of files to restore you can do the following. 

Restore the FULL backup in no recovery mode, restore the latest DIFFERENTIAL backup in no recovery mode and then restore all of your TRANSACTION LOG backups that were taken after the last DIFFERENTIAL.

So if you are issuing both differential and transaction log backups it is the restore process that is really going to get the benefit.  The differential backups really just help by cutting down the number of restores you need to do.

Another way you could restore your database even if you are using differential backups is to restore the FULL backup and all of the transaction log backups that have occured after the FULL backup.

To expound on Greg's post, here's a visual example of a FULL strategy I employed in the past where I backed up
the trans log every hour.   This particular company had business hours between 6am and 7pm, Mon-Fri.

Sunday                                          Mon            Tues                Wed                  Thurs               Fri               
======                                         ====          ====              ====                   ====            ====             

Complete                      7am       Log Backup   Log Backup     Log Backup         Log Backup   Log Backup     
DB Backup                   8am       Log Backup   Log Backup     Log Backup         Log Backup   Log Backup
                                    9am       Log Backup   Log Backup     Log Backup         Log Backup   Log Backup                                               .
                                     .
                                     .
                                     . 

                                  1am       Differential    Differential       Complete DB        Differential     Differential
                                                   Backup       Backup            Backup               Backup          Backup

I can't stress enough that you should perform frequent fire drills to make sure that your backup strategy is working... always be prepared for the worst.

I totally agree with aprato. Setup the appropriate backup strategy for your business. Full backups daily and log/differentials at the right intervals. Restoring should be as easy as possible (although when it does become necessary it's usually not painless) so the right sequence is important. I have a full backup each night and logs every thirty minutes, which based on business needs, allows for no more than 30 minutes of lost data. I have another system that is full backups each night and hourly logs (one hour of lost data is acceptable on this system). And as aprato stressed, test your backup files. It doesn't do you any good to say "I have a backup" if you cannot restore the data and know that it works........

This may be a few days late in coming but I just wanted to say Thanks for taking the time to answer my questions.  I will hopefully be able to return the favor and help someone else at some point in time, but until then it is nice to know I have somewhere to go with my confusion. :)