Performing a SQL Server Backup

1.1 Introduction

Backing up your data is incredibly important; while 12d Synergy provides you with version control capabilities, it is still your responsibility to make sure there is a reliable backup available, in the case of disaster (hardware failure, events outside your control, etc).

This section of the guide will aim to discuss how to best backup your data.

There are two components to any successful backup

1. SQL Server Backup
2. File and Index backup

1.2 General Notes

1. Backup to a network share
   Backup to a network share and not to your server's local drive. If your server encounters system problems at an OS or hardware level then you will be able to quickly build a new server from the last network share backup.
2. Keep Multiple Copies
3. Keep backups in more than one physical location to increase safety.
   For example, keep a second copy on a separate network server, in the cloud, or on tape.
4. Test Your Backups
   Test your backup once a quarter. Grab a recent backup and restore it to a server. Don't assume the backup process is working, test it.
5. Monitor Disk Space in Backup Folders
   Regularly monitor that you have not run out of disk space in the folders where backups are stored. Only keep as many old backups as you have disk space for

1.3 Recovery Model

Ensure your 12d Synergy database is in full recovery model. This mode ensures that in an emergency situation it is possible to restore from a backup with the minimum amount of lost data.

Full recovery model is the default setting but you should make sure it is being used.

How to check the recovery model

• Open SQL Server Management Studio, connect to the 12d Synergy database server.
• Right click the 12d Synergy database and select Properties
• Go to the Options tab. Make sure Recovery Model is set to Full
  

1.4 Backup Scheduler Options

Backup strategy refers to the frequency and type of backups that are automatically scheduled.
We recommend the following backup strategy for 12d Synergy databases:

1. Scheduled once a day (midnight): Do a full database backup
2. Scheduled once a day (noon): Do a differential database backup
3. Scheduled every 30 minutes: Do a transaction log backup

Always use this recommended strategy unless advised otherwise by an experienced SQL Server administrator.

To actually make these scheduled backups happen you must select and configure a scheduler. The next section explains the options available to you.

1.5 How to Create a Manual Once-Off Backup

The choices of backup scheduler you have depends on your version of SQL Server.

The non-Express editions available include Standard, Business Intelligence and Enterprise.

How to determine your SQL Server edition

• Open SQL Server Management Studio, connect to the 12d Synergy database server.
• Go to File menu, New query with current connection
• In the database drop down list (1.) select the 12d Synergy database
• Enter the query select SERVERPROPERTY ('edition') and click Execute (2.)
• The edition is printed below (3.)

Option A: SQL Backup and FTP

The simplest way to setup backups is via a third party software application called SQL Backup and FTP, available from https://sqlbackupandftp.com.
This product is free for users with less than 3 databases.
We have used this software successfully for setting up backups. We must however caution that this is a third party product, not affiliated with 12d Solutions and we do not provide support for it. 12d is not liable for any consequences of choosing to use this software.

Option B: SQL Server Agent

Installations with a paid edition of SQL Server can use the built-in SQL Server Agent tool to schedule database backups.
Please see this guide from Microsoft: https://learn.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-ver16

Option C: Scheduled Tasks

Another way to schedule backups is through Windows Scheduled Tasks.
Please see this guide from Microsoft: https://support.microsoft.com/en-au/kb/2019698

1.5 How to Create a Manual Once-Off Backup

This applies to any SQL Server edition.

• Open SQL Server Management Studio, connect to the 12d Synergy database server.
• Right click the 12d Synergy database and select Tasks then Backup. Choose these options:
• Backup type: Full
• Copy-only Backup: Yes
• Backup component: Database
• Destination: Click Add and choose a folder and filename destination that does not already exist
• Click OK