Backup and Restore

Created by Jessica Rivers (Unlicensed)
Last updated: Aug 01, 2019 by Joel FernandezVersion comment

Backup

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 folder(s) need to add to backup as well. You can locate path to repository in CNG management module under the repository tab on left side column. It is essential that these are backup 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.

Topic

Database and transaction log backups using scripts or from SQL Management Studio.

Why Backup?

Two main reasons:

  1. Disaster recovery
  2. Prevent transaction logs from getting large

What to back up?

SQL Data

We strongly urge you to make database backup for:

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

CNG Repository Data

The CNG document files are stored on the hard drive separate from SQL. These have to be backed up in synch with the SQL backup. (The repository data backup procedure is not covered in this article, but should be pretty straight forward to implement.)

Backup Verification

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.

Methodology

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 write up.

  1. SQL Management studio: Steps are as follows…
    Step 1: Select Server Instance
    Step 2: Select Databases
    Step 3: Select Database of interest (CNG_Main or CNGForms)
    Step 4: Right Click database (CNG_Main)
    Step 5: Select Tasks
    Step 6: Select Backup…
    Step 7: Configure backup ( 3 times for each of the following…)
    1. Full
    2. Differential (if you want to do this)
    3. Transaction Log








  2. External Back Agent. Use instructions that come with it. Backup the following:
    1. Databases CNG_Main and CNGForms
    2. Transaction logs for both the databases
  3. Running script on task scheduler:
    1. 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.



      Differential Backup Script:



      Script to Copy and paste. (You can create a text file and then do a file saveas to save as a .sql file.)

Differential Backup Script to Copy 
######### 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:



Script to Copy and paste. (You can create a text file and then do a file saveas to save as a .sql file.)

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.
##############################################

b. 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.

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 provided in an earlier article.

Validation

The main goal is to be able to see the documents. Since the repository paths can potentially be altered during restoration, we need to take care of it.

Steps to do that 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.

On this page: