DB restore using xml file..issues with powershell script

by vmusunoor at 2013-04-22 11:04:40

Hi,

We have a project to automate the database restore, but its not straight forward…we restore database using third party tool…so we have seperate GUI…so we thought of automating it from command line…since we don’t have flexibility with command line …i’m trying to use powershell.
Our third party tool Commvault can generate a script in the form of xml file…so i’m using that xml file and trying to write the script.
when i execute the below script …i’m getting errors…please help me on this
Here the steps i’m following
1. Creating the parameters for source instance name, source db name, destination db name/server name
2. Creating dynamic xml file using the same format generated by Commvault
3. Using the Commvault command line restore script and in the arguments…i’m passing the parameters mentioned in step 1

I got the following error when i execute this…Please let me know if i’m confusing you

Error :

powershell.exe : C:\program files\Commvault\simpana\Base\ps_restore.ps1 : A parameter cannot be
At line:1 char:11
+ powershell <<<< .\ps_restore.ps1 -clientName sqldbatbal532 -instanceName sqldbatbal532 -restoreSource testCDC -database RestoreTest -t
oTimeValue 2013-04-20 12:24:00 -destClient/clientName sqldbatbal533 -destinationInstance/clientName sqldbatbal533 -destinationInstance/in
stanceName sqldbatbal533
+ CategoryInfo : NotSpecified: (C:\program file…eter cannot be :String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError


Script :

Param (
[string]$ClientName,
[string]$InstanceNames,
[string]$Restoresource,
[string]$Database,
[datetime]$RestTime,
[string]$destClient,
[string]$destinationInstance,
[string]$InstanceName
)

$template_xml = “<TMMsg_CreateTaskReq>
<taskInfo>
<task>
<taskFlags>
<disabled>false</disabled>
</taskFlags>
<policyType>DATA_PROTECTION</policyType>
<taskType>IMMEDIATE</taskType>
<initiatedFrom>COMMANDLINE</initiatedFrom>
<alert>
<alertName></alertName>
</alert>
</task>
<associations>
<backupsetName>defaultBackupSet</backupsetName>
<subclientName></subclientName>
<clientName></clientName>
<appName>SQL Server</appName>
<instanceName></instanceName>
</associations>
<subTasks>
<subTask>
<subTaskType>RESTORE</subTaskType>
<operationType>RESTORE</operationType>
</subTask>
<options>
<restoreOptions>
<browseOption>
<commCellId>2</commCellId>
<backupset>
<clientName></clientName>
<backupsetName>defaultBackupSet</backupsetName>
</backupset>
<timeRange/>
<noImage>true</noImage>
<useExactIndex>false</useExactIndex>
<mediaOption>
<library/>
<mediaAgent/>
<drivePool/>
<drive/>
<copyPrecedence>
<copyPrecedenceApplicable>false</copyPrecedenceApplicable>
<synchronousCopyPrecedence>0</synchronousCopyPrecedence>
<copyPrecedence>0</copyPrecedence>
</copyPrecedence>
<proxyForSnapClients>
<clientName></clientName>
</proxyForSnapClients>
</mediaOption>
<timeZone>
<TimeZoneName>(GMT-05:00) Eastern Time (US &amp; Canada)</TimeZoneName>
</timeZone>
<listMedia>false</listMedia>
</browseOption>
<destination>
<destClient>
<clientName></clientName>
</destClient>
<destinationInstance>
<clientName></clientName>
<appName>SQL Server</appName>
<instanceName></instanceName>
</destinationInstance>
</destination>
<sqlServerRstOption>
<dbOnly>false</dbOnly>
<overWrite>true</overWrite>
<pointOfTimeRst>false</pointOfTimeRst>
<sqlRestoreType>DATABASE_RESTORE</sqlRestoreType>
<sqlRecoverType>STATE_RECOVER</sqlRecoverType>
<stopStartSSA>false</stopStartSSA>
<preserveReplicationSettings>false</preserveReplicationSettings>
<stopMarkRestore>false</stopMarkRestore>
<stopBeforeMarkRestore>false</stopBeforeMarkRestore>
<partialRestore>false</partialRestore>
<logShippingOnly>false</logShippingOnly>
<ffgRestore>false</ffgRestore>
<ignoreFullBackup>false</ignoreFullBackup>
<vSSBackup>false</vSSBackup>
<device>|testCDC|#12!RestoreTest|#12!TEST_REPLICATION|#12!D:\MSSQL\DATA\RestoreTest.mdf|#12!D:\MSSQL\DATA\test_replication.mdf</device>
<device>|testCDC|#12!RestoreTest|#12!TEST_REPLICATION_log|#12!L:\MSSQL\LOG\RestoreTest_log.ldf|#12!L:\MSSQL\LOG\test_replication_log.ldf</device>
<restoreSource></restoreSource>
<database></database>
<timeZone>
<TimeZoneName>(GMT-05:00) Eastern Time (US &amp; Canada)</TimeZoneName>
</timeZone>
<keepDataCapture>false</keepDataCapture>
</sqlServerRstOption>
<fileOption>
<mapFiles/>
</fileOption>
<commonOptions>
<detectRegularExpression>true</detectRegularExpression>
<restoreDeviceFilesAsRegularFiles>false</restoreDeviceFilesAsRegularFiles>
<restoreSpaceRestrictions>false</restoreSpaceRestrictions>
<ignoreNamespaceRequirements>false</ignoreNamespaceRequirements>
<skipErrorsAndContinue>false</skipErrorsAndContinue>
<onePassRestore>false</onePassRestore>
<revert>false</revert>
<recoverAllProtectedMails>false</recoverAllProtectedMails>
<isFromBrowseBackup>false</isFromBrowseBackup>
<clusterDBBackedup>false</clusterDBBackedup>
</commonOptions>
</restoreOptions>
<adminOpts>
<updateOption/>
</adminOpts>
<commonOpts>
<startUpOpts>
<startInSuspendedState>false</startInSuspendedState>
<priority>66</priority>
<useDefaultPriority>true</useDefaultPriority>
</startUpOpts>
<prePostOpts>
<preRecoveryCommand></preRecoveryCommand>
<postRecoveryCommand></postRecoveryCommand>
<impersonation>
<!–PrePost Command Impersonation Level–>
<level>NO_SELECTION</level>
<user>
<userName></userName>
</user>
</impersonation>
<runPostWhenFail>false</runPostWhenFail>
</prePostOpts>
<!–User Description for the job–>
<jobDescription></jobDescription>
</commonOpts>
</options>
</subTasks>
</taskInfo>

</TMMsg_CreateTaskReq>”

$template_xml | Out-File “C:\Program Files\CommVault\Simpana\Base\template_restore.xml” -Encoding ascii


.\qoperation execute -af template_restore.xml -clientName $ClientName -instanceName $InstanceName -restoreSource $Restoresource -database $Database -toTimeValue $RestTime -destClient/clientName $destClient -destinationInstance/clientName $destinationInstance -destinationInstance/instanceName $InstanceName
by mjolinor at 2013-04-22 16:18:19
In the scenario you’re describing, I’d expect to see an XLM template in an expandable here-string, with the parameter variables embedded for expansion, and then invocation of an executable that reads in the xml file.

What you have there is vaguely like that, but not nearly enough for me to be sure that’s what you’re trying to do.
by vmusunoor at 2013-04-23 12:04:12
Thanks for the reply

i believe here’s the issue…from destclient…i guess powershell doesnt support back slash ( /)…any suggestion how to replace it ?

.\qoperation execute -af template_restore.xml -clientName $ClientName -instanceName $InstanceName -restoreSource $Restoresource -database $Database -toTimeValue $RestTime -destClient/clientName $destClient -destinationInstance/clientName $destinationInstance -destinationInstance/instanceName $InstanceName
by mjolinor at 2013-04-23 13:22:39
The way you have this written is very confusing. Is -destClient/clientName a parameter of the qoperation executable?
by eisenbergz at 2013-04-25 00:20:21
Just a few ideas:
Try putting a backtick ( ` - above the tab key, next to the ‘1’ key on a US keyboard) before the slash to escape any normal/reserved meaning of the character.

I would also recommend using the echoargs.exe program to see if the parameters you are passing to the external program are being passed the way you think they are.

Lastly, you might want to try putting an & , ‘cmd /c’ , or use Invoke-Expression at the beginning of the line that calls an external program. That may help ensure that all of those parameters get passed to it properly.
by vmusunoor at 2013-04-25 09:39:43
Hi mjolinor,

Thanks for the response…
yes after .\qoperation execute statement…all were parameters
it worked now…i removed back slash (/) and using the statement like this…somehow it worked…qoperation is accepting them and for date and time of restore we need to put like this "2013-04-25 2:35:00 "

.\qoperation execute -af template_restore.xml -clientName $ClientName -instanceName $InstanceName -restoreSource $Restoresource -database $Database -toTimeValue $RestTime -destClient/clientName $destClient -destinationInstanceclient $destinationInstance -destinationInstanceinstance $InstanceName


but the biggest issue is at the device part…if you look at the xml file…you can see the device which has data and log file paths for source and destination databases…

<device>|testCDC|#12!RestoreTest|#12!TEST_REPLICATION|#12!D:\MSSQL\DATA\RestoreTest.mdf|#12!D:\MSSQL\DATA\test_replication.mdf</device>
<device>|testCDC|#12!RestoreTest|#12!TEST_REPLICATION_log|#12!L:\MSSQL\LOG\RestoreTest_log.ldf|#12!L:\MSSQL\LOG\test_replication_log.ldf </device>

i need to create a parameter for this as well and in parameter i need to give paths for destination data and log file paths…

Please help me on this…

Thanks in advance