Skip to main content

SQL Backup - Native

Caution

Use the native backup together with a snapshot-based backup ONLY WITH CAUTION! Snapshot-based backups are created by a VM backup software which makes use of "Application-aware" processing. If e.g. your chosen Veeam settings interfere with SQL, SQL differentials or log backups could be linked to a full backup which is not available to SQL, but only to the backup application which created it, rendering those backups unusable. More about this here.

If you want to use SQL native backups even so, below is a general description of how to configure it. The settings (e.g. scheduling) might differ for your company's individual needs, so please, follow the guide with care and planning ahead. 

Prerequisites

Repository

You need to have an external repository that is connected to the SQL server where your backup files will be stored. This can be e.g. a NAS attached to the server as an SMB share.

SQL Agent

You need to enable the SQL Server Agent and the Agent XPs component otherwise you'll get an error similar to the one below.

image-1620822317087.png

Open SQL Server Management Studio, connect to your server as you usually do.

Click on the + sign next to the server > Management

Right click SQL Server Agent (Agent XPs disabled) > Start

image-1620822479856.png

Open the New Query window

Type the following:

SP_CONFIGURE 'SHOW ADVANCE',1
GO
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'AGENT XPs',1
GO
RECONFIGURE WITH OVERRIDE
GO

Click Execute

image-1620822558548.png

Types of Backups

Full: it includes objects, system tables data, and transactions. With a full backup, you can restore your database to the state, when it was backed up. Full backups won’t truncate your transaction log.

Differential: it retains data since the last full backup. A differential backup is only useful if used together with a regular full backup.

Transaction log: it will backup all of the transactions that have occurred since the last log backup or log truncation, then it will truncate the transaction log.

Maintenance plans

To create a complete maintenance plan for the system, all three backup types need to be used, so there will be 3 different maintenance plans with 3 different schedules.

  • Full: weekly, on Saturday, 11 PM
  • Differential: daily, except for Saturday, 12 AM
  • Transaction log: every 15 minutes.

The times can be different in your scenario depending on your needs, you may set up differentials every 4 hours, and log backups every hour. Please, note that if a backup takes place, no other backups can be started, so if a transaction log backup is set to run every 15 minutes, but takes 20 minutes, it will never allow to run the other backups!

For the 3 types, we'll need to create 3 separate directories on your repositories, one for each.

image-1620822224055.png

Configure backups

Full

Click on the + sign next to the server > Management 

Right click on Maintenance Plans > Maintenance Plan Wizard

image-1620823087095.png

Click Next on the splash screen

image-1620823103866.png

On the Plan properties screen:

  • Give it a meaningful name
  • Choose "Single schedule for the entire plan"
  • Click Change next to Schedule and choose the following:
    • Occurs Weekly > tick Saturday
    • Occurs once at 11:00:00 PM

Click OK and Next in the wizard.

image-1620823285177.png

Choose the maintenance tasks: Check Database integrity and Back Up Database (Full)

image-1620823333046.png

Leave the order of tasks as it is

image-1620823348963.png

Define All databases for the check integrity task

image-1620823376702.png

Click Next

image-1620823392949.png

Define All databases for the Back up database (Full) task, then click the Destination tab

image-1620823423216.png

On the Destination tab, choose Create a sub-directory for each database and define the folder on your external repository dedicated for your full backups, then click the Options tab

image-1620823547282.png

On the Options tab set the following:

Set backup compression: Compress backup

Backup will expire after 14 days (or your retention period)

Tick the Verify backup integrity checkbox

Click Next

image-1620823658955.png

Leave the defaults on the reporting window and click Next 

image-1620823668980.png

Click Finish to complete the wizard

image-1620823735095.png

Differential

For the differential backup, you need to go through the same process as above for the full backup making the following changes in the configuration.

  • Choose a meaningful name
  • Specify the schedule:
    • Choose Weekly then choose 6 days except for Saturday (when the full backup will run).
    • Occur once at 12:00:00 AM

image-1620823865383.png

Choose Back Up Database (Differential) from among the tasks

image-1620823971839.png

On the Destination tab, choose Create a sub-directory for each database and define the folder on your external repository dedicated for your differential backups

image-1620824015408.png

Everything else is the same as for the full backup.

Transaction Log

For the transaction log backup, you need to go through the same process as above for the full backup making the following changes in the configuration.

  • Choose a meaningful name
  • Specify the schedule:
    • Choose Daily
    • Occurs every 15 minutes 

image-1620824438838.png

Choose Back Up Database (Transaction Logs) from the task list

image-1620824446678.png

On the Destination tab, choose Create a sub-directory for each database and define the folder on your external repository dedicated for your transaction log backups

image-1620824486988.png

Everything else is the same as for the full backup.

When the configuration is done, you'll have 3 maintenance plans:

image-1620824517434.png

To have the differentials run properly, first you need to create a full backup. So out of business hours, right click on your Weekly full schedule and choose Execute

image-1620824938327.png

No Comments
Back to top