I Need To Make A Change With My IT Support! Call (225) 706-8414

Setup Transaction Log Backups in Microsoft SQL Server 2005

You want to perform transaction-level backups of a Microsoft SQL Server 2005 database(s). Generally, you do this because you want frequent backups of the database, e.g., a backup every hour, but a full backup every hour would be expensive in terms of performance. A transaction-level backup however would not heavily impact the SQL server.

Solution

SQL Server can be configured to do this by creating two maintenance plans: 1 to purge/perform a full backup once every so often and then 1 to perform a transaction log backup frequently. In our example, we’ll perform a full backup once a day and a transaction log backup once an hour. The full backup is necessary: The transaction log backup is incremental based on the last full backup.

Note: Don’t fall into the trap of creating this plan and then having another sysadmin perform a full backup of the database without letting you know. If that happens, the transaction log backups you have will be based on that sysadmin’s full backup, not yours, making your transaction log backups unusable.

Note: When setting up this plan, be very careful if you choose All Databases. You may find that you fill up the disk faster than you expected. Test, test, test.

  1. Start SQL Server Management Studio.
  2. Click on <SERVER>->Management->Maintenance Plans.
  3. Be sure to review the backup plans–don’t write a new backup plan that conflicts with another one. Refer to the note above!
Create the Purge/Backup Plan – Backup_Full
First, we need to create a plan that will delete the old full backup from disk and create a new one. You could leave the old backups if your disk has infinite capacity. If you don’t have that type of disk, then you need to purge the old backup.
  1. Right-click Maintenance Plans->New Maintenance Plan
  2. Name the plan “Backup_Full”.
Now let’s create the initial delete task.
  1. In the Maintenance Plan Tasks, click Maintenance Clean Up Task.
  2. The Maintenance Cleanup Task dialog will appear.
  3. For “Delete files of the following type”, choose Backup Files.
  4. For File Location, choose your backup directory.
  5. For File Extension, use “bak” to delete the full backup.
  6. Be sure to enable “Include first-level subfolders” since our backup plan below will create subdirectories for each backup.
  7. For file age, choose 1 Hour(s).
  8. Click Ok.
Second, repeat the above, but for File Extension use “trn”.
Third, we need to create a backup job.
  1. Click on Back Up Database Task.
  2. Set the backup type to Full, choose your databases, and ensure you enable Create a Sub-directory for each backup.
  3. Click Ok.
Now, connect the three jobs so that the go in the proper order. You don’t want to perform a new full backup and THEN delete it. Notice the order shown.

Finally, schedule the job. In our example, we’re running this once a day at 2:00 am.

Be sure to click the X for the plan and save the plan, otherwise all of your work is lost.

Create the Transaction Log Backup Plan – Backup_TransLog

Finally, we’ll create a maintenance plan that runs once per hour to run the transaction log backup.

  1. Right-click Maintenance Plans->New Maintenance Plan
  2. Name the plan “Backup_TransLog”.
Now let’s create the backup task.
  1. Click on Back Up Database Task.
  2. Set the backup type to Transaction Log, choose your databases, and ensure you enable Create a Sub-directory for each backup.
  3. Click Ok.
Next, set the maintenance plan to run once an hour.
Kick Off the Jobs
You’ll want to manually execute Backup_Full and then Backup_TransLog to get things rolling. Be sure to validate results!

Concerned About Cyber Attacks?

CLICK HERE >

Want to Migrate to the Cloud?

CLICK HERE >
Office 365

Ready to Experience Microsoft Office 365?

Want the latest IT news directly in your inbox? Subscribe now!