IBM Spectrum Protect Snapshot (formerly called Tivoli Storage FlashCopy Manager) can clone Microsoft SQL Server databases much faster, and with much less disruption to the production database, than traditional replication-based cloning techniques. Using the advanced copy capabilities of storage hardware, clones can be created in just minutes and with minimal CPU usage.
To start, you must be familiar with Microsoft SQL Server, IBM Spectrum Protect, and Microsoft Windows (including Volume Shadow Copy Services (VSS), DISKSHADOW, and DISKPART). Know your storage hardware and its capabilities and limitations. In the examples outlined, I use a mix of GUI and command-line commands. If you want to automate the cloning, the entire process is scriptable. In my example, I create a clone on the same SQL Server machine. It is also possible to create a clone on a different SQL Server. Let me know in the comments section if you’d like to see that scenario, or any other scenario.
Overview
At a high level, the steps are:
- Back up the SQL Server database to be cloned using IBM Spectrum Protect Snapshot
- MOUNT the backup on the local machine with IBM Spectrum Protect Snapshot
- (Optional) MASK the backup from the local machine using DISKSHADOW
- (Optional) Delete the backup to remove it from IBM Spectrum Protect’s database
- (Optional) IMPORT the shadow copies back to the local machine with VSHADOW
- (Optional) EXPOSE the shadow copies and assign drive letters or mount points with DISKSHADOW
- CLEAR the READONLY bit on the imported volumes with DISKPART
- Use the SQL Server ATTACH command to create the clone database using the imported data and log files
- When you are finished, use the SQL Server DETACH command to remove the clone database from SQL Server
- DELETE the shadow copies with DISKSHADOW
Theoretically, it is possible to perform the cloning operation using snapshots without using IBM Spectrum Protect Snapshot. However, when dealing with dozens or hundreds of databases, each of which might use half a dozen file systems or more, it becomes very unwieldy. Furthermore, as the databases grow and change, adding, removing, and relocating files, the odds of making a mistake are very high. IBM Spectrum Protect Snapshot eliminates the complexity involved. Combined with VSS, it is no longer necessary to keep track of which databases are located on which file systems, and in turn which file systems are located on which LUNs.
Process
1. Create a backup of the desired database. The backup type should be Copy-Only Full, so that it does not affect the regular backup sequence. The backup destination should be LOCAL, or BOTH, which instructs IBM Spectrum Protect Snapshot to keep the shadow copies on local disk. The backup method must be VSS.
Be sure to set the “Import VSS Snapshots only when needed” flag. Under the covers, this causes the VSS transportable flag to be set, which is necessary to transport the VSS snapshot to another server. In version 4.1.3 and later, this setting is enabled by default, but in prior versions the default was disabled.
2. Import the shadow copies into the local machine (or remote machine) using the MOUNT command. This action makes the shadow copy available to the system.
At this point, if you are sure that 1) you or any other administrators will not be confused into thinking that this backup is available for restore and 2) your CopyOnly-Full backups use a different management class than your regular backups, so that your backup retention policy is not affected, you can actually skip steps 3-6 and go directly to 7.
Always be aware of the limitations of your storage hardware. In some cases, you storage hardware may also impose limits on the order that your snapshots can be deleted. Whether you perform steps 3-6 or not, it is important to make note of these limitations or else some of your backups may disappear unexpectedly. In particular, you should not use this procedure with storage systems that have snapshot dependency chains, unless you fully understand both the dependency chain limitations and the ramifications of this procedure.
Steps 3 to 6 are required to remove the clone shadow copy from IBM Spectrum Protect Snapshot’s control (and awareness). Since the cloning operation modifies the files located on the shadow copy, it can no longer be used for restore. We remove it from IBM Spectrum Protect Snapshot so that an administrator does not think that it’s available for restore, and so that it does not disrupt any retention policies.
3. In order to remove it from IBM Spectrum Protect Snapshot, we “hide” the shadow copies, delete the corresponding entry in the internal database, and then “unhide” the shadow copies. The “hide” step can be performed using the DISKSHADOW MASK command. You can determine which shadow copy to mask by checking the time stamp. The “Creation time” will be shortly after the backup date/time.
DISKSHADOW> mask {eb5b1f79-d543-42b0-a431-6d0790560bea}
The shadow copy has been removed from the system.
If you look in the baclient dsmerror.log file, you will notice this error:
10/13/2015 16:50:35 FMV1449W A non-critical, unexpected error was encountered.
Deletion of a snapshot(Set/Volume) failed. Probable cause, snapshot does not exists anymore. Continuing Process...
FCM snapShot ID : {ffc61c9d-b10b-4f9c-a2ed-9646a30880c0}
FCM function name : vssDeleteSnapshot()
FCM function : Deletion of SnapshotSet failed, Continuing...
FCM return code : 131
FCM file : ..\..\common\winnt\vssback.cpp (7134)
Of course, this error is expected, as IBM Spectrum Protect Snapshot could not find the snapshot it was trying to delete. It is safe to ignore this error. If you plan to mount the shadow copies back to a different location, then you may also wish to clean up the mount points as well.
4. Delete the CopyOnly Full backup in IBM Spectrum Protect Snapshot. This removes the entry from IBM Spectrum Protect Snapshot’s internal database, but does not touch the shadow copy LUNs. This is because we masked the LUNs from the local machine, and they are on the SAN at the moment.
Before you delete the backup, though, you need to save the backup documents file. This file is called TSM_Backup.xml, and is located in C:\Program Files\Tivoli\tsm\baclient\adsm.sys\vss_staging\<SQL nodename>\<IBM Spectrum Protect server name>\<backup timestamp>. See Step 5 below for more details, where this file is used.
At this point, the backup (and corresponding shadow copy) has been completely removed from IBM Spectrum Protect Snapshot. The remaining steps are strictly Windows and SQL Server commands. It is important to delete the backup from IBM Spectrum Protect Snapshot; otherwise, an administrator might think that a backup is available for restore, when in fact it is not.
Now that IBM Spectrum Protect Snapshot is no longer aware of the shadow copy, it won’t list the backup as available for restore, and it won’t interrupt your retention policies.
5. Make the shadow copy available again to the server. The VSS operation for this step is the IMPORT command. Unfortunately, the DISKSHADOW tool requires a metadata file that uses its custom format for this step. Instead, we revert to Microsoft’s older VSS tool, called vshadow. On my Windows Server 2012 R2 server, this file is installed by default in "c:\Program Files (x86)\Windows Kits\8.0\bin\x64\vshadow.exe", but your mileage may vary. You may need to download it from the Microsoft web site. It is part of the VSS SDK 7.2.
The vshadow tool takes as input the Backup Components xml file created at backup time. By default, IBM Spectrum Protect Snapshot stores this file in the C:\Program Files\Tivoli\tsm\baclient\adsm.sys\vss_staging\<SQL nodename>\<IBM Spectrum Protect server name>\<backup timestamp>. In some older versions, the file was stored in c:\adsm.sys\.
In order to import the shadow copy, run the following command:
Vshadow –i=TSM_Backup.xml
VSHADOW.EXE 3.0 - Volume Shadow Copy sample client.
Copyright (C) 2005 Microsoft Corporation. All rights reserved.
(Option: Import shadow copy set from file 'TSM_Backup.xml')
Reading the file 'TSM_Backup.xml' ...
- Setting the VSS context to: 0xffffffff
Importing the transportable snapshot set ...
(Waiting for the asynchronous operation to finish...)
Shadow copy set succesfully imported.
6. Now the LUNs are mapped to the local machine, but we need to assign drive letters or mount points to access the data. Previously, in step 2, I mounted the shadow copy volumes as c:\mnt1. This time around, I will expose the shadow copy volumes as H:\ and I:\.
DISKSHADOW> expose {8b8835d3-6e3c-4570-b080-484bfbafcf2e} H:
The shadow copy was successfully exposed as H:\.
DISKSHADOW> expose {7fcbe688-07ab-434c-adaf-652b85ead0ea} i:
The shadow copy was successfully exposed as i:\.
7. The shadow copies are exposed as H: and I:, but by default the volumes are read-only. We need to disable the read-only bit in order to create the clone. We use the DISKPART ATTRIB command to accomplish this.
If you get the attributes of the volume, you’ll see that the read-only bit is set:
DISKPART> attrib volume
Read-only : Yes
Hidden : No
No Default Drive Letter: Yes
Shadow Copy : Yes
You can clear this attribute using the CLEAR command:
DISKPART> list volume
Volume ### Ltr Label Fs Type Size Status Info
---------- --- ----------- ----- ---------- ------- --------- --------
Volume 0 D DVD-ROM 0 B No Media
Volume 1 System Rese NTFS Partition 350 MB Healthy System
Volume 2 C NTFS Partition 99 GB Healthy Boot
Volume 3 E Data NTFS Partition 99 GB Healthy
Volume 4 F DATA ReFS Partition 6110 MB Healthy
Volume 5 G LOG ReFS Partition 6110 MB Healthy
Volume 6 H DATA ReFS Partition 6110 MB Healthy
C:\mnt1\FCM-vol-2\
Volume 7 I LOG ReFS Partition 6110 MB Healthy
C:\mnt1\FCM-vol-1\
DISKPART> select volume 6
Volume 6 is the selected volume.
DISKPART> attrib volume clear readonly
Volume attributes cleared successfully.
DISKPART> select volume 7
Volume 7 is the selected volume.
DISKPART> attrib volume clear readonly
Volume attributes cleared successfully.
DISKPART> attrib volume
Read-only : No
Hidden : No
No Default Drive Letter: Yes
Shadow Copy : Yes
8. The shadow copy is successfully imported, exposed, and writeable. We can now create a clone database using the database files on the shadow copy.
Note that Microsoft does not support cross-server restores with VSS backups. Instead, you should use the ATTACH command to make the clone in SQL Server as shown:
9. Once you are done with the clone, use SQL Server to DETACH the clone.
10. Now that SQL Server is no longer using the shadow copy files, use DISKSHADOW to delete the shadow copies. In my example, the shadow copies are the only ones on the system, so I can use DELETE SHADOWS ALL. If you have other shadow copies on your system, you should delete only the relevant shadow copy set.
DISKSHADOW> delete shadows all
Deleting shadow copy {8b8835d3-6e3c-4570-b080-484bfbafcf2e} on volume \\?\Volume
{b8b77ad1-9cf5-43c4-b5b8-d3c200b1b349}\ from provider {d90dd826-87cf-42ce-a88d-b
32caa82025b} [Attributes: 0x00190029]...
Deleting shadow copy {7fcbe688-07ab-434c-adaf-652b85ead0ea} on volume \\?\Volume
{8b127714-f202-4574-896c-263ff610993d}\ from provider {d90dd826-87cf-42ce-a88d-b
32caa82025b} [Attributes: 0x00190029]...
Number of shadow copies deleted: 2
If you skipped steps 3-6 and left the shadow copies under IBM Spectrum Protect Snapshot’s control, you can use IBM Spectrum Protect Snapshot to perform this step instead. Simply delete the CopyOnly Full backup.
Conclusion
At this point, I’ve demonstrated how to quickly and easily clone a SQL Server database using IBM Spectrum Protect Snapshot. Using the techniques outlined here, you can also create clones in fully automated, multi-database, and multi-server configurations.
This information is presented as-is, WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED. If you need support, please contact IBM, Microsoft, or your storage vendor as appropriate.
References:
How to: Clone an Existing Database
https://msdn.microsoft.com/en-us/library/hh272696%28v=vs.103%29.aspx?f=255&MSPPError=-2147217396
Diskpart command reference
https://technet.microsoft.com/en-us/library/cc766465%28v=ws.10%29.aspx?f=255&MSPPError=-2147217396
Diskshadow command reference
https://technet.microsoft.com/en-us/library/cc772172.aspx
Software Versions Used:
IBM Spectrum Protect Snapshot 4.1.3
IBM SAN Volume Controller 7.5.0.2
IBM VSS Provider 4.10.0.0001
Microsoft SQL Server 2014
Microsoft Windows 2012 R2