Setting up an SQL backup for BMD NTCS

Note:

This guide is for skilled computer administrators with experience in working with databases. It is intended to help you set up an SQL backup. However, this guide is in no way a basis for claims in case of data loss. The customer is solely responsible for ensuring a full, correct and recoverable backup of all data.

Thus, it is necessary that you have a complete backup strategy. BMD recommends that you back up all data daily. This daily backup should be available for 4 weeks. It is also advisable to do a weekly backup to an external medium (e. g. tape) which is not directly accessible. If possible, store this medium off site. We recommend that you keep these backups (e. g. on tape) in monthly segments and for the respective retention period required by law (e. g. 7 years).

Example of a possible backup concept:

• Daily backup to disk - store for 4 weeks
• Weekly backup to tape - store for 4 weeks in a different location
• Monthly backup to tape - store in a different location as per legal provisions

Furthermore, it is necessary to check the backups regularly on whether they are readable and the data can be recovered.

1. Setting up an SQL backup

1.1. Checking the ‘Recovery model’

Windows 7 / server 2008 R2 or earlier versions:
Start → Programs → Microsoft SQL Server 20xx → start SQL Server Management Studio.

Windows 8 / server 2012 or later versions:
Enter ‘SQL Server Management Studio’ in the search dialogue of the Metro user interface or use the arrow button to search and start it.

Alternatively, you can find the SQL Server in Windows Explorer under the following path:
C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 20xx

Replace “20xx” with the respective SQL version in use.

PLEASE NOTE!
If Windows User Account Control (UAC) is active, you have to start SQL Server Management Studio by right-clicking and selecting ‘Run as administrator’!

In SQL Management Studio → Databases → right-click on BMD → Properties
 

Check the ‘Recovery model’ in 'Options' and change it to ‘full’ if it is currently set to ‘simple’ or ‘bulk-logged’.

1.2. Checking the location of the transaction log file

Start SQL Management Studio → Databases → right-click on BMD → Properties.

In ‘Files’ you can check the location and the auto growth settings of the BMD database files.

Due to the setting ‘Full recovery model’, the SQL database will write each transaction to the file BMD_LOG.ldf. This file is written sequentially and thus grows in size until it has been backed up with an SQL backup job. While the file is emptied during the backup, according to the operating system its size does not change.

For this reason, the following aspects concerning the log file are crucial:

• The maximum file size of BMD_LOG.ldf has to be adjusted accordingly.
• There needs to be enough available hard disk space so the log file’s “growth” is not hindered.

Hint
Placing the file BMD_LOG.ldf on a separate hard disk or a separate RAID where only this file is located, increases the performance of BMD NTCS. This especially applies to operations that cause a high database IO load (e.g. BMD NTCS update, analyses in cost accounting, preparing balance sheets etc.).

PLEASE NOTE!
You should use a separate RAID and the RAID controller should have a write cache!
A separate partition on the same disk is not sufficient, since as a result ‘Random’ and ‘Sequential IO load’ cannot be separated!

1.3. Setting up the backup

Prerequisite:
You have to start SQL Server Agent beforehand.
SQL Management Studio → SQL Server Agent → right-click → Start → confirm with ‘Yes’.

Furthermore, you have to change the start mode of this service to ‘Automatic’, in order to restart it in case of a server restart. This ensures that the backup works!

You can change this setting in Windows Services Manager.

The backup concept used in the instructions below is as follows:

You have to back this file up with the daily backup to a magnetic tape or an external medium!
This backup concept ensures that it is possible to recover data from any point in time between 06.00 and 18.00.

Create a directory in Explorer where the backup file is to be located, e. g. C:\BMDNTCS- SQL-Backup.

Download the ZIP SQL script “1_add_backup_device.sql“.
If you double-click on the file, SQL Management Studio should start automatically.

In case it does not, start SQL Management Studio manually and open the script (File → Open → File or click on the ‘Open file’ button in the menu bar or Ctrl + O). Then execute the script (F5 or click on ‘Execute’):
 

You should now see ‘Query executed successfully’ in the footer.

In the bottom right corner, you can check if it was executed in the correct database.

The Backup-BMD device should now be cited in SQL Management Studio under Server Objects → Backup Devices.

Now download “2_full_backup_job.sql“. Then open and execute the script exactly as described above.

As a result, you should see ‘Query executed successfully’ in the footer.

Download the third script “3_trans_log_backup_job.sql“ and proceed as described above.

Again, you should see ‘Query executed successfully’ in the footer.

PLEASE NOTE! 

If you are still using SQL 2005/2008, you have to use the following scripts without compression:

2_full_backup_job.sql

3_trans_log_backup_job.sql

Please update to the latest SQL version (2014/2016) as soon as possible.

Now both backup jobs should be displayed when you open SQL Server Agent → Jobs:

1.4. Checking the backup

You can check whether the backup was successful in two ways:

1. By checking the time stamp of the backup file in the backup directory:
 

2. In SQL Management Studio → SQL Server Agent → right-click on one of the Jobs → View History:
 

Please note that you have to include the corresponding document directories in the backup!
The documents are not directly stored in the database but in separate directories. The database only manages references to these documents and the related master data.

You can find the paths that were used in BMD NTCS under

Tools → Administration → Paths.
 

You need to consider all paths listed here and include them in your daily data backup and/or your backup concept.