TECHNICAL
DOCUMENT
Reference Nr. 20140318/CI/1 Written by Costas Ioannou Latest update 18/3/2014
Comments /
Applies to Inter Engineering DataRemain backup ser-vices
M
S-
SQL
Β
ACKUP
J
OBS
Inter Engineering DataRemain online backup services
This document explains how to use Inter Engineering DataRemain solution to back-up your Microsoft-SQL Databases. The backback-up is performed using Microsoft VSS ser-vices.
Contents:
1. Backup SQL database
2. Monitor Backup Progress
3. Restore SQL database
Prerequisites
In order to execute the following steps, you must first have installed the following DataRemain compo-nents in your system.
Inter Engineering DataRemain Account Console, on administrator’s workstation.
Instructions:http://www.inter-datasecurity.com/dataremain/technotes/install-account-console.html
Inter Engineering DataRemain Backup Agent, on Microsoft-SQL server holding the databases.
Instructions:http://www.inter-datasecurity.com/dataremain/technotes/install-backup-agent.html
Note: The instructions above navigate you to install the backup agent service to run as ‘Local Sys-tem’. If you are going to use ‘Windows Authentication’ for accessing the MS-SQL server, then the backup agent service should be installed to run as an account with access to Microsoft SQL server.
1. Create Backup Job
Open the Account Console and ‘Computers’ on the toolbar.
If you are going to use SQL authentication to gain access to the SQL server, now it’s a good time to con-figure the credentials to be used:
Click the ‘Credentials’ button on the tool bar.
Then click ‘Add new credentials’. Fill in the credentials (username and password), and give a description of the credentials.
Click OK.
Now you can continue with configuring your backup job details. Navigate back to ‘Computers’. Select your backup job, and select ‘Database Selection’.
(1) Click ‘Refresh Server list’ to obtain a list of available SQL servers (2) Select the SQL server from which you wish to take the backup
(3) Select ‘Authentication’ mode. If you select ‘Server authentication’ (SQL authentication), select the credentials you have created in the steps above.
(4) Click ‘Refresh database list’. The agent will connect to SQL server and receive a list of available databases
You can select to take one Full, Differential or Copy backup of a database at specific time.
If your database is running in ‘FULL’ Recovery Model or ‘BULK-LOAD’ Recovery Model, you can take in-termediate transaction log backups.
Enable and select which days and how frequently the ‘Transaction Log’ backup is going to take place. In the ‘Retry Settings’ select how the agent is going to behave if the connections fail for some reason. Click ‘OK’ to complete the choices.
Then select a ‘Retention Period’. You may choose a ‘Simple’ retention period or a more complex ‘Smart’ retention.
Then you can select the compression algorithm. Navigate to ‘Compression’. We recommend using the ‘LZMA’ compression algorithm. You may also choose a ‘compression level’, although we recommend to leave it to default ‘normal’. Click ‘OK’.
Navigate to ‘Enhanced Delta Processing’. Make sure that this option is ‘Enabled’. You can leave the rest of options to default. Click ‘OK’.
Finally with the ‘Email notifications’ you can choose to get notified on each phase or event of the backup job. Click ‘OK’.
You may now choose to take an initial ‘FULL’ database backup, as a starting point for your differential and (optionally) your Transaction Log backup.
A new pop-up window, prompts you to choose the type of backup you wish to take. Select ‘FULL’ backup and click ‘OK’.
The backup now begins. You can monitor its progress by following the instructions below.
2. Monitor Backup Progress
To monitor the backup progress, navigate to ‘Job Summary’ button on the toolbar.
Once the job is complete, the log entry should be turned to green. If not, you can always click ‘Reload List’, to refresh manually the list.
Once the job is done, you can view it in the backup list of the specific job.
Navigate back to ‘Computers’ button on the toolbar. Select the specific backup job, and click on ‘Backup’ selection. You should now see a list of backups available on the remote storage.
By clicking on a specific transaction, you can choose at the bottom of the screen to view
- Flat-based, and tree-based list of backed-up files
- Detailed log of the specific job execution.
3. Restoring a database
The restoration of a database backup includes 2 phases.
- Phase 1: Download the data from the remote storage. This step is optional if both of the
follow-ing is true:
o You have enabled an ‘offline storage’ in the job.(so the latest backup file set is also stored there).
- Phase 2: Recover the database file using the typical Microsoft SQL tools.
Phase 1: Download backup file-set
Navigate to ‘Computer’ button on toolbar. Select the Microsoft SQL computer and the backup job. Navi-gate to the ‘Backup’ list.
Select the backup file-set you wish to download.
Click on the ‘Flat-catalogue’ tab at the bottom. You should see your file-set on the remote storage.
Select ‘Selected directory’ on which the files will be downloaded. Then you may choose overwrite settings.
You must provide your ‘Data Encryption password’. You have set it up when you activated your account (Account Console installation).
You may also choose to perform ‘Data Integrity’ checks. By clicking ‘OK’ the download will start.
The files are downloaded, you can proceed with Phase 2.
To restore the databases to your MS-SQL server you can now use the typical tools provided by Microsoft and your SQL-server installations.
For more details please refer to Microsoft online documentation. More details:
Microsoft-SQL 2005: Backing Up and Restoring Databases in SQL server http://technet.microsoft.com/en-us/library/ms187048%28v=sql.90%29.aspx
Microsoft-SQL 2005: Performing a Complete Database Restore (Simple Recovery Model) http://technet.microsoft.com/en-US/library/ms186216%28v=sql.90%29.aspx
