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

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: