Backup Strategy for recovering data till 5 Minutes before disaster.

Backup Strategy  for recovering data till 5 Minutes before disaster

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

Prerequisites:

For understanding this topic you should have good knowledge of following topics

1. SQL Server Backup Types.
2. Maintenance Plan  

Follwing following steps to implement above mensioned backup strategy.

Step 1: Create Backup directories.

• Create backup folder with appropriate name. Say Daily DB Backups
• Under this folder create tow more folders with names
•  Old Backup
• Running backup

Step 2: Run Maintenance Plan wizard to create Maintenance Plan named "Backup" with three subplans as described in subsequest sections.

• Sub Plan 1: Full Backup
• Sub Plan 2: Differential Backup
• Sub Plan 3: Transactional Backup

Step 3: Restart "SQL Server Agent".  

Sub Plan 1: Full Backup

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

This sub plan will have three Tasks

• 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.
• Click here to know more about creating SSIS package for copying files.

•  Execute SQL Task

• 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.
• Click here for sample query.

• Backup Database Task
• We will configure this task to take full backup of database to specific file with name "Sample_RunningBackup.bak" under "Running backup" folder created in step 1 and overwrite existing backups.
• Click here to know more about Backup Database Task.

Finally, Schedule this Sub Plan to occur every day after  every 1 Hour as shown in screen below.

Sub Plan 2: Differential Backup

This Sub plan  will perform differential backup and also makes sure that full backup is not running at same time.

 This sub plan will have two Tasks

•  Execute SQL Task

• Here we will write a SQL which keeps on executing till "Sub Plan 1" is riunning. Thus, making sure that we don't run Differential backup while Full backup is still running.
• Click here for sample query.

• Backup Database Task
• We will configure this task to take Differential backup of database to same file with name "Sample_RunningBackup.bak" under "Running backup" folder created in step 1 but this time we will append new backup to existing backups.
• Click here to know more about Backup Database Task.

  Finally, Schedule this Sub Plan to occur every day after every 15 Minutes as shown in screen below.

Sub Plan 3: Transactional Log Backup


This Sub plan  takes transactional log backup when Full or Differential log backups  are not running. 

This sub plan will have two Tasks

•  Execute SQL Task

• Here we will write a SQL which keeps on executing till "Sub Plan 1 & 2" is riunning. Thus, making sure that we don't run Transactional Log backup while Full/Differential backup are still running.
• Click here for sample query.

• Backup Database Task
• We will configure this task to take Differential backup of database to same file with name "Sample_RunningBackup.bak" under "Running backup" folder created in step 1 but this time we will append new backup to existing backups.
• Click here to know more about Backup Database Task.

Finally, Schedule this Sub Plan to occur every day after every 5 Minutes as shown in screen below.