Automating SQL DB Backups

 SQL Server supports three types of backups viz.

1. Full Backup
2. Differential 
3. Transaction Log 

—For More details on Backups Click Here

Besides this SQL Server also have a feature called Maintenance plan, which allows us to schedule few routine administrative tasks which includes Backups as well.

We can  use these three types of backup types and Meaintenance Plan together to build effective disaster recovery mechanism as per our existing business scenario.

I have tried to present a scenario where we can recover data till 5 minutes before disaster.

Backup Strategy  for recovering data till 5 Minutes before disaster

Now, Lets see how this can be implemented in practice. We will start with an introduction to maintenance plan.

Maintenance Plan

—Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.

—

The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility. 

We can create maintenance plan from SQL Server Management Studio by navigating to Management Folder them to Maintenance Plans and then right click and select "Maintenance Plan Wizard".

This will open a window shown below.

Say, Next.

Select, Separate schedules for each task.

 

Now, Choose 

•  BackUp Database (Full)
•  BackUp Database (Differential)
•  BackUp Database (Transaction Log) 

Click, Next

 

Now, Maintenance plan wizard will create three sub plans, one for each task you have choosen.

         Each time you have to repeat almost same set of steps like. 

1. Select Database to be backed up.
2. Type of backup.
3. File where backup will be stored.   
4. schedule as we choosen to have different schedule for each subplan.    

         As Shown in screen below

1. 
   
    

After we have run wizard completely, we will customize all three subplans as described below.

For customizing, Newly created maintenace plan we have to go to management studio and refresh "Maintenance plan" folder so that it now shows our newly created Maintenance Plan. And double click on it.

  

Sub Plan 1: Full Backup

This Sub plan  will timestamp & copy existing backup file and do a full backup. 

 As can be seen the screen about shown two extra tasks compare to one you have.

This is because i have customized it, by adding

1. Execute Package Task
• This will call a SSIS package which copies existing backup file from Running Backup folder to Old backup folder and also timestamps it.

1. Execute SQL Task
1. Here we will write a SQL which keeps on executing till above mensioned SSIS package is running. Thus, making sure that we don't run full backup before existing backup file is copied to different location.