Cloning an SQL Server database

Clone standard SQL Server production databases or availability databases by using Microsoft Volume Shadow Copy Service (VSS) and Windows PowerShell cmdlets. You can create a persistent VSS snapshot of the production database at any time. This snapshot is a clone of the production database. Use this clone to complete database activities you would normally do on the production database without impacting the production database. Use Windows PowerShell cmdlets to query the cloned database, and to mount and unmount it on remote servers.

Before you begin

You must install and enable Windows PowerShell 3.0, or later, on all IBM Spectrum Protect Snapshot installations that you are using. To download, install, and enable the software, follow the instructions in: Microsoft Windows Management Framework 3.0 Downloads .
The database that you are cloning must be on a storage device that is managed by a VSS hardware provider.
Before you create a clone, ensure that sufficient space is available on the storage device.

About this task

You can create as many clones of the production database as you want, providing sufficient storage space is available on your storage device. Each clone is a snapshot on your storage device.
You can create a clone on the same SQL Server where the active database is running only.
You can mount the clone to remote servers so that other users can access and use the clone. Clones are always mounted as read/write.
You can access and use the clone on any SQL Server that is attached to the storage device.
You must delete a clone manually as it does not expire based on policy settings.

Procedure

Open a Windows PowerShell command prompt.
Create a clone of your active production database by issuing the following Windows PowerShell cmdlet. You can specify the name parameter to specify the name of your production database. When created, you can access and work with all the files that are contained in the cloned database without any impact to the production database.
```
New-CloneFromDpSqlComponent
```
Tip: At any time, you can query the cloned databases that are created on the current SQL server and node by issuing the following Windows PowerShell cmdlet. You can view all details about all the clones that are created. You can also specify a particular database name to query all the clones that are created for that database.
```
Get-DpSqlClone
```
Mount the created clone to any server (that is running IBM Spectrum Protect Snapshot Version 8.1.4) by issuing the following Windows PowerShell cmdlet. You can mount the clone as often as you want to one or more servers, but only to one at a time. You can also mount the clone to remote servers that other users can access. The cloned database is always mounted as read/write.
```
Mount-DpSqlClone
```
You can use the -PostProcessScript parameter to specify the name of a command script that you can use to run operations on cloned databases after they are successfully mounted; for example, to attach the mounted database to an SQL Server instance. For the script name, you can specify either a relative directory path (starting from your current working directory) or the full directory path. You can also provide additional parameters for the specified command script. However, only the directory path name of the command script can contain backslash characters. If the command script fails, the cloned database is still mounted, but a warning message is displayed. See the following examples:
```
Mount-DpSqlClone -name db1 
-PostProcessScript .\mypostscript.ps1
```
```
Mount-DpSqlClone -name db1 
-PostProcessScript c:\path\mypostscript.ps1
```
```
Mount-DpSqlClone -name db1 -PostProcessScript 
"c:\path\mypostscript.ps1 -parm1 val1 -parm2 val2" 
```
Unmount the clone from a server by issuing the following cmdlet.
```
Dismount-DpSqlClone
```
You can use the -PreProcessScript parameter to specify the name of a command script that you can use to run operations on mounted cloned databases before dismounting; for example, to detach a mounted database from an SQL Server instance. For the script name, you can specify either a relative path (starting from your current working directory) or the full directory path. You can also provide additional parameters for the specified command script. However, only the path name of the command script can contain backslash characters. If the command script fails, the unmount operation terminates. See the following examples:
```
Dismount-DpSqlClone -mountmpoints 
c:\mnt1 -PreProcessScript .\myprescript.ps1
```
```
Dismount-DpSqlClone -mountmpoints 
c:\mnt1 -PreProcessScript c:\path\myprescript.ps1
```
```
Dismount-DpSqlClone -mountmpoints 
c:\mnt1 -PreProcessScript "c:\path\myprescript.ps1 -parm1 val1 -parm2 val2"
```
Unlike backups, cloned databases do not expire based on policy settings and remain on your system until they are deleted manually. When you no longer require the cloned database, delete it from the SQL Server by issuing the following cmdlet.
```
Remove-DpSqlClone
```
Restriction: The delete operation is possible only from the system where the clone was created.
Tip: To view the details about a specific cmdlet, issue the Get-Help cmdlet with the cmdlet name. To enhance how the information is displayed (particularly in languages other than English), display the help in a separate window by including the -showwindow parameter with the help command.
Get-Help Get-DpSqlClone -showwindow