Pass parameter into XML file using Powershell

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  April 16, 2013 at 1:36 pm

  #273608

  Hi,

  I have a project to automate SQL server restore.........use restore db's using third party tool called Simpana, Commvault.....we would like to automate the restore using powershell....i can restore the database using commnd prompt and powershell but to automate its pretty complicated.

  We usually do restore in Simpana using its GUI...and Simpana has some commands for restore...and when using GUI we have an option to save the restore process as script...it saves as .xml file and .dat file....

  To restore the database using powershell we need to use that xml file or we can create a dynamic xml file using that format..i was able to do that....

  here is the dynamic xml file that i'm generating...i need to pass parameters in to this xml file e.g., db name, destination db name ,source server,dest server

  Please let me know if anyone have any questions or need more information....

  $template_Power = "<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 & 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>|DBA_Rep2|#12!DBA_Rep2_Test|#12!DBA_Rep|#12!D:\MSSQL\DATA\DBA_Rep2_Test.mdf|#12!D:\MSSQL\DATA\DBA_Rep2.mdf</device>

  <device>|DBA_Rep2|#12!DBA_Rep2_Test|#12!DBA_Rep_log|#12!L:\MSSQL\LOG\DBA_Rep2_Test_log.ldf|#12!L:\MSSQL\LOG\DBA_Rep2_1.ldf</device>

  <restoreSource>DBATest</restoreSource>

  <database>DBATest</database>

  <timeZone>

  <TimeZoneName>(GMT-05:00) Eastern Time (US & 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 > C:\Program Files\Commvault\simpana\Base\template_restXML.xml

• Nick_UK

  SSCommitted

  Points: 1909

  More actions

  April 17, 2013 at 9:57 am

  #1607312

  can you do a string replace on the areas you need to substitue in your parameters e.g

  $Instance = "SQLInstance"

  $template_Power = $template_Power.Replace("<instanceName></instanceName>","<instanceName>$Instance</instanceName>")

  $template_Power > C:\Program Files\Commvault\simpana\Base\template_restXML.xml

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  April 17, 2013 at 12:54 pm

  #1607402

  Hey thanks for the response...i tried that its isnt working....can you please be more specific....