IBM Tivoli Storage Manager Protecting Microsoft SQL Databases Hosted in VMware

Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
IBM Tivoli Storage Manager
Protecting Microsoft SQL Databases Hosted in VMware
April 8, 2014
V1.0
Authors: Zhengwen He, Del Hoobler, Mandeep Jandir, Mimi Nguyen, Jim Smith,
Omar Vargas, Mark Yakushev, Chris Zaremba
1
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
Note: Before using this information and the product it supports, read the information in the Notices section.
Revision History
Revision
Number
1.0
Revision
Date
4/8/2014
Summary of Changes
Changes
marked
Initial version
© Copyright International Business Machines Corporation 2014.
US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule
Contract with IBM Corp.
2
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
Contents
Contents .......................................................................................................................... 3
1.
Introduction ............................................................................................................. 4
1.1
Overview ................................................................................................................................. 4
1.2
Supported products ................................................................................................................. 5
1.3
IBM support of procedures in this document .......................................................................... 5
2.
Configuration .......................................................................................................... 6
2.1
Environment overview ............................................................................................................. 7
2.2
Configuration of Data Protection for VMware for self-contained application support configuration 8
2.3
Verification of Data Protection for VMware backup .............................................................. 10
2.4
Configuration of Data Protection for Microsoft SQL .............................................................. 11
3.
Backup management ............................................................................................ 13
3.1
Scheduling ............................................................................................................................ 13
3.1.1
Scheduling Data Protection for VMware backups .................................................. 14
3.1.2
Scheduling Data Protection for Microsoft SQL ....................................................... 15
3.1.3
Verify virtual machine and log backups .................................................................. 17
3.2
4.
Managing versions of Microsoft SQL log backups ................................................................ 17
Recovery .............................................................................................................. 19
4.1
Restoration of Microsoft SQL database backup from a virtual machine backup .................. 20
4.2
Restoration of Microsoft SQL log backups ........................................................................... 21
5.
Appendices ........................................................................................................... 22
5.1
Appendix A – Recovery using the Data Protection for Microsoft SQL command-line interface22
5.2
Appendix B - Sample script for validating full virtual machine backups are available .......... 26
5.3
Appendix C - Ensuring the Microsoft SQL volumes are not excluded during virtual machine backup
28
5.4
Appendix D - Handling of relocated, new, or deleted database and logs files ..................... 29
5.5
Appendix E - Tivoli Storage Manager file space information ................................................ 31
3
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
1. Introduction
1.1
Overview
As many workloads are being virtualized, the methods that are deployed to protect those applications are
evolving to take advantage of the virtualized infrastructure. Take the example of Microsoft SQL Servers that
are deployed in VMware ESXi virtual guest machines. Data protection products today provide the ability to
not only take application consistent backups of virtual machines hosting Microsoft SQL databases but also
provide the ability to recover individual Microsoft SQL databases from the backup of the virtual machine
image.
One of the key requirements that must be considered for any data protection solution is the recovery point
objectives (RPO), i.e., the time granularity to which you can recover a Microsoft SQL database. One potential
solution is to take virtual machine backups on a frequent basis so that the data protection product can provide
the necessary recovery points (since the recovery point of a virtual machine level backup is at the point of the
backup). Even with the efficiencies of change block tracking and data deduplication, this approach can
become prohibitive if only in the cost of creating and deleting virtual machine snapshots. On the other hand,
most traditional in-guest data protection methods can provide the appropriate recovery point objectives but
these in-guest methods lose the efficiencies that are introduced by backup at the virtual machine level.
The goal of this paper is to provide guidance on how to deploy Tivoli Storage Manager for Virtual
Environments (Data Protection for VMware) and Tivoli Storage Manager for Databases (Data Protection for
Microsoft SQL) in a manner which preserves the backup efficiencies offered by backing up data at a virtual
machine level but also providing more granular recovery points by deploying complementary in-guest backup
methods. Specifically, the goals of this paper are to demonstrate how Tivoli Storage Manager can be used to
accomplish the following:
-
Provide optimized backups of the virtual machine leveraging VMware’s vStorage APIs for Data
Protection and changed block tracking technologies
-
Provide the Microsoft SQL database administrator (DBA) the appropriate tools to augment the virtual
machine backups with SQL log backups
-
Provide the Microsoft SQL DBA the appropriate tools to recover a Microsoft SQL database to a
desired recovery point using the virtual machine backups in conjunction with the log backups
4
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
1.2
Supported products
This document is intended to be used with the following product versions:
-
IBM Tivoli Storage Manager for Virtual Environments – Data Protection for VMware V7.1 (which
includes the Tivoli Storage Manager Backup-Archive Client)
-
IBM Tivoli Storage Manager for Databases – Data Protection for Microsoft SQL Server 7.1
For more information about the supported versions of Microsoft Windows, Microsoft SQL, and VMware ESXi,
please refer to the following external links:
-
IBM Tivoli Storage Manager for Databases – All Requirements Documentation
-
IBM Tivoli Storage Manager for Virtual Environments – All Requirements Documentation
1.3
IBM support of procedures in this document
Before reading this document, it is important to note the following considerations:
1. The user needs to ensure that no other products (other than the documented use of Tivoli Storage
Manager – Data Protection for Microsoft SQL) taking Microsoft SQL backups (other than COPY type)
as this could lead to loss of recoverability to desired recovery points.
2. The user needs to ensure they have properly set up policies to keep sufficient versions of Microsoft
SQL logs and virtual machine backups as outlined in the section entitled Managing versions of
Microsoft SQL log backups.
3. Microsoft SQL databases and logs must be hosted on VMware virtual disks; this data cannot be
hosted on raw device mapped (RDM) disks in physical compatibility mode, independent disks, or on
disks attached directly to the guest via iSCSI.
4. IBM Tivoli Storage FlashCopy Manager for VMware cannot be used in conjunction with the solution
outlined in this paper.
5. Microsoft SQL databases must reside on a single server and cannot be participating in any type of
clustering, for example, failover clusters, AlwaysOn Availability Groups or AlwaysOn Failover Cluster
instances, etc.
In the context of the considerations listed above, the procedures that are documented in this paper are
supported by IBM.
5
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
2.
Configuration
In this section, we outline the products and configuration steps required to:

Configure Data Protection for VMware to properly protect Microsoft SQL databases

Verify that Data Protection for VMware is properly configured to enable restore of individual Microsoft
SQL databases

Configure Data Protection for Microsoft SQL to perform Microsoft SQL log backups and restore
individual Microsoft SQL databases from a virtual machine backup
It is assumed that Data Protection for VMware and Data Protection for Microsoft SQL have already been
installed and configured. This document will not cover the installation procedure and initial configuration but
focus on the additional steps needed to configure the products to meet the objectives of this document.
6
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
2.1
Environment overview
We will use the following use case and configuration as a reference throughout this document:
1. User wants to recover a single Microsoft SQL database on virtual machine vm_sql01
2. Virtual machine vm_sql01 is being protected by Data Protection for VMware under node name
datacenter01 (this is the node name in the Tivoli Storage Manager Server representing the entire
vSphere datacenter). The data mover nodes are called datamover01, datamover02, etc.
3. The virtual machine guest is configured with the virtual machine display name of vm_sql01 and the
Microsoft Windows machine name is SQL01.
a. Data Protection for Microsoft SQL is installed in the guest and has been configured to the
Tivoli Storage Manager server to use node name sql01_SQL.
b. The Backup-Archive client has been configured as the VSS requestor node and is using
node name sql01.
Figure 1 - Sample Environment
7
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
2.2 Configuration of Data Protection for VMware for self-contained
application support configuration
By default, Data Protection for VMware provides application consistency when backing up virtual machines
hosting Microsoft SQL servers which allows recovery of the entire virtual machine with Microsoft SQL in a
consistent state. In order to recover only select databases from this type of backup with Tivoli Storage
Manager without having to recover the entire virtual machine, it is necessary to preserve information from
Microsoft pertaining to the state of the Microsoft SQL server at the time of the virtual machine snapshot and
backup. This information is collected as part of the Microsoft Volume Shadow Copy Services (VSS)
interaction which takes place when a virtual machine snapshot is taken.
In order for Data Protection for VMware to collect the Microsoft VSS metadata (or manifest data) for Microsoft
SQL, it must be configured to obtain this information from the virtual machine during a backup operation.
Technical note: The manifest data has information about the backup operation that extends beyond the life of
the virtual machine snapshot; this information has to be obtained automatically by Data Protection for
VMware after the backup is complete to the Tivoli Storage Manager server and must be obtained directly
from the file system within the virtual machine guest.
Below are the required steps for configuring self-contained application protection. Note that it is assumed
that the options file has already been configured for Data Protection for VMware and already contains
required parameters such as vmchost, vmcpw, etc.
1. Configure Data Protection for VMware to preserve the Microsoft VSS metadata information during a
virtual machine backup for machines hosting Microsoft SQL
a. Locate the options file for the Data Protection for VMware data mover; this is either the
dsm.opt file (Windows) or dsm.sys file (Linux)
b. Specify that the virtual machine backups preserve the Microsoft VSS metadata information
so that this information can be utilized by Data Protection for Microsoft SQL at recovery time
by specifying the INCLUDE.VMTSMVSS option for the virtual machine:
INCLUDE.VMTSMVSS <vm display name> OPTions=KEEPSqllog
For example: INCLUDE.VMTSMVSS vm_sql01 OPTions=KEEPSqllog
c.
Ensure the VMDKs hosting the Microsoft SQL database or log files are not being excluded
from the virtual machine backup operation. There is more detailed information about how to
verify the exclude status of VMDKs in the Best Practices section.
d. Repeat this step for all data movers that are protecting virtual machines hosting Microsoft
SQL servers.
2. On each data mover (for example, datamover01), store the guest virtual machine credentials to Data
Protection for VMware by executing the Tivoli Storage Manager Backup-Archive Client command-line
interface:
dsmc set password –type=vmguest <vm guest display name> <guest admin ID> <guest admin pw>
This operation will store the guest virtual machine credentials encrypted on the machine hosting the
data mover.
Note: To understand the user permissions required for the virtual machine guest to preserve the
Microsoft VSS metadata please refer to this IBM Technote.
8
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
At this point you are ready to verify the virtual machine backup configuration and back up the virtual machine
hosting Microsoft SQL.
You can optionally verify that the self-contained backup is configured, VMDKs are not being excluded, and
other parameters by issuing the backup command via the Backup-Archive command-line client with the
preview option. This operation will not perform a backup:
dsmc backup vm <vm display name> -preview –asnode=<datacenter node>
To perform a virtual machine backup you will typically want to use the Tivoli Storage Manager scheduler to
schedule periodic backups of all of your virtual machines. For the purposes of verification, you can also
backup the virtual machine hosting Microsoft SQL via the Backup-Archive command-line client:
dsmc backup vm <vm display name> –asnode=<datacenter node>
9
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
2.3
Verification of Data Protection for VMware backup
Before you can restore individual Microsoft SQL databases from a Data Protection for VMware V7.1 or higher
virtual machine backup, you must complete at least one successful virtual machine backup which contains
the necessary Microsoft SQL database metadata. This section will demonstrate how to verify that the backup
has captured the appropriate information.
Use the Backup-Archive client query command on one of the data mover nodes:
dsmc query vm <vmname> -detail –asnode=<datacenter node>
In the command output, validate the following responses:
1. The ‘Application(s) protected:’ value must specify ‘(database-level recovery)’
2. Ensure that there are no ‘VMDK Status’ fields for virtual disks hosting Microsoft SQL database or log
files that indicate ‘Excluded’. This would indicate that one or more of the VMDKs needed to recover
a Microsoft SQL database or logs are not being protected.
Query Virtual Machine for Full VM backup
#
Backup Date
Mgmt Class
Size
Type
-----------
------ --- ---------------
---
-------------------
----------
1
02/20/2014 12:43:59
STANDARD
43.94 GB
IFFULL
A/I Virtual Machine
A
vm_sql01
Size of this incremental backup: n/a
Number of incremental backups since last full: 0
Amount of extra data: 0
TSM object fragmentation: 0
Backup is represented by: 328 TSM objects
Application protection type: TSM VSS
Application(s) protected: MS SQL 2008 (database-level recovery)
VMDK[1]Label:
Hard Disk 1
VMDK[1]Name:
[ess800_dev2] vm_sql01/vm_sql01.vmdk
VMDK[1]Status:
Protected
VMDK[6]Label:
Hard Disk 6
VMDK[6]Name:
[ess800_dev2] vm_sql01/vm_sql01_5.vmdk
VMDK[6]Status:
Protected
…
10
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
2.4
Configuration of Data Protection for Microsoft SQL
Once you have configured Data Protection for VMware and verified that you have created a virtual machine
backup suitable for recovery of a single Microsoft SQL database, you need to configure Data Protection for
Microsoft SQL inside the virtual machine guest. This section describes how to configure Data Protection for
Microsoft SQL for this type of recovery.
Log in to the virtual machine hosting the Microsoft SQL database (vm_sql01) and follow these steps:
1. Verify the following software packages are installed:
a. Data Protection for VMware Recovery Agent and License (from the Data Protection for
VMware product package)
b. Data Protection for Microsoft SQL
c.
Tivoli Storage Manager Backup-Archive Client
2. When configuring Data Protection for Microsoft SQL using the Tivoli Storage Manager Configuration
Wizard, make sure to enter the datacenter node name, for example, datacenter01, in the ‘Data
Center Node’ field on the Tivoli Storage Manager Node Names wizard page.
3. Once Data Protection for Microsoft SQL is configured, verify that the ‘Configuring Recovery Agent
rule’ status is Passed.
11
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
Log onto one of the data mover instances and follow these steps (note that these steps have to be executed
from only one instance of a data mover and not repeated for all data mover instances):
1. Give the Tivoli Storage Manager Backup-Archive Client that has been configured as the VSS
requestor node (for example, sql01), access to the virtual machine backups (for example, file space
for vm_sql01) by using the Backup-Archive client command-line set access command. This is
required because the VSS requestor node accesses the virtual machine backups on behalf of Data
Protection for Microsoft SQL. This command will be run from the datacenter node (datacenter01)
directly which requires creation of a temporary options file. Note that you only need to complete this
step to create a temporary options file if the current data mover’s options file (dsm.opt) contains an
ASNODE entry:
a. Copy dsm.opt to dsm.setaccess.opt file
b. Edit the dsm.setaccess.opt file. For any line that contains ASNODE, remove the line.
c.
Edit the dsm.setaccess.opt file to set the NODENAME option to the following entry:
NODENAME <DC_NODE> (for example, datacenter01)
2. Enter the following command:
dsmc set access backup –type=VM <vm_name> <vss_requestor_node> -optfile=dsm.setaccess.opt
Example:
dsmc set access backup –type=VM vm_sql01 sql01 –optfile=dsm.setaccess.opt
ANS1148I ‘Set Access’ command successfully completed.
dsmc query access
Node name: datacenter01
Type
Node
User
Path
-------------------------------------Backup
sql01
*
\VMFULL-vm_sql01\*\*
ANS1148I ‘Query Access’ command completed successfully
Note: In the case that the datacenter node name (for example, datacenter01) password is not known,
the Tivoli Storage Manager server administrator will have to reset the password in order to execute
the set access command.
12
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
3.
Backup management
In this section, we describe how to manage virtual machine backups and Microsoft SQL log backups. The
backup management is achieved by setting up a virtual machine backup schedule and a Microsoft SQL log
backup schedule that are in harmony to protect the Microsoft SQL server in accordance with the recovery
requirements.
3.1
Scheduling
Once the configuration steps from the previous section are completed, you are ready to start scheduling
virtual machine backups and Microsoft SQL log backups. This section describes how to:
1. Schedule virtual machine backups using Data Protection for VMware
2. Schedule Microsoft SQL log backups using Data Protection for Microsoft SQL
13
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
3.1.1 Scheduling Data Protection for VMware backups
To schedule virtual machine backups, log into the Data Protection for VMware user interface and navigate to
the Backup tab and click on “Create Schedule” where you can specify a backup schedule name, source
(which virtual machines to include in the backup schedule), and other scheduling options. Make sure that the
Source of the schedule includes the virtual machine(s) hosting Microsoft SQL.
Figure 2- Create schedule example
Once the schedule is created, the virtual machines(s) will be backed up at the specified time.
14
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
3.1.2 Scheduling Data Protection for Microsoft SQL
Once the virtual machine backup schedule is created, you will need to create the Microsoft SQL log backup
schedule. The frequency of log backups should be dictated by the desired recovery point objectives (RPO).
Note: You must ensure that you have a valid full virtual machine backup before backups of Microsoft SQL
logs can be created.
To schedule Microsoft SQL log backups, launch the Data Protection for Microsoft SQL user interface from the
virtual machine hosting Microsoft SQL and click on the Manage tab :
1. Right click on Scheduling and select Scheduling Wizard option
Figure 3 - Data Protection for Microsoft SQL scheduling wizard
2. Navigate through the Scheduling Wizard to set up the scheduler name and timing.
3. Use the Command-Line box and SQL template to specify the database log backup:
tdpsqlc backup * log /truncate=yes 2>&1
15
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
Figure 4 - Data Protection for Microsoft SQL Standalone Scheduler Wizard
This is one approach to set up a Microsoft SQL log backup schedule. A more global approach is to use the
Tivoli Storage Manager centralized scheduling service which enables you to create a backup schedule for all
the Microsoft SQL server instances on a virtual machine.
16
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
3.1.3 Verify virtual machine and log backups
After you have successfully created a backup of one or more Microsoft SQL logs, verify that you can query
the virtual machine backups and the log backups from the Data Protection for Microsoft SQL interface:
1. From the Data Protection for Microsoft SQL Management Console, select a Microsoft SQL Server
and go to the Recover tab, select View: Databases for the list of Microsoft SQL databases backups
available for restore.
2. Microsoft SQL databases that were backed up using Data Protection for VMware are listed with the
backup method of vmvss.
3. Microsoft SQL logs that were backed up using Data Protection for Microsoft SQL will also be listed in
this panel with the backup method of Legacy.
At this point you are ready to recover one or more Microsoft SQL databases based on your recovery point
objectives (RPO). For more information about recovery, refer to the Recovery section of this document.
3.2
Managing versions of Microsoft SQL log backups
Traditional usage of Data Protection for Microsoft SQL manages expiration of log backups by associating
them with the full database backup. Because log backups are uniquely named, they do not expire due to
version limit nor are they inactivated by another log backup. Data Protection for Microsoft SQL inactivates all
active log backup objects for a database when a new full backup of the database is taken (note: this also
inactivates the prior full database backup). It is also recommended to set the RETONLY value in the
management class used for log backups to match the RETEXTRA parameter used for full database backups.
This mechanism of inactivating logs when the prior full backup is inactivated together with the management
class guidance ensures that log backups will be retained on the Tivoli Storage Manager server as long as the
full database backup with which they are associated are retained.
To achieve the objectives of this document, the log backups created by Data Protection for Microsoft SQL
must be explicitly inactivated because the full database backups are being performed by Data Protection for
VMware. Explicit inactivation of all active log backups for all databases on the Microsoft SQL server
protected by Data Protection for Microsoft SQL can be achieved by issuing the Data Protection for Microsoft
SQL inactivate command.
One major difference between scheduling log inactivation explicitly and implicit inactivation done at the time
of a traditional full database backup is that explicit log inactivation will be done independently of the success
or failure of the virtual machine backup. A simple way to consistently manage backup object retention in this
case is to use time-based retention policies.
17
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
For example, if you want to retain your Microsoft SQL server backups for 30 days, do the following:
1. Define the retention parameters in the management class used for virtual machine backups as
follows:

Retain Extra Versions=30

Retain Only Versions=30

Versions Data Exists=nolimit

Versions Data Deleted=nolimit
Note: use the vmmc option in the data mover option file to specify the management class used
for the virtual machine backups
2. Define the retention parameters in the management class used for Microsoft SQL Log backups as
follows:

Retain Extra Versions=0 (this does not apply to log backups so any valid value is fine)

Retain Only Versions=1

Versions Data Exists=nolimit

Versions Data Deleted=nolimit
Note: Use the following include options to specify the management class for the Microsoft SQL
log backups in the dsm.opt file used by the Data Protection for Microsoft SQL agent:
INCLUDE *:\...\*log
<management class name>
INCLUDE *:\...\log\..\* <management class name>
3. Schedule the following Data Protection for Microsoft SQL command to run daily:
tdpsqlc inactivate *
log=* /OLDERTHAN=30
This configuration allows for a 1-day grace period after the Microsoft SQL log backups are inactivated before
the Tivoli Storage Manager server will delete them.
Note that the scheduled Inactivate and Log backup commands are associated with Data Protection for
Microsoft SQL running inside the virtual machine while the scheduled virtual machine backups are associated
with the Data Protection for VMware client.
18
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
4.
Recovery
After virtual machine backups and Microsoft SQL log backups are completed you can recover a Microsoft
SQL database based on your recovery point objectives (RPO). The recovery procedure contains two steps:
1. Restoration of a full backup of the Microsoft SQL database from the Data Protection for VMware
backup using the norecovery option to specify that roll back does not occur.
2. Restoration and application of log backups to recover the database to the desired point in time.
This section will illustrate an example recovery scenario using these steps.
Note: If you restore the entire virtual machine, the Microsoft SQL Server databases will be restored and
recovered to the point of the virtual machine backup and you will not be able to restore and recover any log
backups that were created after that point. If you want to restore and recover any log backups that were
created after the virtual machine backup, you will need to follow all the steps in this document to restore and
recover the individual database and log backups.
19
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
4.1 Restoration of Microsoft SQL database backup from a virtual
machine backup
A full Microsoft SQL database backup can be recovered from a virtual machine backup through the Data
Protection for Microsoft SQL user interfaces. Note that it is necessary to execute the database recovery with
the norecovery option to achieve the objectives in this document.
To achieve a full Microsoft database recovery from a virtual machine using the Data Protection for Microsoft
SQL graphical user interface:
1. Select a Microsoft SQL server and then select the Recover tab, all backups including all database
backups from a virtual machine backup will be listed.
2. Click restore options to show all options and change the AutoSelect and RunRecovery options to
False
3. Select the appropriate full database backup to restore.
4. Click Restore in the Actions pane
Figure 5 - Recover Microsoft SQL Database with Norecovery option
20
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
4.2
Restoration of Microsoft SQL log backups
Once the full database is restored successfully, transaction logs can be restored in order:
1. Select a Microsoft SQL server and then select the Recover tab.
2. Verify that AutoSelect option is set to False.
3. Change the RunRecovery option to True.
4. Select all the logs that you need to recover
5. Click the Restore action to perform the restore.
Figure 6 - Data Protection for Microsoft SQL log restoration and recovery
21
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
5.
Appendices
5.1 Appendix A – Recovery using the Data Protection for Microsoft
SQL command-line interface
To achieve a full Microsoft SQL database recovery from a virtual machine using the command-line interface:
1. Issue the query command to find the database full backup and log backups. For example, to find all
backups for Microsoft SQL database “wi3094db01’:
tdpsqlc q tsm wi3094db01
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server
Version 7, Release 1, Level 0.0
...
Querying TSM Server for Backups ....
Backup Object Information
------------------------SQL Server Name
........................ SQL01
SQL Database Name ........................ wi3094db01
Backup Method
........................ VMVSS
...
Backup Creation Date / Time .............. 11/14/2013 13:41:18
...
Backup Object Information
------------------------SQL Server Name
........................ SQL01
SQL Database Name ........................ wi3094db01
Backup Method
........................ Lgcy
...
Backup on Secondary Replica .............. No
Backup Object State ...................... Active
Backup Creation Date / Time .............. 11/14/2013 15:46:07
...
The operation completed successfully. (rc = 0)
22
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
2. Issue the database restore command with the norecovery option:
tdpslqc restore databaseName /backupMethod=vmvss /recovery=no
For example, to restore Microsoft SQL database “wi3094db01”
tdpsqlc restore wi3094db01 /backupmethod=vmvss /sqlserver=sql01 /fromsqlserver=sql01
/recovery=no
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server
Version 7, Release 1, Level 0.0
(C) Copyright IBM Corporation 1997, 2013. All rights reserved.
Connecting to SQL Server, please wait...
Querying TSM Server for Backups ....
Connecting to TSM Server as node 'SQL01_SQL'...
Connecting to Local DSM Agent 'SQL01'...
Using backup node 'SQL01_SQL'...
Starting Sql database restore...
Beginning VSS restore of 'wi3094db01'...
Restoring 'wi3094db01' via file-level copy from snapshot(s). This process may take some
time. Please wait
Files Examined/Completed/Failed: [ 2 / 2 / 0 ]
Total Bytes: 3146070
VSS Restore operation completed with rc = 0
Files Examined
: 2
Files Completed
: 2
Files Failed
: 0
Total Bytes
: 3146070
Total LanFree Bytes : 0
The operation completed successfully. (rc = 0)
23
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
3. Issue the command to restore the logs after the full database restore completed successfully. For
example, to restore all logs based on previous restored Microsoft SQL database “wi3094db01”, issue
the following command. Note that you can also use the /stopat option to specify a more granular
point in time.
tdpsqlc restore wi3094db01 log=*
/sqlserver=sql01 /fromsqlserver=sql01 /recovery=yes
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server
Version 7, Release 1, Level 0.0
(C) Copyright IBM Corporation 1997, 2013. All rights reserved.
Connecting to SQL Server, please wait...
Starting Sql database restore...
Connecting to TSM Server as node 'SQL01_SQL'...
Querying Tivoli Storage Manager server for a list of database backups, please wait...
Beginning log restore of backup object wi3094db01\20131114154607\00000DB0, 1 of 3, to
database wi3094db01
...
Beginning log restore of backup object wi3094db01\20131114155130\00000DB0, 2 of 3,
database wi3094db01
to
....
Total database backups inspected:
3
Total database backups requested for restore:
3
Total database backups restored:
3
Total database skipped:
0
Throughput rate:
134.32 Kb/Sec
Total bytes transferred:
385,536
Total LanFree bytes transferred:
0
Elapsed processing time:
2.80 Secs
The operation completed successfully. (rc = 0)
24
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
Note 1: In order to restore an older (inactive) backup using the Data Protection for Microsoft SQL commandline interface (TSPSQLC), you must specify the "Database Object Name" (of the specific backup you want to
restore) in the restore command.
To obtain the "Database Object Name", issue the "tdpsqlc q tsm dbname full /all" command. Once you have
the "Database Object Name" value, specify it on the "/OBJect=objectname" optional parameter of the
TDPSQLC restore command (where objectname is the "Database Object Name" ). For example:
tdpsqlc restore db44 /object=20140311131051 /backupdestination=tsm /backupmethod=vmvss
Note 2: Recovery of a Microsoft SQL database to an alternate location on the virtual machine is not
supported in the procedures outlined in this document.
25
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
5.2 Appendix B - Sample script for validating full virtual machine
backups are available
As noted several times in this document, you must ensure that you have a valid full virtual machine backup
before backups of Microsoft SQL logs can be created. One procedure for checking for the existence of a full
virtual machine backup is to schedule the usage of a script such as shown below. This script will check for
the instance of a full backup and then execute the Microsoft SQL log backups if a full virtual machine backup
exists. This script could be used in conjunction with a scheduler service such as the Tivoli Storage Manager
scheduler:
@echo off
dsmc q vm sql01_SQL -detail -asnode=datacenter01 | find /c "database-level recovery" >
c:\temp.txt
SET /p VAR=<c:\temp.txt
if %VAR% == "1" (
tdpsqlc back * log
) ELSE (
echo "There is no full backup"
set ERRORLEVEL=1
)
26
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
This script will produce the following output:
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server
Version 7, Release 1, Level 0.0
(C) Copyright IBM Corporation 1997, 2013. All rights reserved.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Connecting to TSM Server as node 'SQL01_SQL'...
Using backup node 'SQL01_SQL...
ACO5458W The TSM Server 'backup delete' setting for node (SQL01_SQL) is set to
NO. It should be set to YES for proper operation. Processing will continue.
Beginning log backup for database model, 1 of 2.
Full: 0
Read: 87808 Written: 87808 Rate: 32.54 Kb/Sec
Database Object Name: 20140303011509\000007CC
Backup of model completed successfully.
Beginning log backup for database sqldb test2, 2 of 2.
Full: 0
Read: 88832 Written: 88832 Rate: 132.44 Kb/Sec
Database Object Name: 20140303011511\000007CC
Backup of sqldb test2 completed successfully.
Total
Total
Total
Total
Total
SQL
SQL
SQL
SQL
SQL
backups
backups
backups
backups
backups
selected:
attempted:
completed:
excluded:
deduplicated:
Throughput rate:
Total bytes inspected:
Total bytes transferred:
Total LanFree bytes transferred:
Total bytes before deduplication:
Total bytes after deduplication:
Data compressed by:
Deduplication reduction:
Total data reduction ratio:
Elapsed processing time:
4
2
2
2
0
51.85 Kb/Sec
176,640
176,640
0
0
0
0%
0.00%
0.00%
3.33 Secs
The operation completed successfully. (rc = 0)
You can also use the Tivoli Storage Manager activity log and extended summary table to determine if virtual
machine backups are successful. Please refer to the Tivoli Storage Manager Data Protection for VMware
Reporting Enhancements white paper for examples on how to access information in the activity log and
extended summary
27
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
5.3 Appendix C - Ensuring the Microsoft SQL volumes are not
excluded during virtual machine backup
To preserve the backup efficiencies offered by backing up data at a virtual machine level but also providing
more granular recovery points by deploying appropriate in-guest backup methods it is critical that the virtual
machine virtual disks containing the volumes that contain the Microsoft SQL databases are not excluded from
the Data Protection for VMware backup processing and that the databases are not located on physical
compatibility mode raw device mapping (RDM) disks, independent disks, or on disks attached directly to the
guest via iSCSI.
Ensure that any EXCLUDE.VMDISK statements in the Data Protection for VMware data mover used to back
up the virtual machine do not inadvertently exclude virtual machine disks hosting volumes that contain
Microsoft SQL files, file space, database, log, index, etc. Consider the example:
-
vm_sql01.vmdk contains logical volume C:
-
vm_sql01.vmdk contains logical volumes E: and F:
-
The label for vm_sql01_1.vmdk is “Hard Disk 1”
-
The label for vm_sql01_2.vmdk is “Hard Disk 2”
-
The Microsoft SQL Databases files to be backed up are located on the E: and F: drive.
Ensure that there are no statements excluding vm_sql01_2.vmdk from your virtual machine backup by
ensuring that the data mover does not contain the following or similar statements:
EXCLUDE.VMDISK VM_SQL01 “Hard Disk 2”
EXCLUDE.VMDISK * “Hard Disk 2”
Alternatively, if you exclude most hard disks, you have to explicitly include the desired virtual machine disks
using one of the following statements:
INCLUDE.VMDISK VM_SQL01 “Hard Disk 2”
INCLUDE.VMDISK * “Hard Disk 2”
Note: include and exclude statements are processed from bottom to top as they appear in the dsm.opt file.
Place them in the proper order to achieve the desired goal.
Also, keep in mind that you can specify the exclusion or inclusion of a virtual machine disk from the command
line interface:
dsmc backup vm “VM_SQL01:-vmdisk=Hard Disk 2” –asnode=datacenter01
28
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
5.4 Appendix D - Handling of relocated, new, or deleted database and
logs files
The Tivoli Storage Manager backup solution described in this document consisting of Data Protection for
VMware and Data Protection for Microsoft SQL working collaboratively will properly handle database and log
files that are relocated, created, or deleted after a virtual machine backup. When restoring the backups, keep
the following points in mind:
 the full database restore from the Data Protection for VMware backup will restore all files that existed
at the time of the backup to their original location
 if database or log files were relocated during the backup cycle, Data Protection for Microsoft SQL
restore and recovery processing will place the files in their original locations
 if any new database or log files were created during the backup cycle, Data Protection for Microsoft
SQL restore and recovery processing will recreate the new files
 if any database or log files were deleted during the backup cycle, Data Protection for Microsoft SQL
restore and recovery processing will remove those files
Consider the following example:
1. Data Protection for VMware backs up virtual machine “vm_sql01” which includes Microsoft SQL DB
“moose” at time t1; the Microsoft SQL database consists of the following files at time t1

C:\sqldbs\moose\moose.mdf

C:\sqldbs\moose\moose_log.ldf
2. The database admin decides to relocate the database “moose” at time t2 to the following location

E:\sqldbs\moose\moose.mdf

F:\sqldbs\moose\moose_log.ldf
3. The database admin decides to add two new files to database “moose” at time t3 such that the
database is now made up of the following files:

E:\sqldbs\moose\moose.mdf

F:\sqldbs\moose\moose_log.ldf

E:\sqldbs\moose\moose2.ndf

F:\sqldbs\moose\moose2_log.ldf
4. Data Protection for Microsoft SQL takes a log backup at time t4
5. At time t5 the database admin needs to restore the entire “moose” database

First, restore the full database from the Data Protection for VMware backup with
runrecovery=false

Second, restore and apply the log backup taken at time t4

The resulting “moose” database will be restored to the following location:
i. C:\sqldbs\moose\moose.mdf
ii. C:\sqldbs\moose\moose_log.ldf
iii. E:\ sqldbs\moose\moose2.ndf
iv. F:\ sqldbs\moose\moose2_log.ldf
29
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
In the above example, the full virtual machine restore placed the files to their original location. Applying the
log backup restored the files added after the relocation.
30
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
5.5
Appendix E - Tivoli Storage Manager file space information
The Data Protection for VMware backups are stored under the node name of the vSphere datacenter (for
example, datacenter01). This example shows the file space information for the virtual machine vm_sql01.
tsm: ORION>q file datacenter01 f=d
Node Name: DATACENTER01
Filespace Name: \VMFULL-vm_sql01
Hexadecimal Filespace Name:
FSID: 61
Collocation Group Name:
Platform: TDP VMware
Filespace Type: API:TSMVM
Is Filespace Unicode?: No
Capacity: 0 KB
Pct Util: 0.0
Last Backup Start Date/Time: 01/13/2014 21:29:17
Days Since Last Backup Started: 31
Last Backup Completion Date/Time: 01/13/2014 21:30:26
Days Since Last Backup Completed: 31
Last Full NAS Image Backup Completion Date/Time:
Days Since Last Full NAS Image Backup Completed:
Last Backup Date/Time From Client (UTC):
Last Archive Date/Time From Client (UTC):
Last Replication Start Date/Time:
Days Since Last Replication Started:
Last Replication Completion Date/Time:
Days Since Last Replication Completed:
Backup Replication Rule Name: DEFAULT
Backup Replication Rule State: Enabled
Archive Replication Rule Name: DEFAULT
Archive Replication Rule State: Enabled
Space Management Replication Rule Name: DEFAULT
Space Management Replication Rule State: Enabled
At-risk type: Default interval
At-risk interval:
31
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
The Microsoft SQL log backups are under the Data Protection for Microsoft SQL node name. For example,
this document has been using the node name of sql01_sql for this purpose:
tsm: ORION>q file sql01_sql f=d
Node Name: SQL01_SQL
Filespace Name: SQL01_SQL\data\0001
Hexadecimal Filespace Name:
FSID: 4
Collocation Group Name:
Platform: TDP MSSQL Win64
Filespace Type: API:SqlData
Is Filespace Unicode?: No
Capacity: 0 KB
Pct Util: 0.0
Last Backup Start Date/Time: 10/15/2013 11:20:17
Days Since Last Backup Started: 121
Last Backup Completion Date/Time: 10/15/2013 11:20:20
Days Since Last Backup Completed: 121
Last Full NAS Image Backup Completion Date/Time:
Days Since Last Full NAS Image Backup Completed:
Last Backup Date/Time From Client (UTC):
Last Archive Date/Time From Client (UTC):
Last Replication Start Date/Time:
Days Since Last Replication Started:
Last Replication Completion Date/Time:
Days Since Last Replication Completed:
Backup Replication Rule Name: DEFAULT
Backup Replication Rule State: Enabled
Archive Replication Rule Name: DEFAULT
Archive Replication Rule State: Enabled
Space Management Replication Rule Name: DEFAULT
Space Management Replication Rule State: Enabled
At-risk type: Default interval
At-risk interval:
32
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
Notices
This information was developed for products and services offered in the U.S.A. IBM may not offer the
products, services, or features discussed in this document in other countries. Consult your local IBM
representative for information on the products and services currently available in your area. Any reference to
an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or
service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM
intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify
the operation of any non-IBM product, program, or service.
IBM may have patents or pending patent applications covering subject matter described in this document.
The furnishing of this document does not give you any license to these patents. You can send license
inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10504-1785 U.S.A.
For license inquiries regarding double-byte (DBCS) information, contact the IBM Intellectual Property
Department in your country or send inquiries, in writing, to:
Intellectual Property Licensing
Legal and Intellectual Property Law
IBM Japan, Ltd.
19-21, Nihonbashi-Hakozakicho, Chuo-ku
Tokyo 103-8510, Japan
The following paragraph does not apply to the United Kingdom or any other country where such
provisions are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS"
WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED
TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE.
Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this
statement might not apply to you.
This information could include technical inaccuracies or typographical errors. Changes are periodically made
to the information herein; these changes will be incorporated in new editions of the publication. IBM may
make improvements and/or changes in the product(s) and/or the program(s) described in this publication at
any time without notice.
Any references in this information to non-IBM websites are provided for convenience only and do not in any
manner serve as an endorsement of those websites. The materials at those websites are not part of the
materials for this IBM product and use of those websites is at your own risk.
IBM may use or distribute any of the information you supply in any way it believes appropriate without
incurring any obligation to you.
Licensees of this program who wish to have information about it for the purpose of enabling: (i) the exchange
of information between independently created programs and other programs (including this one) and (ii) the
mutual use of the information which has been exchanged, should contact:
33
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
IBM Corporation
2Z4A/101
11400 Burnet Road
Austin, TX 78758 U.S.A.
Such information may be available, subject to appropriate terms and conditions, including in some cases
payment of a fee.
The licensed program described in this document and all licensed material available for it are provided by
IBM under terms of the IBM Customer Agreement, IBM International Program License Agreement or any
equivalent agreement between us.
Any performance data contained herein was determined in a controlled environment. Therefore, the results
obtained in other operating environments may vary significantly. Some measurements may have been made
on development-level systems and there is no guarantee that these measurements will be the same on
generally available systems. Furthermore, some measurement may have been estimated through
extrapolation. Actual results may vary. Users of this document should verify the applicable data for their
specific environment.
Information concerning non-IBM products was obtained from the suppliers of those products, their published
announcements or other publicly available sources. IBM has not tested those products and cannot confirm
the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the
capabilities of non-IBM products should be addressed to the suppliers of those products.
All statements regarding IBM's future direction or intent are subject to change or withdrawal without notice,
and represent goals and objectives only.
This information is for planning purposes only. The information herein is subject to change before the
products described become available.
This information contains examples of data and reports used in daily business operations. To illustrate them
as completely as possible, the examples include the names of individuals, companies, brands, and products.
All of these names are fictitious and any similarity to the names and addresses used by an actual business
enterprise is entirely coincidental.
COPYRIGHT LICENSE:
This information contains sample application programs in source language, which illustrate programming
techniques on various operating platforms. You may copy, modify, and distribute these sample programs in
any form without payment to IBM, for the purposes of developing, using, marketing or distributing application
programs conforming to the application programming interface for the operating platform for which the
sample programs are written. These examples have not been thoroughly tested under all conditions. IBM,
therefore, cannot guarantee or imply reliability, serviceability, or function of these programs. You may copy,
modify, and distribute these sample programs in any form without payment to IBM for the purposes of
developing, using, marketing, or distributing application programs conforming to IBM‘s application
programming interfaces.
Each copy or any portion of these sample programs or any derivative work must include a copyright notice as
follows:
© (your company name) (year). Portions of this code are derived from IBM Corp. Sample Programs. ©
Copyright IBM Corp. _enter the year or years_. All rights reserved.
If you are viewing this information in softcopy form, the photographs and color illustrations might not be
34
© Copyright International Business Machines Corporation 2014
Tivoli Storage Manager– Protecting Microsoft SQL Databases Hosted in
VMware
displayed.
Trademarks
IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines
Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of
IBM or other companies. A current list of IBM trademarks is available on the Web at “Copyright and
trademark information” at www.ibm.com/legal/copytrade.shtml.
Java and all Java-based trademarks and logos are trademarks or registered trademarks of Oracle and/or its
affiliates.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
35
© Copyright International Business Machines Corporation 2014