Microsoft SQL Server database backup and restoration

A database backup duplicates all the data in the Incentives database and creates a copy of the full database.

You can then recreate the entire database in one step by using the restore command. The restored database is an exact match of the database at the time that the backup completed.

Backing up a database

You can use SQL Server Enterprise Manager to back up the Incentives database.

In SQL Server Enterprise Manager, click Tools → Backup Database.
Select one of the following Backup Types:
Complete backup
Makes a full backup of your database. Start your backup process with a full back up of your database.
Differential backup
Stores all changes that occurred to the database since the last full backup.
You can edit the backup file destination. By default, the backup file is stored in the following location: C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\.
Tip
The database can remain online and accessible to users while the backup is being made.

Restoring a database backup

In the backup file, SQL Server stores the names and locations of the files that are used in the Incentives database. After you restore the database, SQL Server recreates all the necessary files and the database is restored to the point in time that the backup finished.

In SQL Server Enterprise Manager, click Tools → Restore Database.
The Restore as Database field is the name of your newly restored database. It does not need to be identical to the name of the original (backed up) database.
To define your restoration parameters, select the original database name and the stored backup that you want to restore.

Restored database addition

After restoring a database, you must call the Incentives tenant services to add the restored database.

Make sure you have set the keys and values for the header before making this request.

In a tool, such as Postman, use the following call to add the restored database:

Request

POST /tenants/1/models/externalRestore

Sample Call

Path: http://localhost:9100/services/tenants/1/models/externalRestore
Method: Post
Body: {
	"model_name":"string",
	"server":"1",
	"isOverwrite":false,
	"isUnicode":true
}

model_name: This is the name of the model. Model names cannot contain any spaces, underscores, or punctuation.
isOverwrite: If you set this value to true, than the model must already exist in tenant services and will be deleted, including all users, and re-added as the new one. If this value is set to false, then the model is considered to be new. If the value is set to false, and tenant services already has a model by this name, the call will fail. Likewise, if you set this value to true but the model does not exist in tenant services, the call will also fail.

isUnicode: Set this value to true if your database type is Microsoft SQL Server 2014 Unicode. Otherwise, set this value to false.

Sample Response

{
	"model_id":0,
	"model_name":"string",
	"tenant_id":0,
	"server":"1",
	"database_name":"string",
	"created_at":"2016-03-01T23:59:29.305Z"
}

Load users after database restoration

After adding a restored database by calling Incentives tenant services, you must also make a call to load the users into tenant services from the model.

Tip

Make sure you have set the keys and values for the header before making this request.

Run a GET call to get all the users in the model that was externally restored.

Path: http://localhost:9101/services/admin/tenants/:tenantID/models/:modelID/getmodelusers
Method: GET

Sample response

{ 
"ignoredUsers": [
"scheduler",
"admin"
],
"newUsers": [],
"validUsers": [
"user1",
"user2",
"user3"
]

Run a POST call to load the 'validUsers' from the previous response to the tenant into the postgres database.

Path: http://localhost:9100/services/admin/tenants/1/models/<ID of model>/addmodelusers
Method: Post
Body:{
"users": ["user1", "user2", "user3"]
}

Using backup and restore to transfer databases between servers

You can restore a backup of the Incentives database on a new server by creating a backup of a database and saving it to a location that is accessible from the server where you are completing the restoration.

From the server that you want the database transferred to, open Enterprise Manager.
Click Tools → Restore Database.
Type the name that you want the database to be restored with in the Restore as database field.
Select the From device option.
Select File name and type the location of the backup file.