Posted by: Colin Smith
Add new tag, DBA, Powershell, Scripting
I recently had a request to go out and find the logical name and the physical name of mdf and ldf files in sql server. I thought this might be a good place to use powershell and the invoke-sqlcmd cmd-let that is with sqlserver2008 snapin.
Here is an example of what I did to find the files for one specific database:
$files = Invoke-Sqlcmd -ServerInstance “sqlserver” -Database “master” -Query “select name, physical_name from sys.master_files where name like ‘%dbname%’”
$primary = $files.name
$primary_physical = $files.physical_name
$log = $files.name
$log_physical = $files.physical_name
Pretty simple but this will give you the name and physical location of the files that you need. Hope that will help. I actually plan on using this is my Monitoring scripts.