Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieve data from sql server and update/add existing xml file Expand / Collapse
Author
Message
Posted Thursday, May 02, 2013 7:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:18 AM
Points: 78, Visits: 658
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
Post #1448821
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse