Overview

We highly recommend you backup your data. Doing so will ensure no data loss should a disaster recovery be needed. Below are steps to assist with backup up your databases. Also, your Repository folders should be backed up the same way standard files in your filesystem are backed up. You can locate path to repository in PSIsafe Management module under the repository tab on left side column. It is essential that this data is backed up to prevent any data loss.

A few minutes spent on a backup, verification and a recovery plan will go a long way in keeping your sanity when you actually need your backed up data.

Why Backup?

Two main reasons:

1. Disaster recovery in the event of data loss.
2. Prevent transaction logs from getting excessively large by performing regular log truncations (which also improves overall performance).

PSIsafe SQL Databases via SSMS

We strongly urge you to make database backups for:

1. Main database (generally named CNG_Main)
2. Forms database (Always named CNGForms)
3. Transaction Log files for both #1 and #2

PSIsafe Repository Data

The PSIsafe Repository files are stored on the hard drive separate from SQL. The backup of Repository files should occur at the same time as the backup of SQL Databases.

1. Open PSIsafe Management
2. Log in as the Administrator account.
3. Click on Filing Structure > Repositories
4. You will see where all the physical repositories are. Ensure each one is copied and moved to a secure location. System information displays approximate filesizes.
5. Ensure these files are backed up.

Creating a Regular Backup Process

Once your backup process is finalized: scripts are generated and scheduled to run, or a backup job is created in SQL\ backup utility, It is very essential to test the process to see if it is backing up your data correctly. It is also a good idea to periodically check this process, as settings, passwords or data locations may change over time. It is also a good idea to have a backup in a location different from the original data (CNG & SQL) location.

Backup Process - Manual Script Creation

Databases and transaction log files can be backed up using a variety of ways. Your IT personnel should help you set these up. Methods and steps listed below should give you an idea of what databases need to be backed up and how it can be done. 

NOTE: Read about Recovery Model at the end of this article.

1. Open SQL Server Management Studio (SSMS)
2. Select Server Instance
3. Select Databases
4. Select Database of interest (CNG_Main or CNGForms)
5. Right Click database (CNG_Main)
6. Select Tasks
7. Select Back Up...
   
8. Configure backup (Configure for each database and log file)
   1. Full (Full copy of the database on each backup)
   2. Differential (only takes new data and merges)

Using an External Backup Agent

Use any instructions relevant to your External Backup Agent, and be sure to backup the following:

• Databases CNG_Main and CNGForms
• Transaction logs for both the databases

Running Backup Script on Task Scheduler

Scripts: The backup can be done with different backup types. One suggestion would be to write a script for full, Sequential and transaction log. Then run the sequential every night of the week and the full backup once every week over the weekend. (Sample screen shots are provided for both tasks and scripts)

NOTE: The scripts provided include both the main database (CNG_Main) and the forms database (CNGForms). Each script will include the backup of both database and transaction log.

NOTE: Running both a differential and full backup is up to the discretion of the IT Administrator

######### START: DIFFERENTIAL ‐ Edit the path and database name if necessary. File name: SQLBackupScripts‐Differential.sql ############

BACKUP DATABASE [CNG_Main] TO DISK = N'D:\Backups\CNG_Main.bak' WITH DIFFERENTIAL, NOFORMAT,
NOINIT, NAME = N'CNG_Main‐Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [CNG_Main] TO DISK = N'D:\Backups\CNG_Main.bak' WITH NOFORMAT, NOINIT, NAME = N'CNG_Main‐
Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [CNGForms] TO DISK = N'D:\Backups\CNGForms.bak' WITH DIFFERENTIAL,NOFORMAT,
NOINIT, NAME = N'CNGForms‐Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [CNGForms] TO DISK = N'D:\Backups\CNGForms.bak' WITH NOFORMAT, NOINIT, NAME = N'CNGForms‐
Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

######### END DIFFERENTIAL Script to Copy and paste.
##############################################

Full Backup Script to Copy

######### START: FULL ‐ Edit the path and database name if necessary File name:
SQLBackupScripts‐Full.sql ############

BACKUP DATABASE [CNG_Main] TO DISK = N'D:\Backups\CNG_Main.bak' WITH NOFORMAT, NOINIT, NAME =
N'CNG_Main‐Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [CNG_Main] TO DISK = N'D:\Backups\CNG_Main.bak' WITH NOFORMAT, NOINIT, NAME = N'CNG_Main‐
Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [CNGForms] TO DISK = N'D:\Backups\CNGForms.bak' WITH NOFORMAT, NOINIT, NAME =
N'CNGForms‐Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [CNGForms] TO DISK = N'D:\Backups\CNGForms.bak' WITH NOFORMAT, NOINIT, NAME = N'CNGForms‐
Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

######### END FULL Script to Copy and paste.
##############################################

Task Scheduler

Once the scripts are written they need to be run via the task scheduler. Following steps will help you configure the tasks.

1. Start Task Scheduler
2. Create new Task
3. Configure the tasks

NOTE: Please change the path to the program appropriately and set the correct SQL username and password where needed.

Locate "Task Scheduler" from the list:

Create a Task and use the settings outlined below in the following screenshots for the General, Triggers, Actions, Conditions, and Settings tabs.

Create a Task - General Tab

Create a Task - Triggers

New Trigger - For Differential, set to week days only.

Create a Task - Actions

New Action

The paths may be different on your machine depending on the location of install and SQL version. Arguments are CASE SENSITIVE.

Program/Script:

SQL 2008: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
SQL 2014+: "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn"
NOTE: The Version # in this string "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\[VERSION]\Tools\Binn" may vary depending on your SQL version.

Add arguments (Optional): ‐S \SQLEXPRESS ‐U username ‐P password ‐I "D:\Backups\SQLBackupScripts‐Differential.sql"

NOTE: \SQLEXPRESS can be anything. It is just the name of the SQL Server instance

Create a Task - Conditions

Create a Task - Settings

Full Backup Task use the same settings as Differential Backup Task with the exception of the Trigger and the script Action to run as shown in the screenshots below.

Create a Task - Trigger (Full Backup)

Create a Task - New Action (Full Backup)

Finally…Test your scheduled task to see if it runs correctly.

You would like to see “Task Scheduler successfully finished.” At this point if the backup file has been created and if it already exist, check its modified date time stamp.

Default outputs will look like the screenshot below.

Backup Process - SQL Server Agent Maintenance Job

Review the SQL Server Maintenance Plans documentation available from Microsoft:

Maintenance Plans

Maintenance plans are an excellent tool available through SSMS v. 17+, and SQL Standard or SQL Enterprise. Unfortunately, SQL Server Agent is not available in SQL Express. The SQL Server Agent enables the SQL Server Agent service to enact SQL transact scripts with detailed steps that can be controlled and automated right from the SSMS console. Below is a simple set of SQL transact scripts to implement using steps in the SQL Server Agent Maintenance Wizard, as a part of a Job. The following, once implemented, will perform a regularly scheduled Backup via the SSMS Console and Maintenance Wizard Process:

BACKUP DATABASE CNG_Main
TO DISK = N’D:\CNGData\CNG_MAIN.bak WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N’CNG_Main‐FULL’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [CNG_Main] TO DISK = N’D:\CNGData\CNG_Main_log.bak WITH NOFORMAT, NOINIT, NAME = N'CNG_Main‐TLBU', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [CNGForms] TO DISK = N’D:\CNGData\CNGForms.bak WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'CNGForms‐FULL’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [CNGForms] TO DISK = N’D:\CNGData\CNGForms_log.bak WITH NOFORMAT, NOFORMAT, NOINIT, NAME = N'CNGForms‐TLBU’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

USE CNG_Main; 
GO 
BACKUP DATABASE CNG_Main 
TO DISK = 'D:\CNGData\CNG_MAIN.bak' 
WITH FORMAT, 
MEDIANAME = 'D_SQLServerBackups', 
NAME = 'CNG_Main_Full'; 
GO

USE CNG_Main; 
GO 
BACKUP LOG CNG_Main
TO DISK = 'D:\CNGData\CNG_MAIN_Log.bak' 
WITH FORMAT, 
MEDIANAME = 'D_TLBU', 
NAME = 'CNG_Main_Log'; 
GO

USE CNGForms; 
GO 
BACKUP DATABASE CNGForms 
TO DISK = 'D:\CNGData\CNGForms.bak' 
WITH FORMAT, 
MEDIANAME = 'D_SQLServerBackups', 
NAME = 'CNGForms_Full'; 
GO

USE CNGForms; 
GO 
BACKUP LOG CNGForms
TO DISK = 'D:\CNGData\CNGForms_Log.bak' 
WITH FORMAT, 
MEDIANAME = 'D_TLBU', 
NAME = 'CNGForms_Log'; 
GO

USE CNG_Main; 
GO 
BACKUP DATABASE CNG_Main 
TO DISK = 'D:\CNGData\CNG_MAIN.bak' 
WITH DIFFERENTIAL;
GO

USE CNG_Main; 
GO 
BACKUP LOG CNG_Main 
TO DISK = 'D:\CNGData\CNG_MAIN_Log.bak' 
WITH NOFORMAT;
GO

USE CNGForms; 
GO 
BACKUP DATABASE CNGForms 
TO DISK = 'D:\CNGData\CNGForms.bak' 
WITH DIFFERENTIAL;
GO

USE CNGForms; 
GO 
BACKUP DATABASE CNGForms 
TO DISK = 'D:\CNGData\CNGForms_Log.bak' 
WITH NOFORMAT;
GO

The above can also be run as a regularly scheduled script, as detailed in the previous method. 

Recovery Model

This should be set to “Full”. If it is not please set it by doing the following:

1. Select Database (CNG_Main)
2. Right Click and Select Properties.
3. Select Options
4. Set Recovery Model: to Full
   

Database File Settings

Settings can be set by doing the following:

1. Select Database (CNG_Main)
2. Right Click and Select Properties.
3. Select Files
   
4. Click the ellipses button of Autogrowth and set what is appropriate for your database.
   
   Settings for the Log file:
   

Restore

What are the components required for a successful restore? A restore process from scratch requires the following:

1. Backup of CNG SQL Database (In synch CNG Files Repository)
2. Backup of CNG Files Repository (In synch with CNG SQL Database)
3. CNG‐SAFE Installation Files
4. MS SQL Server files

What are the verification steps to be followed before a restore?

1. Verify if the CNG SQL Database and CNG Files Repository backups are in synch. This just means that the backups were done at the same time.
2. Verify if SQL server is installed on the machine. If not please install the SQL Server ( Whatever was used earlier, Full or Express version)
3. Verify if CNG Server is installed on the machine. If not please install CNG Server. NOTE: Set the “Skip database creation” to TRUE. This is because we are going to restore the database from backup.
4. Verify if the client is installed on the CNG Server. If not please install the client. This step is required for registration and validation process.

What is the process involved in a restore? There are three sections to this process:

1. CNG SQL Database Restore
2. CNG Files Repository Restore
3. Registration
4. Validation

CNG SQL Database Restore

1. Log in to SQL management Studio
2. In database explorer, Right click on “Databases”
3. Select “Restore database…” item
   
4. In the General page, Set the Destination for restore. Name this something other than your old database name. Use the “CNG_” prefix to the name.
   
5. In Source to restore, Set the option “From Device” and use the browse button to select your back file.
   
   
   
   
   
   
   Finally, click on the checkbox of the last full restored backup to restore.
   
6. Click on the OK button to restore.

CNG Files Repository Restore

The procedure used to backup the repository will determine the restoration process. Whatever be the backup method, the requirement is to restore to the server hard drive. Backup utilities will allow you to restore to any directory. Hence it will be a good idea to the same path as before but, you can also create a new folder and restore to it.

Registration

The database has to registered for successful restoration. This is because it is possible that the CNG Server or SQL Server Instance might have changed during the restoration. Please read the step by step instructions here:

PSIsafe Server Administrator Guide: Registration

Validation Notes

The main goal is to be able to see the documents. Since the repository paths can potentially be altered during restoration, we need to make sure the path is correct.

Steps for each repository entry in the management:

1. Identify the actual restored location of the repository path. This should be the parent level to where the “CNG” folder containing the numbered folders exist.
2. Delete the “CNG_DO_NOT_DELETE” file present there (This is the only time it is alright to delete this file)
   
3. Login to CNG‐Management as “Administrator”
4. Select Repositories. Highlight your repository and edit it. Change the path to your new path. NOTE: Keep in mind, it should not end with “CNG”.
   
5. If the repository is not on the same server as CNG‐Server installation, the repository has to be given full security access rights to the account that runs the CNG‐Server service. Finally, login to the client as a regular user and verify the data and files can be accessed.