SQL Server: Setting Up Transactional Backups

Heya guys, it’s been awhile. Welcome back to the first SQL Something in 2 weeks. I sincerely apologise for this. The first week I came down with an awful fever and then the second week was really busy workwise. 

Sigh, still feeling pretty bad about not updating though. Bleh.

Anyhoo let’s begin without further ado. This week, we’re gonna take a look at setting up Transactional Backups.

In a nutshell, transactional backups are backups that are taken of only the changes that have occurred since the last backup/transactional backup.

PLEASE NOTE: you need to have an initial full backup first before you have your first transactional backup. Please schedule a full backup sometime before you schedule your first transactional backup to occur.

Now imagine that you have a full backup occurring every night and transactional backups occurring every 15 minutes. Woops, something goes wrong and you need to do a restore. If you only had the once a night backup then you would lose all your data between that full backup and the time of failure. Luckily however, you also had transactional backups occurring! Worst case scenario you might only lose about 14 minutes as appose to hours. Stand proud. You are a hero, at least for the day.

Alright let’s get you to that point.

Setting up Transactional Backups

Open Management Studio and expand the “Management” tab. Right click “Maintenance Plans” and choose “Maintenance Plan Wizard”.

Fig. 1: New Maintenace Plan needed

Define your schedule according to your needs. If you have a DB that is constantly being written to and every bit of data is important then a more regular schedule might be more suited to you. If data writes are not very often then, the schedule can be a little more spaced out.

PLEASE NOTE: you need to have an initial full backup first before you have your first transactional backup. Please schedule a full backup sometime before you schedule your first transactional backup to occur.

Click Next.

 

Fig. 2: Defining a schedule. Example every 15 mins in this case.

Now we define the type of backup job;. Select “Back Up Database (Transaction Log)” as shown in the diagram. Click Next twice.

Fig. 3: Selecting the plan type.

Select database(s) you wish to run transactional backups for.

Fig. 4: Selecting the database(s)

Select if you wish to have a report made and where. Also, if you wish the report to be emailed. Entirely optional. Click Next then click finish.

Fig. 5: Creating reports.

Poof. There you go. Transactional Backups have been set up. This should ease your mind a little more. :-) 

Remember, at the end of the day, you are awesome. Setting up precautions make you more so.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)