MS SQL: Setting up a 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) that 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 disc - 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”

From Windows 10 / Server 2016:

Search for “SQL Server Management Studio” in the Windows Start menu or in the Windows task bar search box.

Alternatively, you can also find the program in Windows Explorer under the following path:

“C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 20xx”

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

For older SQL Server versions, the path and the name are slightly different and can be found under the following path: 

“C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 20xx”

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.1.2. Checking the storage 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 aspect is crucial for the log file:

• There needs to be enough available space on the hard disk for the log file to grow larger without any problems.

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

1.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:

This backup concept ensures that it is possible to recover data from any point in time between 06:00 and 18:00. If possible, you should use a separate drive for the backup destination.

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

Please make sure to save this backup file to an external data carrier during the daily backup of the server!!!

Download the SQL script in the ZIP file “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_with_compression_and_verify.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_with_compression.sql“ and proceed as described above.

Again, you should see “Query executed successfully” in the footer.

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

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