The Multifunctioning DBA

Nov 24 2010   6:52PM GMT

Find Database Files with Powershell

Colin Smith Colin Smith Profile: Colin Smith

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[0].name
$primary_physical = $files[0].physical_name
$log = $files[1].name
$log_physical = $files[1].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.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: