retrieve data and log file name from SQL database

by vmusunoor at 2013-05-01 12:56:43


I’m trying to pull the logical and physical file names from sql server database using powershell and add those values in exisiting xml file…
This whole process is for database restore…we don’t do direct sql db restore…we use third party tool called Commvault…that is the reason for restore automation…Here’s my code…its just a part of it…




$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

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

$SqlCmd.CommandText = “select dbname, logicalfilename,f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and = ‘$Sourcedatabase’ and f.type = 0 "

$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$dbs = $DataSet.Tables[0]

In the above sql server i’m just trying to pull data file with type = 0…its working fine…if i put type =1 i can get log file info…please let me know how to get data and log file information, such that i can path those information into xml file …like below

<device>|SourceDB|#12!DestinationDB|#12!SourceLogicallogfilename|#12!Destinationlogfilepath|#12!Sourcelogfilepath.ld </device>

all fields needs to filled accordingly…if we have more than one data file…then it hsould populate n number of device tags…

Please help me on this…thanks…let me know if you have any questions…
by vmusunoor at 2013-05-01 12:58:12
forgot to mention…we need to get source and destination database names, source logical data and log file names , Source and destination data and log file names ( physical )…
by poshoholic at 2013-05-02 05:52:31
To get both data files and log files in one query, you can should be able to replace this:
f.type = 0
with this:
(f.type = 0 or f.type = 1)
in your where clause.

You will probably want to get the type as part of your query so that you can identify the type once you have the results.

Putting the string together to push into an xml document is easy once you have all of the data fields you need. I’d start there, make sure you have all of the data and that you can access the specific fields you want using PowerShell first, then reply back with that information and someone here can show you how to put the string together.
by vmusunoor at 2013-05-02 07:03:43
Thanks poshoholic…
we can get both information without f.type in where clause…but i’m looking for separate outputs for data and log files…
like this… if we can get this data separately…we can try to input this into xml file with the same format above…with this i think i’m getting all the required data…
coming to source and destination database…we will provide them as parameters…we are only and mainly looking for logical and physical data and log file paths for source and destination databases…now that we got the data…please let me know how to input into xml file …
Many thanks

name physical_name
test_repp D:\MSSQL\DATA\test_repp.mdf
name physical_name
test_repp_log L:\MSSQL\LOG\test_repp_log.ldf
by poshoholic at 2013-05-02 12:54:57
I’m not sure which part you need help with. Do you need help building the string that will be written into the XML document, or do you need help adding values to an existing XML document? It would be easier for me if you shared what you have, ideally a simplified version of it, showing the values you want inserted into the xml document as well as the position in the xml document where it needs to be inserted (again, a short, simplified version) and then I should be able to help you out more.
by vmusunoor at 2013-05-02 13:33:23
sorry i wasn’t clear with my script…
i need help in building the string that will be written to xml file and adding that string to existing xml file.
Below is the xml position where i want to insert…since xml file is big…i’m not posting here…


i’m looking for the string like this…which we need to build a string and add into existing xml file

<device>|SourceDB|#12!DestinationDB|#12!SourceLogicaldatafilename|#12!Destinationdatafilepath|#12!Sourcedatafilepath</device> ( Data File )
<device>|SourceDB|#12!DestinationDB|#12!SourceLogicallogfilename|#12!Destinationlogfilepath|#12!Sourcelogfilepath.ld </device> ( Log File )

First we will try creating the above similar string…final step is to add this string into xml file
I got the source database information but unable to find the destination database info…please take a look at this and let me know




$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

$SqlConnection.ConnectionString = “Server = $ClientName; Database = $Sourcedatabase; Integrated Security = True”

$SqlCmd.CommandText = “select, ,f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and = ‘$Sourcedatabase’”

$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
#$dbs = $DataSet.Tables[0].Rows[0][1]
#$dbs1 = $DataSet.Tables[0].Rows[0][2]
#$dbs2 = $DataSet.Tables[0].Rows[1][1]
#$dbs3 = $DataSet.Tables[0].Rows[1][2]
# <device>|testCDC|#12!RestoreTest|#12!TEST_REPLICATION|#12!D:\MSSQL\DATA\RestoreTest.mdf|#12!D:\MSSQL\DATA\test_replication.mdf</device>

foreach ($Row in $Dataset.Tables[0].Rows)
$dev = “<device>|”
$dev = $dev + $($Row[0]) +”|#12!"
$dev = $dev + $($Row[1]) +"|#12!"
$dev = $dev + $($Row[2]) +"|#12!"
$dev = $dev + “</device>”


with the above code i’m able to get the source db info and similar string like this


…but its missing destination db info…not sure how to get and destination db info and put that in string ?
is there any way that we can put the destination instance and db info…in sql query ?
coming to Destination server and database…i’m looking for default data and log file path info…

Please help me on this and let me know if i confuse you
by DonJ at 2013-05-02 14:21:54
So, you’re kinda confusing a few of us who’ve looked at this :). Please remember that we don’t really understand what you’re trying to do or what your environment looks like.

We understand “I need to construct an XML string and insert it into an XML file.” Got it. What we’re not understanding is where “source DB” and “destination DB” enter into this. Are we querying something from a SQL Server that’s going into an XML string? I understand you’re trying to get physical and logical name information…

So, a couple of things. Typically, you don’t build an XML file by building literal strings with tag names in them. The [xml] type in PowerShell understands the XML format, and you use methods and an object model to add new nodes, values, and attributes to the XML. You can then emit the modified XML to a file.

Can you narrow this down to one simple question? “How do I ____?" I realize that may not be your only question, but let’s start with just one to try and minimize the confusion.

If your question is, “I know how to query information from the first server, now I need to know how to query it from the second server,” that’s a straightforward answer. There is no way to query two servers in a single SQL connection. You have to make a brand-new connection, command, query, and everything for the second server. If you know how to retrieve the information from the first server, then it’s the exact same set of steps for the second server - just use the second server’s server name.
by vmusunoor at 2013-05-02 17:49:12
Thanks Don for the response…
i’m really sorry to confuse you guys…this is something…i need to explain in detail in order to make you understand…

Details of my environment :

1. We use third party tool Commvault Simpana for SQL Server backups and restore ( I have already mentioned in my first post) , so to restore a database we need to use Commvault, its mandatory…
2. we use Commvault GUI to restore the databases, we have an option to save the restore process as script…when we save it…it stores in the form of XML file ( this is where XML file comes into picture )
3. I’m using that XML file as a template for automation

I have few questions…
1. since its a database restore it should have parameters like sourceserver,sourcedatabase,destinationserver,destinationdatabase…so i’m trying to retrieve the information as per the below node from source server…for source server we got the required output…but for destination…we still need destination data and log file path as per the below node …as you said…i will try creating the whole process for the destination as well

<device>|SourceDB|#12!DestinationDB|#12!SourceLogicaldatafilename|#12!Destinationdatafilepath|#12!Sourcedatafilepath</device> ( Data File )
<device>|SourceDB|#12!DestinationDB|#12!SourceLogicallogfilename|#12!Destinationlogfilepath|#12!Sourcelogfilepath </device> ( Log File )

2. I already have an XMl file which i’m using as a template for whole restore process…, i just need to add nodes into it ( the node that i’m trying to add serves as database source and destination information) …

Please let me know if i still confuse you guys…

Many thanks

Heres my xml file…

[code2=xml]<xml version=‘1.0’>
<appName>SQL Server</appName>
<appName>SQL Server</appName>
<TimeZoneName>(GMT-05:00) Eastern Time (US & Canada)</TimeZoneName>
<!–PrePost Command Impersonation Level–>
<!–User Description for the job–>

by vmusunoor at 2013-05-03 06:26:13
If i still confuse you guys…as you said just a single question…forget about adding node to existing xml file( final step )…right now i’m just looking to pull information from source and destination databases…since we got source output…looking for destination db server info…

Here i’m trying to get default data and log file information for destination sql server instance and put both source and destination database info in the below node and same format…i’m looking for a output like this…


first line is data file information and second line should have log file information…
#12! - separates the values in the command

for better understanding please see here and please let me know if you need any information…thanks

by kittH at 2013-05-03 07:59:55
Took a look, at first I was unable to cast that file as XML in powershell. $XML = [XML](Get-Content C]
Looks like they didn’t escape an ampersand on line 87. Once corrected it should look like:
<TimeZoneName>(GMT-05:00) Eastern Time (US &amp; Canada)</TimeZoneName>
Once I had the file loaded in as $xml, I was able to access the destination device keys at:
I’m still not exactly sure I understand what you want to do with them, but hopefully this can get you started.
by vmusunoor at 2013-05-03 08:47:10
Thanks KittH…you are right…i’m using that xml file as template…and load it into powershell…if you access this path from powershell…


you will get an output like this


As i explained earlier…if you take a look at the above output …it has database information…i’m trying to pull information from database and create a node like above and add this node to
the xml file…

So , how do i get the destination sql server instance default data and log file paths ?
by ArtB0514 at 2013-05-03 09:40:16
Up until now, I couldn’t really figure out what you were trying to do, but I think I’ve gotten an AH-HAH moment that will help you. Try this:
$db = $XML.xml.TMMsg_CreateTaskReq.taskInfo.subTasks.options.restoreOptions.sqlServerRstOption.device
$Data = $db[0].Split(’|’)
$Log = $db[1].Split(’|’)

Having done this, you can then see where the different elements go:
0…($Data.Count-1) | foreach {"$
:: $($Data[$_])”}
0 ::
1 :: sourcedatabase
2 :: #12!destinationdatabase
3 :: #12!config
4 :: #12!D:\MSSQL\DATA\config_Venkat.mdf
5 :: #12!D:\MSSQL\DATA\config.mdf

Now you can do $Data[1] = $NewSourceDatabase
$Data[2] = ‘#12!’ + $NewDestinationDatabase

Perform the same process for the Log device, then join these all back together and reset the device item:
$Device = @()
$Device += $Data -join ‘|’
$Device += $Log -join ‘|’
$XML.xml.TMMsg_CreateTaskReq.taskInfo.subTasks.options.restoreOptions.sqlServerRstOption.device = $Device
by poshoholic at 2013-05-03 10:14:41
I’m just curious, have you approached the folks at CommVault with this problem? It seems to me that approaching the vendor would be a good idea, they might be able to provide you better help on their forums than what you can get from the PowerShell community. They probably would like to know someone is trying to work this out too (it might even make for a good blog post for them to do). Just a thought.
by vmusunoor at 2013-05-03 11:14:05
thanks Kirk, but I already contacted them and posted about this idea in forums…but no response…
I think some folks here got me…i mean what i’m trying to do here…

Please post the step that you dont understand here…so that we will be on same page…