CODEWITHSUNDEEP
sqlsql-serverpowershell
user3770612
user3770612

Reputation: 1769

How to specify backup directory for SQL Server backup using powershell

I am trying to create a backup using the below script but this script creates the backup into default backup folder, how to specify a user defined folder e.g c:\backup

$dbToBackup = "inventory"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") |   
Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")   
| Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-  
Null

#create a new server object
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDirectory = $server.Settings.BackupDirectory

#display default backup directory
"Default Backup Directory: " + $backupDirectory
read-host "stop"
#i put stop here to see the default directory that is C:\Program Files\Microsoft SQL    
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup 
$db = $server.Databases[$dbToBackup]
$dbName = $db.Name

I have modified the script but still not getting the new location appeard in screen

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()

$backupDirectory = $server.Settings.BackupDirectory

#display default backup directory
"Default Backup Directory: " + $backupDirectory
read-host "Stop"

Upvotes: 1

Views: 3232

Answers (2)

nabrond
nabrond

Reputation: 1388

The problem with the suggestion by Rafal is that it will modify the default path for ALL backups of the instance. This is not a safe approach to redirect a single backup. The script below shows how you can use the Backup object to create a database backup while specifying a destination outside of the server's default path.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$dbToBackup = "SomeDatabase"
$dbBackupFile = "Drive:\Path\to\database\backup\file.bak"

# no server name will connect to the default instance on the local computer
$server = New-Object Microsoft.SqlServer.Management.Smo.Server

# this object performs the database backup
$backup = New-Object Microsoft.SqlServer.Management.Smo.Backup

# set properties
$backup.Action = "Database"
$backup.BackupSetName = "$dbToBackup Backup"
$backup.BackupSetDescription = "Full backup of $dbToBackup"
$backup.Database = $dbToBackup
$backup.Incremental = $false

# define the file to use for writing backups
$backupFile = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $dbBackupFile, "Disk"

# add it to the backup job
$backup.Devices.Add($backupFile) | Out-Null

# execute the backup
$backup.SqlBackup($server)

Upvotes: 1

Rafał Czabaj
Rafał Czabaj

Reputation: 740

I think i have found a solution for your problem.

Please add this code to your script, if you want to override the backup directory:

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.

Code sample and text was taken from site: http://www.powershellmagazine.com/2013/03/14/pstip-change-sql-server-default-backup-folder-location/

Upvotes: 0

Related Questions