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 d.name dbname,f.name logicalfilename,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 "
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$dbs = $DataSet.Tables
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
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...