Backup / Restore of Microsoft SQL Server (ALL VERSIONS)

Knowledge Base: BACKUP : Backup / Restore of Microsoft SQL Server (ALL VERSIONS)

Creation Date: April 30, 1997

Revision Date: June 29, 2016

Product: DS‑Client (Windows)

Summary

The DS-Client software allows you to perform online backups of Microsoft SQL Server databases.

• “Restoring a Microsoft SQL Server database backup set”

• “Restoring Microsoft SQL Server data to an alternate location”

Security and User Accounts

The DS‑Client software requires either one or two user accounts to successfully backup an Microsoft SQL Server. The number depends on the setup of the Microsoft SQL Server:


Network user account	This account is used when connecting to the computer where the Microsoft SQL server is running. This account must have sufficient permissions (e.g. member of the Administrator Group).
Database user account	This is a database user account. If the Microsoft SQL server is configured for integrated security access, this account is not required and the network user account will be used for both network and database access.

Backup Dump Devices

Depending on the Microsoft SQL Server's version and the configuration of the backup set, DS‑Client will use different backup/restore methods:


Dump to disk file	Databases will be dumped directly to a disk file either on the Microsoft SQL Server computer, or to an accessible UNC path (remote location). The file name will be in the form 'database.y', where 'y' is an internal number. After the database is dumped, the dump file will be backed up like a regular file and then removed. The backup set's configuration should provide the path for the dump location. You can enter a UNC path directly in the “Dump Path” field when you select the dump location.
Dump to DS‑Client buffer	Databases will be dumped directly to a disk file on the DS‑Client computer in the DS‑Client buffer directory (this directory location is set up in the DS‑Client Configuration dialog box ‑ Parameters tab). You can use this option if you do not have enough space on the Microsoft SQL Server computer.
DS‑Client Pipe	The database is dumped through a pipe. During backups, the Microsoft SQL server writes to the pipe, and the DS‑Client service reads from the pipe and sends data to the DS‑System. The pipe is created on the Microsoft SQL Server computer. Use this method if your database is too big to be dumped to a file with either of the above options. NOTE: You should only use this option if you have a consistently fast and reliable connection to the DS‑System.
Backup Transaction Log (Microsoft SQL 2005 and up)	The database must be configured with FULL or BULK_LOGGED recovery mode, otherwise this option will have no effect. • If selected, DS-Client will dump the Transaction Log and send it to DS-System in addition to the database(s). • To view the transaction logs from the Restore Wizard, select the Advanced Option “Latest generation of all data” and click Show Files to display the files. Backed up log files have a *.log extension.

Using DBCC

DS‑Client service can run a Database Consistency Check (DBCC) after the database is dumped. DBCC will check the database and the database allocation ('checkdb' / 'checktable' and 'checkalloc' options). If you are using the DS‑Client Pipe option, DBCC is performed before the dump. In either case, the DS‑Client service can be instructed not to perform the backup if DBCC errors are found.

Database Backup Policy

DS-Client supports additional backup options for Microsoft SQL Server 2005 / 2008 / 2012. These options can help reduce the backup time for very large databases (where the dump takes up significant time).

You can configure the backup set to perform differential or incremental database backups (after the backup of a full dump).

• Differential backups contain all the changes to the database since the last full dump.

• Incremental backups contain only the changes to the database since the last backup.

Database Backup Policy requirements:

• Windows DS-Client only.

• Target Microsoft SQL Server version must be 2005 / 2008 / 2012.

• For Full dump + incremental backup, each target database’s model must be full or bulk_logged recovery.


Full dump always	[Default] This is the default behavior for all Microsoft SQL Server backups. • Each backup of the SQL Server performs a full dump of each database.
Full dump + differential backup	Performs a full dump of the database on first backup, followed by differential backups until another full backup is needed (*). • This policy can be used on Microsoft SQL Server 2005 / 2008 / 2012 databases. • The database can be selected with or without the ‘backup transaction log’ option.
Full dump + incremental backup	Performs a full dump of the database on first backup, followed by incremental backups until another full dump is needed (*). • This policy can be used on Microsoft SQL Server 2005 / 2008 / 2012 databases. • The database should be selected without the ‘backup transaction log’ option. (Even if ‘backup transaction log’ is selected, that option will be ignored if this backup policy is selected.) • The database’s Recovery Model must be: ‘full’ or ‘bulk_logged’.
Transaction Log Only backup	Use this database backup policy in combination with a separate VSS-aware or Classic Microsoft SQL Server backup set that performs Full database backups for the same database(s). When using this database backup policy, you must do the following: • Ensure the integrity of the sequence of the transaction logs in relation to the Full database backups performed by the other Microsoft SQL Server backup set. • Select the option to keep all generations within a specific period and specify that period. • Set the retention settings to keep 0 generations.
(*) A full dump is needed (other than first time) when: • a database has been backed up by another backup set (or manually from the SQL tools); • a database has been restored (either from another database or an earlier generation); • the database properties have been modified (recovery model switched to ‘simple’); • local cache is used for the backup set (This can occur for ‘Online’ backup sets with the option ‘Transmission cache on local storage’, but only when a generation must actually ‘pass through’ the local cache. If the connection to DS-System is down, then a full dump occurs and is written to the local cache. After that dump, DS-Client continues with the selected database backup policy.) • the DS-Client requires a Master generation (configured in the “MasterGenerations” value of the DS-Client Advanced Parameters - Setup > Configuration > Advanced).

NOTE: The DS-Client can take advantage of the differential or incremental backup policy if the backup set (of the SQL database) is the only process that backs up that target. If another backup set is used to backup the database (or the internal SQL commands are used to perform manual backups / dumps), then the backup policy requires the next backup to be a full dump.

Database Backup Policy Full Backup Schedule Over-ride Rules

These rules are optional. They allow greater control when a Full Backup can occur (e.g. not during business hours).

IMPORTANT: Full dumps can be delayed, but a full database dump needs to be performed at regular intervals to protect the integrity of the database backup. By default, DS-Client allows a full backup to be skipped a maximum of 120 consecutive times. This parameter is defined in the DS-Client Advanced Configuration options ‘MaxNonFullDumps’ parameter (see: “DS-Client Advanced Configurations”).

• If you are using any skip full backup rule, you should force a full backup before this maximum interval occurs. Use the Full every... option(s) to specify that time.

Advantages and disadvantages of different database backup policies

Choosing the best database backup policy method depends primarily on the backup window and how often the backups need to be run. For example, a customer who needs to backup a 400GB database may not be able to backup every hour using the Full dump always policy; however, the backup may be able to accommodate an incremental or differential dump every hour.

The following table lists the advantages and drawbacks of each respective DB backup policy:


Backup Policy	Advantages	Disadvantages
Full dump always	• No restrictions on the number of backup sets backing up a particular Microsoft SQL Server.	• Dump time required for each backup.
Full dump + differential backup	• Less dump time of the data from the Microsoft SQL Server, therefore less data transmitted from the source Microsoft SQL Server to the DS-Client (over LAN) and possible storage savings on the DS-System because of the size of the incremental / differential generations. • Even when a Full dump is required, DS-Client still processes “incremental forever” and sends only the delta changes to DS-System.	• Only one backup set can backup the target Microsoft SQL Server, since the backup set must maintain the record of differential backups. No manual backups from the SQL tools can be performed (otherwise a Full backup is required). • Available only for Microsoft SQL Server 2005 / 2008 / 2012 databases.
Full dump + incremental backup	• Less dump time of the data from the Microsoft SQL Server, therefore less data is transmitted from the source Microsoft SQL Server to the DS-Client (over LAN) and possible storage savings on the DS-System because of the size of the incremental/differential generations. • Even when a Full dump is required, DS-Client still processes “incremental forever” and only sends the delta changes to DS-System.	• Only one backup set can backup the target Microsoft SQL Server, since the backup set must maintain the record of incremental backups. No manual backups from the SQL tools can be performed (otherwise a Full backup is required). • Configuration requirements: Database Recovery Model must be ‘full’ or ‘bulk_logged’, which means that more processing will occur on the Microsoft SQL Server and more data and logs will be stored. • Only available for Microsoft SQL Server 2005 / 2008 / 2012 databases.
Transaction log only backup	• Offers more granular control by allowing you to restore the target database to a specific point in time. • Shorter backup time required because the backup of transaction logs requires less data transfer.	• Requires two steps in the restore process: (1) restore a full database using a Microsoft SQL Server (Classic or VSS-aware) backup set, and (2) restore transaction logs from a Microsoft SQL Server (Classic) backup set.
Backup using VSS (Separate VSS-aware backup set)	• Avoids the dump time for the data.	• More data is transmitted between source Microsoft SQL Server and DS-Client.

Restoring Databases

A database that was backed up using the “dump to disk file” option can be restored only using the same method. (i.e. you cannot restore such a backup using “DS‑Client Pipe“.)

The file name generated by the restore using the dump to disk file option will be in the form 'database.y', where 'y' is an internal number.

Using the Transaction Log Only database backup policy

The database backup policy Transaction Log Only in a Microsoft SQL Server (Classic) backup set is one part of a combined solution that allows you to restore a target database to a specific point in time, offering more granular control in the restore process. The backups of transaction logs also require less data transfer time.

This combined solution requires the collaboration of two backup sets:

• A Microsoft SQL Server (Classic or VSS-aware) backup set based on the database backup policy Full dump always

• A Microsoft SQL Server (Classic) backup set based on the database backup policy Transaction Log Only.

NOTE: When using a Microsoft SQL Server backup set based on the Transaction Log Only database backup policy, you must do the following: (1) ensure the integrity of the sequence of the transaction logs in relation to the Full database backups performed by the other Microsoft SQL Server backup set, (2) select the retention option Keep all generations for the last [specific period] and specify that period, and (3) configure the time-based retention setting Keep most recent generations to 0. The backups of transaction logs update the same files over time and do not result in multiple generations, and those files become obsolete after every full dump.

To restore transaction logs to a target database, you need to perform two steps in this order:

1. Restore the full database using the Microsoft SQL Server backup set configured for Full dump always and leave the target database in RESTORING mode.

2. Restore transaction logs to the target database using the Microsoft SQL Server (Classic) backup set configured for Transaction Log Only.

NOTE: Transaction logs can only be restored to the target database after the database has first been restored in full.

Backup / Restore of a Microsoft SQL Server Cluster

You can use the virtual node as the “Path” to back up a Microsoft SQL Server Cluster that is built on a Windows 2008/R2 (or newer) Cluster. You can create a Microsoft SQL Classic or VSS-aware backup set for it using the its virtual node as long as DS-Client can meet two requirements:

DS-Client must be able to resolve the following for the Microsoft SQL Cluster:

• Virtual node, specifically its IP address

• Individual nodes, specifically their names and IP addresses

• The Windows cluster’s name, specifically its IP address

DS-Client must be able to connect to the nodes listed above.