How to backup Microsoft SQL Server with Nordic Backup Pro

by admin | Jan 17, 2018 | guides | 0 comments

How to backup Microsoft SQL Server with Nordic Backup Pro

Print User Guide

Before creating a SQL backup set within Nordic Backup Pro it is first necessary to verify that the settings of the SQL server are correct. It is important that the database Recovery Model be set to "full" and that you create an account within your SQL Server to be used exclusively for Nordic Backup. Begin by opening the SQL Server Management Studio, then expand your server as shown on the right. Then expand "Databases", and then right click on the database you would like to backup and click "Properties".

In the "Database Properties" screen, click on "Options" in the left pane. Then click on the drop down box next to "Recovery Model". This should be set to "Full". The default on a new SQL installation is "Simple" which will cause the backup job to fail. Click "OK" and repeat this procedure for each database you intend to back up. This will not affect any users accessing the database.


Now we will create the user account for Nordic Backup Pro to have access to the databases. The account needs to be setup with SQL only authentication, having the "sys-admin" role for full access to the databases. Expand the "Security" section below databases, then right-click on "Logins", then left-click on "New Login" in the pop-up menu.

In the "Login - New" screen, first select the radio button to use "SQL Server Authentication". Then enter the account user name you wish to use in the top box. We recommend "Nordic Backup SQL". Enter the account password of your choice. Then uncheck the box to "Enforce Password Policy". This will ensure the account password will not expire and there are no complexity requirements.


Click on the "Server Roles" in the left pane to change the roles for the account. Check the box for the "sys-admin" role. Click "OK" when complete, then close SQL Server Management Studio. You are now ready to create a SQL database backup set in Nordic Backup Pro.


Open Nordic Backup Pro, then click on the "Backup Setting" button at the bottom.

At the top of the Backup Setting screen, click on the Plus (+) sign to create a new backup set. Nordic Backup is designed to maintain a separate backup job for each type of backup (File, Database, System State, etc.) and run them simultaneously if their backup times overlap.


Enter a descriptive name for the backup set, for example SQL backup. Change the backup type to ‘MS SQL Server Backup’. Then the SQL server connection info will appear below the Backup Set information. This specifies the server and login information to connect to the local server. By default, it will be populated with the local server name and SA account, however it is important to change the account information to the account that was entered in the "Login - New" screen (our example was Nordic Backup SQL). Click “Next”.

Select the databases to backup by clicking on the box next to it, then click “Next”. If there are no databases shown, an incorrect Login ID or Password was supplied in the previous screen.


Set the schedule options for the database backup set. By default, the Transaction logs are scheduled to be backed up Monday through Thursday at 21:00 hours (9pm). The full database backup is scheduled on Fridays at 21:00 hours. To modify these schedules, click on the schedule and then click “Properties” as shown.

Change backup times and days to run as you would like. We recommend running the transaction log backups several times a week and the full database at least once a week. If the database you are backing up is less than 5 GB in size, it is not necessary to perform the "Transaction Log Backup," because the full backup is compressed so well that it can be uploaded with modest bandwidth during a weeknight. In this case, remove the schedule for the transaction log backup, then modify the "Database Complete Backup Schedule" to run daily. Click “Ok” when done, then “Next” in the New Backup Set Wizard. Note: You can have more than one schedule in a backup set, i.e. you can perform intra-day backups by adding more than one daily backup schedule to your backup set.


On this screen you may set the encryption for this backup set. You can choose between “Default” and “Custom". If you choose "Default” your files will be encrypted with the password you use to log into the Nordic Backup Pro. Note that the encryption key then is fixed and does not change if you subsequently change the password of your account. We recommend that you select "Custom" and enter an encryption key of your choice in the 2 bottom boxes. It is important that you choose an encryption key which only you know, thus assuring that you are the only who can access your files through Nordic Backup. Please note that your databases cannot be restored without the encryption key. It is important that you keep it in a safe location, in case you lose your computer. Nordic Backup does not store encryption keys. We recommend that you select the following options for encryption: AES algorithm CBC mode 256 bits Your encryption key must be at least 8 characters including upper case letters and lower case letters and at least one number. Click "OK" to continue.

Click "OK" to finish creating the backup set.


After the SQL backup set has been created, there are some advanced settings to check. You will need to ensure that the temporary location for storing the backup has enough free space and is not included in a regular file backup. You should also verify the Retention and In-File Delta settings are to your satisfaction. By clicking the "Backup Settings" button on the main screen, you can see and change settings for each backup set.

In the "Backup Setting" screen you can modify any of your backup sets. To select which backup set to modify, click on the drop-down arrow button on the main screen, you can see and change settings for each backup set. If you have backup sets that belong to another machine you are backing up, do not adjust settings except from the client software directly on that machine. It is also important NOT to have more than one instance of Nordic Backup Pro open that are connected to a single account. When you have completed the configuration of the backup client, save the settings and close the software.


Options Here you can select the temporary work directory for Nordic Backup. Before uploading your backup set to Nordic Backup, your data is first encrypted and compressed to ensure confidentiality. This is a disk-intensive process and a substantial amount of space is needed, therefore we recommend that you place the temporary folder on a drive which has the most free disk space. You may use an external drive as your temporary folder, but this drive must be connected during every backup or the backup will fail.

Retention policy Retention is an important part of any backup because it specifies how long a changed, deleted, or older backup image is retained on our backup servers. By default, Nordic Backup is configured to retain old backup data for 7 days. However, Nordic Backup Pro can retain all previous backup data for as long as you specify, but be advised that all data is counted toward your backup storage usage. If you would like to increase or decrease your data storage limit, it can be managed through the Nordic Backup website or the Retention Policy tab of the Backup Setting screen at any time. For most backup sets, we recommend you set Retention time to 30 days minimum.


In-File Delta Delta Block mode is very useful for backup of large files, as it only takes a backup of the changes within the file itself. Even if a file is 1 GB, it can easily change as little as 50 MB. After the initial full backup, during subsequent backups only the parts of the file that have changed will be backed up. We recommend that you adjust the default settings by clicking on the "Advanced" button.

Adjusting the In-File Delta settings are recommended for large databases where you want to control the backup behavior on select days. With the In-File Delta technology enabled by default, 100 delta backups are permitted before a new backup file is created and takes the place of the previous backup file. By setting a monthly override of the delta upload, it ensures that the database will be backed up in full at Nordic Backup on certain days. Another benefit of the monthly override is to reduce the number of delta blocks and reduce the restore time. For example, check all the boxes for each month then select the radio button for the specific day. We recommend choosing the last weekday of the month. In this example, no matter how many delta backups were performed, the last weekday will always do a full backup of a new backup file.

US-2014.01.30