Please see my other Database Development articles.

Ensure successful disaster recovery for databases

This section describes steps taken for database to backup data in preparation for a disaster.

Recovery Model

Of the three recovery models provided by SQL Server for its databases (Full, Simple, Bulk), the Full recovery model was chosen for its ability to recover from a disaster to a specific point-in-time.
Note: Databases in full recovery model won’t have their transaction log cleared unless a transaction log backup has occurred. Performing a full or differential backup (see backup types below) won’t clear the log.

This may be set in the properties of a database or via t-sql script.

ALTER DATABASE [database_name] SET RECOVERY FULL WITH NO_WAIT

Backup Types

Each database regularly performs the following types of backups:

• Full: A complete copy of the database at a point in time.
  • This occurs once a week.
• Differential: Changes to the database since the last full backup.
  • This occurs every 4 hours.
  • Note: Since this represents all changes since the last full backup, only the last differential backup needs to be restored.
• Transaction log: All changes (transactions from CRUD operations) since the last log backup.
  • This occurs every hour.
  • Note: Since each log backup captures all changes since the last log backup, all log backups since the last differential would need to be restored to ensure all changes were restored.

Maintenance Plan – Full, Differential, and Log Backups

This section describes how to setup a maintenance plan to perform full, differential, and log backups of a database.

1) R-click Maintenance Plans -> Maintenance Plan Wizard.

*Wizard starts.

2) Next.

3) Set the following properties, Next.
*Type Description into Notepad, then copy back to keep formatting.
*Use Schedule Change button set the desired schedule.

Name: [Database name] – Backup – Full, Differential, Log
Description:
Database: [Database name].
Backup types: Full, Differential, Log.
Schedules
– Full: Friday, 6pm.
– Differential: Every 4 hours.
– Log: Every hour.

4) Set the properties, Next.

5) Reorder tasks if necessary, Next.

Configure Full Backup Properties

1) Set the following properties, Next.

1a) General Tab.

1b) Destination tab: Choose a target folder for the database.

1c) Options tab: Set the desired options, Next.

1d) Configure the schedule.

Click Change.

Update the name to reflect only the current backup type.

Configure Differential Backup Properties

1) Set the following properties, Next.

1a) General Tab.
(same as previous step)

1b) Destination tab: Choose a target folder for the database.
(same as previous step, ensuring the backup is written to the same folder as the previous step)

1c) Options tab: Set the desired options, Next.
(same as previous step, except don’t use compression for backups)

1d) Configure the schedule.

Click Change.

Update the name to reflect only the current backup type.

Configure Optional Report

1) Choose a target for optional reports, Next.

*You’ll now be presented with an overview.

9) Review the overview, make changes (Back) or Finish.

10) A report is generated, Close.

*You should now see your Maintenance Plan.
*Ensure the current instance SQL Server Agent is started.

When you’ve saved the maintenance plan, you should be able to view its steps.

11) Execute the plan to confirm its success.

Note: Should the plan contain multiple steps (sub plan) with different schedules, you’ll need to execute the job instead.

12) Confirm the backup resides at your target folder from the previous step.
*If any issues occurred, view the report file in the same folder.