#PSTip Change SQL Server default backup folder location

Note: This tip requires PowerShell 2.0 or above.

If you have ever backed up a SQL Server database, you will know that the default backup location is set to the Program Files folder where SQL is installed. For example, on a system with SQL Server 2008 R2, it is set to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup. This may not always be an ideal location to store database backups.

So, how do we change this using PowerShell? We can either use Windows Registry to change this path or SQL Management Objects (SMO) to do this. We shall see how we can use SMO in this tip.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($env:ComputerName)
$server.Properties["BackupDirectory"].Value = "K:\Backup"
$server.Alter()

Make a note that the assembly version specified in Add-Type command above is SQL Server 2008 R2 version. If you want to use this code snippet for SQL Server 2012, you’d have to find the right SMO version.

About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts

%d bloggers like this: