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,



Tags: ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.