Retrieve data from sql server and update/add existing xml file

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  May 2, 2013 at 7:53 am

  #99843

  Hi guys,

  i have to retrieve data and log file paths from sql server database and add that output to xml file....

  here are my questions

  1. how to select xml file from drive or disk ?

  2. how to update the sql query output to xml file...

  here's my sample xml file...if you look at the device tag which s highlighted....that we need to update in the same format...

  here's the sql code..which i'm running from powershelll...

  please help me on this...many thanks

  <xml version='1.0'>

  <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></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>|sourcedatabase|#12!destinationdatabase|#12!config|#12!D:\MSSQL\DATA\config_Venkat.mdf|#12!D:\MSSQL\DATA\config.mdf</device>

  <device>|sourcedatabase|#12!destinationdatabase|#12!config_log|#12!L:\MSSQL\LOG\config_Venkat_log.ldf|#12!L:\MSSQL\LOG\config_log.ldf</device>

  <restoreSource>config</restoreSource>

  <database>config</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>

  </xml>

  powershell script :

  param

  (

  [Parameter(Mandatory="True")][String]$ClientName,

  [Parameter(Mandatory="True")][String]$Sourcedatabase,

  [Parameter(Mandatory="True")][String]$DestServerName,

  [Parameter(Mandatory="True")][String]$Destdatabase

  )

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $DataSet = New-Object System.Data.DataSet

  #$devicedata = " | + $Sourcedatabase + '#12!$destdatabase' + | + '#12!$Sourcelogicaldatafilename' + | + '#12!$Destdatafilepath' + | + '#12!$Sourcedatafilepath'" | out-file "C:\Brain\Restore\restore_template__test.xml"

  #$devicelog = " | + $Sourcedatabase + '#12!$destdatabase' + | + '#12!$Sourcelogicallogfilename' + | + '#12!$Destlogfilepath' + | + '#12!$Sourcelogfilepath'" | out-file "C:\Brain\Restore\restore_template__test.xml"

  $SqlConnection.ConnectionString = "Server = $ClientName; Database = $RestoreSource; Integrated Security = True"

  $sqlquery1 = "select f.name,f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and

  d.name = '$Sourcedatabase' and f.type = 0 "

  $sqlquery2 = "select f.name,f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and

  d.name = '$Sourcedatabase' and f.type = 1"

  $sqlQueries = @($SqlQuery1, $SqlQuery2)

  #$Results = @()

  #Foreach ($Query in $sqlQueries)

  $SqlCmd.CommandText = $sqlqueries

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter.SelectCommand = $SqlCmd

  $SqlAdapter.Fill($DataSet)|out-null

  $dbs =$DataSet.Tables[0]

  $dbs