Plugin for MS SQL Server

Syncrify can efficiently backup and restore a running instance of Microsoft SQL server. This page demonstrate how to configure the SQL server plugin in Syncrify. It also talks about some pre-requisites and other important information to keep in mind.

Prerequisites

• SQL Server and Syncrify client must be on the same machine
• TCP/IP must be enabled in SQL Server.
• You must use SQL Server Authentication. The plugin does not work with Integrated Security.
• The user running the service for SQL server must have write permission to the folder where backup is created. Click here for details.

Background Info

Backing up SQL server is a two-step process:

• First, Syncrify issues a BACKUP DATABASE command against the server, which creates a local file containing a backup of the database. The output of this step is typically a .bak file
• Syncrify client then copies this file to a remote machine.

Configuration

Important!

The pop-up menu in Syncrify client is context sensitive. Select the top most node in the tree view to bring up the pop-up menu. This will display the sub-menus for plugins

• Start Syncrify Client, and select your desired profile.
• Click the right mouse button on the left hand side.

  

• This invokes the following screen

  

  Field Definition

  Friendly name:	A name that identifies this backup entry. This name is used as part of the folder to store the backup on the server. Therefore, this name must conform to the rules used to specify file names. For example, you cannot have \ / : * ? " < > | characters.
  SQL Server host:	This MUST be localhost. You cannot backup a SQL server running on a different machine.
  TCP/IP Port:	Refers to the TCP/IP port where server is running. You must enable TCP/IP as the protocol in SQL server.
  Login:	A valid user in SQL server that has permission to backup
  Password:	User's password
  Database:	Database name in SQL server. Although you can backup any database, including master and msdb, you cannot use Syncrify to automatically restore a master database. See below to see what is an automatic restore
  Instance name:	Leave this field blank if your SQL server does not use an instance name
  Temp file path:	This is the location where Syncrify creates a backup of the database. Since this backup file is created by SQL server and not Syncrify, the user that runs SQL Server must have permission to write in this folder. Typically, the service for SQL Server is run by NT AUTHORITY\NETWORK SERVICE and by default this user does not have permission to write in folders other than the ones belonging to SQL server. In order to avoid permission denied messages, you have one of two option: Specify the path designated by SQL Server to hold backup, which is similar to C:\Program Files\MS SQL Server\MSSQL11\Backup Explicitly grant permission to NT AUTHORITY\NETWORK SERVICE on a specify folder
  Auto Restore	Unintentional restores in a database can be dangerous. This checkbox provides a safeguard against such intentional restores. When this box is not checked, a restore will only cause the .BAK to get downloaded from the remote Syncrify server to client. An actual database restore won't occur. A DBA can then manually restore the database from the .BAK if needed. When this box is checked, besides restoring the .BAK file, Syncrify will run a RESTORE DATABASE command against SQL server.
  Retain work files:	Backing up SQL Server is a two-step process. First, Syncrify runs a BACKUP DATABASE command against SQL Server. This creates a .BAK file on the client machine contain a backup. Syncrify then backs up this local file to the remote machine in the second step. When this file is checked, Syncrify won't delete the .BAK file that is created in the first step. If you have enough hard-disk space to hold this temporary files, we recommend you leave this option checked.

Directions

The Direction option in Syncrify makes this plugin for MS SQL Server acts differently. Following table describes this behaviour.

Client to Server	This is the default backup direction. Syncrify asks SQL Server to create a .BAK file, which is then transferred to the remote machine.
Server to Client	This is like a restore. Syncrify pulls the .BAK file from the server and if Auto Restore is checked, runs a DATABASE RESTORE command against the database.
Two-way sync	We discourage using two-way sync when using this plugin. Since a .BAK file is created on demand, Syncrify will always end-up pushing the local copy to the server. This can create unwanted results.

Manual restoration

When Auto Restore option is turned off, your must manually restore a database. Refer to the documentation of MS SQL Server to see how to restore a database for a detail description. In short, you can run the following script: