Database Training Blog

The Full Recovery Model allows you to not only backup the database but also allows you to backup your transaction log.  Changing the Recovery Model of a database changes how information is kept in the transaction log.

Every SQL Server database has a Transaction Log.  The settings that are selected for recovery tell SQL Server how long to store committed transactions.

Every Insert, Update and Delete transaction that occurs is placed in the transaction log.  Changing the Recovery Model to FULL tells SQL Server to keep all committed transactions in the Transaction Log until there is a backup.  Once a backup has occurred, SQL Server will remove all committed transaction that have been added to the database from the log.  Using Full Recovery allows you to restore the database up to the minute of failure.

To select the Full Recovery Model option:

1. Right-click on the database, select Properties.
2. In the Database Properties Window, select the Options page.
   

Once the Recovery Model has been set you can then backup up not only a database but the Transaction Log.

To backup a Database

1. Right-click on the database.
2. Select Tasks.
3. Select Backup…
   
   

Before you do any secondary backup like a Transaction Log backup, you must do a full backup.

To do a Full Backup to disk:

In the Source section of the backup page:

1. Select Full Backup from the drop down Backup type.
2. Backup component is Database
   
3. Enter a name for the backup set.
   
4. Click the Add button in destinationto bring up the Select Backup Desination window then click on the Ellipse button to select where to save the file.
   
5. Select the Location and Name for the backup including the extension.
   
6. Click on the Options Page.
7. In the Overwrite Media section there are 2 options.
   • Back up to exisiting media set.
     You can either Append or Overwite the existing backup set
   • With Append there are multiple backups in the backup set.
   • Overwrite will back up to erase the old media and and then backup.
8. In this example let’s select Append.
   
9. The Reliability section allows you to have the backup:
   • Verify that it was successful
   • Perform a checksum bfore writing to media.  It tests to see if the data is corrupt.
   • If an error occurs, let the backup continue.

Even though SQL Server will check for reliability, restoring the backup to another server, if possible, is the best way to check that the backup was successful.

Since this is a full backup, the Transaction log section is unavailable and there is no tape drive attached to this server so the Tape drive section is also greyed out.

SQL Server has several choices for Compression:

1. Use Server Settings
   • Found in the Server Properties.  The default is no compression.
2. Compress Backup
3. Do not Compress Backup
   
4. Click OK. The backup starts.

In this post we looked at creating a Full Backup of a database.  Next time I will demonstrate how to do a Differential backup.

Backing up and restoring SQL Server 2008 databases is covered in our Maintaining a Microsoft SQL Server 2008 Database class.