Microsoft SQL Server

Important

Microsoft does not recommend backing up the user defined SQL database files while performing a full system backup so BMC follows their recommendation and does not support backup of user defined SQL Server databases on a full system backup. BMC does however support backup of the system databases (including master and mdb) to make recovering the machine easier. If you needed to recover the entire system, you will have a working instance off MSSQL. You would only need to restore the user databases using the MSSQL plugin (described here).

Overview#

The Microsoft SQL Server is a fully-functioning Relational Database Management System (RDBMS) that is hosted on a Windows server system. It provides a number of backup and restore options that provide Database Administrators the flexibility they need to plan their database recovery strategy.

This chapter describes how UPSTREAM can be used to provide a dependable backup solution for Microsoft SQL Server databases.

The UPSTREAM Client method for backing up and restoring Microsoft SQL Server databases use the MSSQL plugin. The MSSQL PlugIn is a component of the Client and is independent of the Windows version that it is run on. SQL database backups and restores are performed by the MSSQL PlugIn using a method which does not require disk space or I/O. A communication method called Named Pipes is used instead of disk access to transfer SQL Server database backup data between the SQL Server and the MSSQL PlugIn. This method of SQL Server backup is recommended by Microsoft.

Planning#

The backup plan that you create should cover the following databases:

126_List1_Bullet_1076736 • The master database. This is the most important database maintained by Microsoft SQL Server as it contains the configuration information for all other databases. This database does not support incremental backups, but through special functionality in the MSSQL PlugIn, this database is always backed up in full regardless of the backup type. This database always gets backed up during a Windows Full System Backup.
126_List1_Bullet_1076990 • The msdb database. This database contains all the information about scheduled events that are maintained by the Microsoft SQL Server Scheduler. This database also does not support incremental backups and is always backed up in full regardless of the backup type. This database always gets backed up during a Windows Full System Backup.
126_List1_Bullet_1076813 • The model database. This database always gets backed up during a Windows Full System Backup.
126_List1_Bullet_1063903 • All user defined databases.

To ensure that these databases are consistent (i.e. are operating properly), you should plan on monitoring them periodically. The following T-SQL script commands can be used to check the consistency of a database:

{{id name="MicrosoftSQLServer-126_JCL_1063907"/}}

{{id name="MicrosoftSQLServer-1063907"/}}

DBCC CHECKDB
DBCC CHECKALLOC or NEWALLOC
DBCC CHECKCATALOG\\A backup of a SQL database can be performed while the database is active (on-line). This means that you do not have to plan any down time for a database while it is being backed up.

Each database maintained by Microsoft SQL Server has a configurable recovery model. The recovery model for a database defaults to simple when the database is first created, which means that you may not backup the database incrementally. The recovery model for a database must be changed to full to allow incremental backups.

You should plan how often you want to perform full database backups and incremental transaction log backups. A typical database backup schedule involves performing full database backups once a week and incremental transaction log backups daily. In addition to the backups you perform according to this schedule, there are times when it is important to perform a non-scheduled full database backup as follows:

126_List1_Bullet_1063920 • After initially creating the database.
126_List1_Bullet_1063921 • After performing a non-logged operation.
126_List1_Bullet_1063922 • After creating an index.

Also, a full database backup of the master database should be performed after any of the following actions are performed:

126_List1_Bullet_1063925 • ALTER DATABASE
126_List1_Bullet_1063926 • CREATE DATABASE
126_List1_Bullet_1063927 • DISK INIT
126_List1_Bullet_1063928 • DISK MIRROR
126_List1_Bullet_1063929 • DISK UNMIRROR
126_List1_Bullet_1063930 • DISK REMIRROR
126_List1_Bullet_1063931 • DISK RESIZE
126_List1_Bullet_1063932 • various DBCC options such as SHRINKDB
126_List1_Bullet_1063933 • sp_addlogin
126_List1_Bullet_1063934 • sp_addremotelogin
126_List1_Bullet_1063935 • sp_addsegment
126_List1_Bullet_1063936 • sp_addumpdevice
126_List1_Bullet_1063937 • sp_configure
126_List1_Bullet_1063938 • sp_dropdevice
126_List1_Bullet_1063939 • sp_droplogin
126_List1_Bullet_1063940 • sp_dropsegment
126_List1_Bullet_1063941 • sp_dropserver
126_List1_Bullet_1063942 • sp_dropremotelogin
126_List1_Bullet_1063943 • sp_extendsegment
126_List1_Bullet_1063944 • sp_logdevice

If ever you should need to restore a database, you must ensure that the database is not in use before proceeding. This can be accomplished by using the following T-SQL commands:

{{id name="MicrosoftSQLServer-126_JCL_1063948"/}}

{{id name="MicrosoftSQLServer-1063948"/}}

sp_dboption
sp_who
KILL\\To restore a database, you must first restore from the latest full database backup and then restore from each subsequent incremental transaction log backup in the order they were backed up. While this may sound cumbersome it is easily accomplished automatically by the MSSQL PlugIn. All you need to do is specify the last backup version from which you want to restore and let the MSSQL PlugIn figure out which backup versions it needs to restore from, and then perform as many individual SQL Server restore operations as necessary to complete the task.

If the database you need to restore happens to be the master database or the msdb database, you should first read the Backing Up and Restoring section below to be aware of the special considerations for these databases. The special considerations for the master and msdb databases are also covered in the “Restoring the Master Database” in Section and “Restoring the MSDB Database” in Section sections. Please notice the ‘Master’, ‘MSDB’ and ‘Model’ databases always gets restored as part of a windows bare metal recovery.

Configuration#

Configuring SQL Server

The Windows system on which the Client is run must also have the Microsoft SQL Client installed.

The Microsoft SQL Client Network Library Configuration

Before using the MSSQL PlugIn, you must first ensure that the SQL Server and the SQL Client have both been configured to use the same network protocols. This is accomplished by using the SQL Server Network Utility and the SQL Client Network Utility, respectively. Your Database Administrator will understand how to do this.

The Microsoft SQL Server Connection

In addition to the special considerations outlined in Section “Planning”, you must determine which type of connection you want the MSSQL PlugIn to use to access SQL Server. The two types of connections that can be used are:

126_List1_Bullet_1063977 • An explicit SQL Login connection using a specific SQL Server Login ID and password.

This is not the recommended method as it requires that the Login ID and password be specified in ASCII text in your set of Client parameters. Doing so poses a security risk.

126_List1_Bullet_1063983 • A trusted connection using the SQL Server’s integrated Windows security mode to authenticate the Windows account under which the Client is executed. This is the recommended method. The UPSTREAM (the default name) Windows service must be configured to run under a specific Windows account (not the Local System account) that is associated with a SQL Server Login ID configured for the Windows account. When running the Client as an application (by selecting UPSTREAM Local from the Windows Start menu) the Windows account that you are currently using must have an associated SQL Server Login ID.

Each SQL Server Login ID you create for use by the MSSQL PlugIn must be configured to match the Windows account under which the Client is run. You must then create a database alias of ‘dbo’ (the owner of the database) in each database for this Login ID.

For example, assume that the Windows account under which the Client runs is named “UPSTREAM”. A Login ID of “UPSTREAM” must be created with the default database set to the master database. And then for each database that the UPSTREAM Login ID is to backup or restore, an alias for that database must be assigned to be ‘dbo’. The procedure to be used to create Login IDs is different for each Microsoft SQL Server version.

By configuring a special Login ID for the UPSTREAM user account, the MSSQL PlugIn can establish a ‘trusted connection’ with the server to perform backups, restores and queries. The Login ID does not necessarily have to be “UPSTREAM”. It can be any other Login ID that matches any Windows account name that you choose to run the Client under. You may in fact create two or more Login IDs and may even create a separate unique Login ID for each database you want to backup and restore.

UPSTREAM configuration

There are a number of related steps that need to be performed. These are:

126_List1_Num_First_1064001 1. Determine the UPSTREAM Server storage requirements for your full and incremental database backups.
1. 126_List1_Num_Next_1064002 2. Create an UPSTREAM Server backup profile enabled for merge backups. There are no special requirements for the options used for this backup profile other than the enabling of the merge backup option.
2. 126_List1_Num_Next_1064003 3. A plan for vaulting the UPSTREAM Server backup data sets for the backup profile should be created to ensure that a failure of UPSTREAM Server storage media does not prevent restoration of your SQL Server databases.

These steps are no different from those for any of the servers you are already using UPSTREAM to back up.

Backups and Restores#

MSSQL PlugIn Backups

The backup type for all MSSQL PlugIn backups must be either First-time full, Full merge or Incremental merge. The MSSQL PlugIn does not support Non-merge backups. Whenever the master or msdb databases are included in an Incremental merge backup they are backed up using the full backup method.

Warning! A database (other than master or msdb) must be configured with a Recovery Model of “Full” to be backed up incrementally.

To back up a set of SQL Server databases, use the MS SQL Databases dialog in the UPSTREAM Director (by selecting Backup from the Action menu). Start by selecting a Target Server hosting an SQL Server and a Backup Profile to be used.

Use the Get MSSQL Information button to detect all SQL instances on the selected backup target.

In the Director, the screen will help you substantially in setting up your backup:

In the Director, you must select the Target you wish to examine, and then select the SQL Server Instance visible from that Target to display the list of databases. The list of databases includes <ALL>, which is recommended if you wish to backup all the databases on your system. You can create a single SQL Backup job covering multiple instances by expanding each MS SQL Instance and selecting / adding what need to be backed up.

The Get MSSQL Information dialog displays a tree of available SQL Server instances on the backup target and the databases they own. If a trusted connection is successful a list of that server’s databases is displayed, the status for the server is labeled “Trusted Connection” and the server’s version is displayed. If a trusted connection is not successful, the databases are not displayed and the status for the server is labeled “Not Connected”.

To change the connection options for any server, highlight the server and specify the desired connection options in the Connection Options group. To select a database to be backed up, simply check its check-box. To specify that all the databases for a given server are to be backed up, simply check the server’s check-box. Individual databases may then be excluded by un-checking their check-boxes. Also, when all the databases are selected, an individual database not configured with a Recovery Model of “Full” is automatically excluded.

The DIFFERENTIAL check box is enabled only for Incremental merge backups and when the highlighted database name belongs to a Microsoft SQL Server version 7.0 or higher. DIFFERENTIAL is a special option on the T-SQL BACKUP command that is issued to the Microsoft SQL Server when performing a backup. Refer to the Microsoft SQL Server documentation for the T-SQL BACKUP command for more information about this option.

The MSSQL PlugIn uses the parameters specified on the Specify MSSQL Parameters dialog to construct the PLUGINPARAMETERS and FILES file specification (repeating) parameters. The FILES parameter (the file specification) is formatted as follows:

{{id name="MicrosoftSQLServer-126_JCL_1064052"/}}

{{id name="MicrosoftSQLServer-1064052"/}}

~\~\servername\PIPE\MSSQL\databasename.bin\\The MSSQL PlugIn requires a specific set of file specification parameters that it sets automatically. As a result, the Backup Parameters dialog does not allow you to modify the Backup Specification field or click the Spec Detail button to alter the rest of the file specification parameters.

The MSSQL PlugIn may be used for multiple file specifications as long as the server and database name combinations are unique for each file specification. The MSSQL PlugIn may also be used in conjunction with other file specifications that do not use PlugIns or use other PlugIns as long as the other PlugIns also allow this combination.

Automating Backups

Like all other UPSTREAM backups, those that use the MSSQL PlugIn may also be initiated from the Storage Server via a ustbatch job. The parameters for such a backup or restore are the same as any other backup or restore with the addition of the following parameters in the file specification section (i.e. after the SPECNUMBER parameter):

{{id name="MicrosoftSQLServer-126_JCL_1064065"/}}

{{id name="MicrosoftSQLServer-1064065"/}}

FILES ~\~\backupservername\PIPE\MSSQL\backupdatabasename.bin
PLUGIN MSSQL.dll
PLUGINPARAMETERS SERVER=servername DATABASE=databasename …\\For a backup, the correct format for the FILES parameter is not crucial since the MSSQL PlugIn overrides it.

Each PlugIn module has a specific set of PlugIn specific parameters. The format for the PlugIn parameters (the PLUGINPARAMETERS repeating parameter) for a MSSQL PlugIn backup file specification is:

{{id name="MicrosoftSQLServer-126_JCL_1064073"/}}

{{id name="MicrosoftSQLServer-1064073"/}}

SERVER=[“]severname[“]
DATABASE=[“]databasename[“]
[LOGINID=[“]loginid[“]
LOGINPASSWORD=~[~[“]loginpassword[“]]]
[DIFFERENTIAL]\\Where:

SERVER=[“]servername[“]

This is the name of the server which owns the database to be backed up. This parameter is required. The backup job must be targeted to execute on the server that owns the database.

DATABASE=[“]databasename[“]

This is the name of the database to be backed up or restored. This parameter is required. The name of the database may also be ‘*’ to indicate that all the databases for a given server are to be backed up (as in DATABASE=*).

[LOGINID=[“]loginid[“]]

This is an optional parameter to be used to specify the Login ID to be used for an explicit connection. Its use is not recommended for security reasons; we [LOGINPASSWORD=[[“]loginpassword[“]]]: This is an optional parameter to be used to specify the Login Password to be used for an explicit connection. This parameter must not be specified without the LOGINID parameter. Its use is not recommended for security reasons; we recommend using a trusted connection.

[DIFFERENTIAL]

An optional parameter that may be specified when the backup type is Merge incremental and the Microsoft SQL Server is at version 7.0 or above. DIFFERENTIAL is a special option of the T-SQL BACKUP command. Refer to the SQL Server Books Online documentation to determine how to use this option.

Double quotes may be used to delimit some of the parameter values that may contain spaces or commas.

MSSQL PlugIn Restores

To start a restore of one or more databases that were backed up with the MSSQL Plugin, use the UPSTREAM Director. In the Director, you have the two cooperating windows, the first to select the target, profile, backup and database(s) to be restored:

Once you have added one or more databases to restore, you then select the details about the restore of each database:

The Specify MSSQL Parameters dialog displays a tree of SQL Servers and databases that were backed up in the backup version are listed in the Restore Type field. The MSSQL PlugIn performs a restore by first checking the backup type of the selected backup version to see if it is a full backup version or not. If the backup version selected is a full backup, the restore is performed from that backup version only. If the backup version selected is an incremental backup, it then identifies the previous full backup version and performs as many restores as necessary beginning with the previous full backup version up to and including the incremental backup version selected. This results in two or more restores depending on the number of backup versions between the full backup version and the specified incremental backup version. If you want to change the selected backup version, click the Backups button to select a different backup version.

To select a database to be restored, simply find it in the tree and check its check-box.

Remotely Executed Restores

If you are performing restores from ustbatch or some similar remote execution facility the parameters for such a restore are the same as any other restore with the addition of the following parameters in the file specification section (i.e. after the SPECNUMBER parameter):

{{id name="MicrosoftSQLServer-126_JCL_1064920"/}}

{{id name="MicrosoftSQLServer-1064920"/}}

FILES ~\~\backupservername\PIPE\MSSQL\backupdatabasename.bin
DESTINATION ~\~\restoreservername\PIPE\MSSQL\restoredatabasename.bin
PLUGIN MSSQL.dll
PLUGINPARAMETERS SERVER=servername DATABASE=databasename …\\Where SERVER=servername and DATABASE=databasename are as defined in below in this section.

The correct format for the DESTINATION parameter is not crucial since the MSSQL PlugIn overrides it and forces it to be:

{{id name="MicrosoftSQLServer-126_JCL_1064223"/}}

{{id name="MicrosoftSQLServer-1064223"/}}

~\~\servername\PIPE\MSSQL\databasename.bin\\The FILES parameter for a restore must match the name of a real file in the backup version from which the restored is to be performed.

The PLUGINPARAMETERS parameter must be complete with all the appropriate sub-parameters to perform the backup or restore as desired.

Like backups, a MSSQL PlugIn restore requires a set of PlugIn parameters. The format for the PlugIn parameters (the PLUGINPARAMETERS repeating parameter) for a MSSQL PlugIn restore file specification is:

{{id name="MicrosoftSQLServer-126_JCL_1064235"/}}

{{id name="MicrosoftSQLServer-1064235"/}}

SERVER=[“]severname[“]
DATABASE=[“]databasename[“]
[LOGINID=[“]loginid[“]
LOGINPASSWORD=~[~[“]loginpassword[“]]]
[RESTORETODIRECTORY=[“]directoryname[“]]
[RESTORETOSERVER=[“]servername[“]]
[RESTORETODATABASE=[“]databasename[“]]
[REPLACE]
[KEEP_REPLICATION]
[STOPAT=yyyy/mm/dd,hh:mm:ss |
STOPATMARK=[“]mark[“],yyyy/mm/dd,hh:mm:ss |
STOPATMARKAFTER=[“]mark[“],yyyy/mm/dd,hh:mm:ss |
STOPBEFOREMARK=[“]mark[“],yyyy/mm/dd,hh:mm:ss |
STOPBEFOREMARKAFTER=[“]mark[“],yyyy/mm/dd,hh:mm:ss]\\Where: