Introduction

This article describes recommended backup strategy for a WATS on-premises installation. When the WATS server is running locally you also need to manage backup locally. The WATS on-premises datastore is based on Microsoft SQL Server. WATS is logically divided in two databases, but may have been combined into one. These two databases are referred to as reportstorage and TDM. 

See Also:  

WATS Server Requirements

Microsoft SQL Server configuration for WATS

Microsoft SQL Server backup

Microsoft SQL Server databases is running in one of three different recovery models: Full, bulk-logged or simple. When a database is running in Full recovery model each transaction is logged, and a restore can be made to "any point in time". This does however quickly fill up transaction logs, which may lead to service interruption if the system is not backed up, monitored and maintained regularly. Simple recovery model use transaction log space only for each transaction, and the allocated space is reused once the transaction is committed or rolled back. Database restore is only possible to most recent backup (full or differential backup), transaction log backup is not possible in simple recovery model.

Refer to Microsoft documentation for further details regarding recovery models: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server

Backup strategies for WATS on-premises server

What backup strategy to select depends a lot on how the site infrastructure is configured, and disaster recovery requirements. As more installations are implemented in virtualized environment, backup is often moved from server to host using snapshot technologies. Normally, the backup software will inform the SQL Server running inside the VM that backup has been performed, but some setups may need additional configuration to make these technologies work correctly together.

These backup strategies are meant as guidelines, your IT department may have other strategies or requirements. However, you should decide whether Full recovery model is desirable or if simple is sufficient.

Basic database backup using SQL Server file backup

This is the entry level backup, only recommended for local/site-servers where data is transferred to the next level. Recovery time may be higher and has low level of protection from hostile cyberattacks.

External database backup

By using external database backup software, the backup strategy and protection level is controlled centrally, usually by the IT department. This is the recommended solution if the infrastructure is in place. It is recommended to combine with periodic bare metal recovery. 

Virtualization environment backup or snapshot

In a fully virtualized environment, this may be an acceptable solution. Depending on the virtualization environment this may limit the restore to any point in time. It is also essential to verify that the database engine is detecting the backups if recovery model is set to Full. Depending on backup intervals it may be better to set the recovery model to simple in this scenario.