Knowledge Base

CONTEXT

This article covers the most usual kinds of backup, without requiring detailed knowledge of SQL Server, as follows:

– Complete or Full backup

To protect the entirety of the databases (i.e. the data, table structures, index, keys etc.).

– Differential backup

To protect all changes to the databases (i.e. to the data, table structures, index, keys etc.) since the last complete backup was taken.

When to use complete backups?

If your database is not large you may want to back it up less often (say weekly or daily).

When to make a differential backup in addition to a complete backup?

If your database is large you may want to back it up frequently (say once or twice a day).

When to schedule a full backup?

A full backup takes time because it protects all of your database. You should schedule it for when the HDS and SQL Server are least busy, that is to say when there is no sustained activity such as a purge, defragmentation, replication or general usage.

When to schedule a differential backup?

A differential backup takes less time than a complete backup because it only saves the differences between the last complete backup and the starting point of the differential backup. You should not schedule it to run during a purge, defragmentation or replication.

What to do with all of these backups?

You should always keep a complete backup and one or more differential backup(s) to protect against data loss if there is a database crash.

Warning: A differential backup without a complete backup won’t enable you to restore a database because it would not have the data contents to which its changes refer.

SETTING UP A COMPLETE BACKUP

Here we will set up a maintenance schedule and a custom maintenance task in PcVue. This will run a stored T-SQL procedure for backing up the HDS databases.

1) Setting up the stored procedure for full backup of the HDS database

Open the SQL Server Management Studio tool and connect to your SQL Server process.

Right-click on the database to be backed up, via the menu Tasks / Back-Up…

General tab

Set the backup type to FULL.

Use the Remove button then the Add button to change the destination of the backup file to your second hard disk drive. (The extension in a backup’s file name is often .bak.)

Tab Options

Tick the option Overwrite all existing backup sets.

Tick the option Verify backup when finished.

Without validating this dialogue box, click on the Script button at the top.

You have to use a script that is displayed in the Request Editor.

Delete the two keywords GO in this script.

Enter a new request (via the New Query button).

Copy the script.

CREATE PROCEDURE sp_fullBackup
AS
BEGIN
SET NOCOUNT ON;
— Backup script begins here
— Backup script ends here
END
GO

Insert the backup script between the two comment lines:

CREATE PROCEDURE sp_fullBackup
AS
BEGIN
SET NOCOUNT ON;
— Backup script begins here
BACKUP DATABASE [DB_TO_BACKUP] TO  DISK = N’E:\backup.bak’ WITH NOFORMAT, INIT,  NAME = N’DB_TO_BACK UP-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’DB_TO_BACKUP’ and   backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’DB_TO_BACKUP’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ”DB_TO_BACK UP” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N’E:\backup.bak’ WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
— Backup script ends here
END
GO

Run the script (Run button)

2) Test the stored procedure

Warning: This test will trigger an actual backup of your HDS database’s contents.

From a fresh request dialog, run the following script:

EXEC sp_fullBackup

3) Setting up the maintenance task

Now you only need to set up a maintenance schedule in PcVue that contains a custom task. Its script will be as follows:

EXEC sp_fullBackup

You can set the maintenance schedule to Daily at 00:00 (midnight).

SETTING UP A FULL AND DIFFERENTIAL BACKUP

Follow the three steps of the setting up a complete backup.

1) Setting up the stored procedure for a differential backup of the HDS database

Right-click on the database to be backed up: Tasks / Back-Up…

General tab

Set the backup type to Differential.

Tab Options

Tick the option Append to the existing backup sets.

Tick the option Verify backup when finished.

Without validating this dialogue box, click on the Script button at the top.

You have to use a script that is displayed in the Request Editor.

Delete the two keywords GO in this script.

To create a new request (using the New Query button)

Copy the script below:

CREATE PROCEDURE sp_differentialBackup
AS
BEGIN
NOCOUNT SET ONE;
–Backup script begins her
— Backup script ends here
END
GO

Insert the following backup script between the two comment lines:

CREATE PROCEDURE sp_differentialBackup
AS
BEGIN
SET NOCOUNT ON;
— Backup script starts here
BACKUP DATABASE [DB_TO_BACKUP] TO  DISK = N’E:\backup.bak’ WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N’DB_TO_BACK UP-Differential Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’DB_TO_BACKUP’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’DB_TO_BACKUP’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database “DB_TO_BACK UP” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N’E:\backup.bak’ WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
— Backup script ends here
END
GO

Run the script (Run button)

2) Test the stored procedure

Warning: This test triggers an actual backup of your database’s contents.

In a new Request Editor, run the following script:

EXEC sp_differentialBackup

3) Setting up the maintenance task

Now you only need set up a maintenance schedule in PcVue that contains a custom task. The script of the task custom will be as follows:

EXEC sp_differentialBackup

You can set the maintenance schedule to run every two hours from 01:00 (midnight). There will then be a complete backup every day at 00:00 then every 2 hours from 01:00.

Warning: In this case your complete backup must not take more than one hour to run otherwise the two backup processes will try to run in parallel!

TO CHECK THE EXISTENCE OF A BACKUP OF YOUR HDS DATABASE

Right-click on the database to select it, via the menu Tasks / Restore  / Databases…

General tab

The two backups (complete and differential) are listed.

Now you can check that PcVue’s custom tasks have worked.