Creating a database backup

    In this video we explain how to back up your JTL-Wawi database.

    Creating a data backup for JTL-Wawi

    Your task/initial situation

    Case A – Data backup: You would like to set up a regular database backup for JTL-Wawi and learn how to create a backup of the database manually and automatically. Case B – Creating a backup for transfer to third parties/other operating environments: You would like to transfer a database backup to colleagues or external service providers. Read more on this topic here: Restoring a database backup.

    Choosing a backup strategy

    We recommend that you start thinking about a backup strategy early on. There are several layers to a backup strategy, and it can be more or less complex depending on the size of your business and order volume. Always ask yourself the following questions:

    • How often should I back up my data?

    For a smaller company, one backup per day for the productive tenant of JTL-Wawi is sufficient. For example, one regular backup in the evening. The productive tenant holds the dataset of the productive operating environment in your company. In general, smaller companies only use one tenant. For larger businesses, shorter intervals might be a better option. A good way to determine the right interval is to ask yourself: How much data would I have to reconstruct if I restored the last backup?

    • How do I back up my data?

    You can perform backups manually or automatically. The possible automation options depend on your SQL server version and, if applicable, the additional software used. If you back up your data manually, you need to ensure that there is a designated employee who always performs the update, and another who can cover this role in their absence. We generally recommend automating the backup.

    • Where do I store my backups?

    You should never store the backups on the same hard drive as the original database (which is the server). Ideally, the backups are stored on a different server at a different location than the database server. This helps to avoid the destruction of both the original and the backup database, for example in case of a fire.

    Please note: You should check on a regular basis whether your backups are being created correctly. The easiest way to do this is to restore a backup. Use a test instance of Microsoft SQL Server which is run, for example, on a different computer.
    • How many backups should I keep?

    If there is unlimited storage space, you can keep every backup. However, it is usually sufficient to keep only the last few backups. For example, the backups of the last two weeks. In addition, we recommend archiving a backup separately, e.g. once a month. This is useful if older data needs to be reconstructed.

    • Creating backups before high-risk activities

    There are activities that can pose a significant risk to the data integrity. These are all actions in which large amounts of data can be changed at once. These typically include:

    Updating to a different version: During a version update, some of the data structures can be changed significantly for technical reasons. Before updating JTL-Wawi, you should always perform a data backup. With newer versions of JTL-Wawi, a backup is carried out automatically during the update process as long as you do not deactivate this option.

    Adding new systems with data synchronisation: You should always carry out a data backup before activating a new system that writes data into your productive operating environment. Many systems do not only read large amounts of data from JTL-Wawi, but also send just as much data back to JTL-Wawi and thus significantly change the database. This group includes all data synchronisations with online shops, cash register systems, marketplaces, the JTL-Fulfillment Network, the JTL-Vouchers solution, the JTL-Customer Centre/Customer Account and all third-party systems connected via API that have a write access to JTL-Wawi.

    Changing large amounts of data with JTL-DataTransfer: JTL-Wawi’s import/export tool is a powerful tool that can quickly change large amounts of data. However, if configured incorrectly, faulty changes are made on a large scale. You should always carry out a data backup before each import with JTL-DataTransfer, especially if you are not very familiar with the tool yet.

    Please note: A connected system landscape consisting of other systems with their own data storage in addition to JTL-Wawi can only be considered fully secured if a data backup is created for each of the systems at around the same time. Exceptions are systems that only import data from a leading system.
    • How do I prepare backups for sharing?

    With the usual data backup, the current data status is backed up and set aside for emergencies. If an emergency occurs and you decide to restore your data from a data backup, the database backup will be restored in the productive operating environment. Another purpose of a backup is to pass it to third parties. For example to reproduce errors in support cases, to prepare updates in a staging environment or as a template of true to original data as part of a development project, e.g. a new online presence or setting up JTL-WMS.

    Attention: If you pass on data to third parties, please make sure that this copy of the data does not affect your productive operation! To do this, it is necessary to remove any configuration for data synchronisation from your database backup. Read more on this topic here: Restoring a database backup.

    Manually performing a JTL-Wawi backup

    1. Open the database administration of JTL-Wawi via Start > Database.
    2. Log in with your SQL server user data.
    1. Select the tenant for which you want to create the backup. In the Editing tenants section, click Database export.
    Please note: If you want to share the database backup with third parties or use it for any purpose other than backup-only, make sure to remove all synchronisation configurations. Read more on this topic here: Restoring a database backup.
    1. Select a storage location for your backup and give it a name. If you do not choose a different storage path, the default directory will be used: “c:\programme\microsoft sql server\instanz\backup”.

    Reorganise and create new indices before the backup: Here you can specify whether the database indices should be optimised before the backup. We recommend activating this option to prevent problems when restoringa backup.

    1. Click Start. If a backup was successful, a database backup will have been created in the form of a .bak file.
    Please note: If you change the path of the backup, the selected directory must have sufficient rights. Otherwise, the backup cannot be created. The default backup directory “c:\programme\microsoft sql server\instanz\backup“ always has sufficient rights.

    The backup can be restored at any time via the DB import option.

    For further information on importing backups, please read:

    Automatically backing up JTL-Wawi data

    If you use at least a standard licence for Microsoft SQL Server, you can automate the backup in SQL Server Management Studio. The automatic backup is then executed on the server by the server agent. Read our SQL server tips.

    For users of Microsoft SQL Server Express, there is a workaround for the automatic database backup (main database and/or tenant databases) in the form of a scheduled Windows task. This comprises the daily creation of a new backup file in the backup directory. This is also possible without using additional tenants. The default database is called “eazybusiness”. This means that in the code below, the respective lines for the tenants would be missing.

    JTL-Wawi uses the following names for databases:

    • eazybusiness: Main database. This database contains all the information on tenants (the database administration stores data here) and on the first tenant (main tenant). This database is created automatically after installing JTL-Wawi.
    • tenant_x: Tenant databases. Every additional tenant has their own database. The main database “eazybusiness” notifies JTL-Wawi that additional tenants exist. The tenant databases named tenant_x are created automatically when a new tenant is added.

    Please note that a backup of the main database can only be imported by JTL-Wawi as a main database.

    .bat file:

    The respective backup folders must be created beforehand, e.g. d:\backup\eazybusiness\tenant_1, etc.

    tenant_1 to 6 are only examples for tenant databases.

    When there are fewer tenants, the code can be shortened after osql.exe to the actual number of existing tenants.

    @echo off
    osql.exe -S (local)\JTLWAWI -U sa -P sa04jT14 -Q "BACKUP DATABASE eazybusiness TO DISK = 'd:\backup\db\%date%.bak'"
    osql.exe -S (local)\JTLWAWI -U sa -P sa04jT14 -Q "BACKUP DATABASE mandant_1 TO DISK ='d:\backup\mandant_1\%date%.bak'"
    osql.exe -S (local)\JTLWAWI -U sa -P sa04jT14 -Q "BACKUP DATABASE mandant_2 TO DISK = 'd:\backup\mandant_2\%date%.bak'"
    osql.exe -S (local)\JTLWAWI -U sa -P sa04jT14 -Q "BACKUP DATABASE mandant_3 TO DISK = 'd:\backup\mandant_3\%date%.bak'"
    osql.exe -S (local)\JTLWAWI -U sa -P sa04jT14 -Q "BACKUP DATABASE mandant_4 TO DISK = 'd:\backup\mandant_4\%date%.bak'"
    osql.exe -S (local)\JTLWAWI -U sa -P sa04jT14 -Q "BACKUP DATABASE mandant_5 TO DISK = 'd:\backup\mandant_5\%date%.bak'"
    osql.exe -S (local)\JTLWAWI -U sa -P sa04jT14 -Q "BACKUP DATABASE mandant_6 TO DISK = 'd:\backup\mandant_6\%date%.bak'"

    Attention: Adjust the path D:\Backup\eazybusiness\ for your system. The backup files will be saved to this folder. Try performing a manual test backup of the JTL-Wawi database and saving it to the respective folder first. If this is not possible and an error message is generated, please read the troubleshooting page: Backup not possible. If you have changed the database password, use your changed password instead of “sa04jT14”.

    @echo off
    osql.exe -D eazy -U sa -P sa04jT14 -Q "BACKUP DATABASE eazybusiness TO DISK = 'd:\backup\db\%date%.bak'"
    Attention: Adjust the path d:\backup\eazybusiness\db\ for your system. The backup files will be saved to this folder. If you changed the database’s password, use the changed password instead of “sa04jT14”.

    The tenant backup data can be restored via the Database administration.


    Creating a database backup

    In this video we explain how to back up your JTL-Wawi database.