SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A small facet of information


I’ve blogged about facets before, but I just thought that I’d mention them again, as they came in quite handy today.

I was looking to change the default backup directory of my SQL instance.

Usually, I’d just change the value by editing the registry, either manually or with a script. But as I had a bit of time I thought I’d check to see if there was a way of doing this through a UI tool – and there is. It’s right in the facets in SSMS.

Right click on the name of your SQL instance in SSMS, and then from the context menu choose “Facets”

From the “View Facets” dialog, select “Server Settings” as the facet.

From there, you’ll see “BackupDirectory”  - as shown below.

But, a word of caution.

You’ll have to manually enter a path, there’s no ability to pick a path. There’s also no validation that the path is correct.

Just as an extra piece of information, here’s a PowerShell script to get the backup directory.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null;
$Instance = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) “.”;
$bckupDir = $Instance.Settings.BackupDirectory;
#display default backup directory;
“Default Backup Directory location: ” + $bckupDir;

have a nice day,




Posted by leksshmanan on 5 July 2011

Hi Martin,

It would be great if you can add a note that it only works from SQl 2008 and above , for SQL 2005 we are still having to edit the reg key

Leave a Comment

Please register or log in to leave a comment.