Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Retrieve Default SQL Server Backup Folder using PowerShell

By:   |   Read Comments (1)   |   Related Tips: More > PowerShell

Identify and Eliminate SQL Server Performance Monitoring Issues - Free Webcast


In a previous tip on creating a Function to Return Default SQL Server Backup Folder , you've seen how you can create a T-SQL function to query the registry and retrieve the default SQL Server Backup folder. Is there an easier way to do it in Windows PowerShell?


Leveraging on what we already know from the tip on Using PowerShell with SQL Server Management Objects (SMO), we can access the different members of the Server object by running the Get-Member cmdlet

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST" 
$s | Get-Member -MemberType Property 

Get-Member PowerShell Cmdlet

One particular property to note is the Settings property which we will use to retrieve modifiable settings of our SQL Server instance. We can then retrieve the different Settings property to list the default SQL Server Backup folder

$s.Settings | Get-Member -MemberType Property

$s.Settings | Get-Member -MemberType Property

The BackupDirectory property gives us the default SQL Server Backup directory value. This property can be modified as well but for our purpose, retrieving it would be enough.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST" 

Find the SQL Server backup directory path in PowerShell

Notice how we have highlighted the power and ease of use of Windows PowerShell with SMO where, in three lines of code, we managed to retrieve the default SQL Server Backup folder. Applying what we have learned from previous Windows PowerShell tips with SMO, we can simply pass a list of SQL Server instances from any data source - text file, Excel spreadsheet, etc. - to retrieve this property across the enterprise.

Next Steps
  • Download and install Windows PowerShell
  • Read more on the SMO Class Library to translate SQL Server objects that we are already familiar with to SMO objects
  • Check out the other Windows PowerShell tips

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Send me SQL tips:


Saturday, March 07, 2015 - 10:01:21 PM - Amol Lembhe Back To Top

Thank you very much for this post, it has helped me a lot.

Learn more about SQL Server tools