1

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"
Improve this question
5
  • I have just changed the default database data folder in sql server management studio but still i am not able to see the new folder Commented Aug 13, 2014 at 8:49
  • $server.Settings.BackupDirectory="c:\yourfolder" ?? Commented Aug 13, 2014 at 8:53
  • its giving me error message, i have fix this problem by changing the registry setting for database backup, i specified new backup path in registry settings by going to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer Commented Aug 13, 2014 at 8:59
  • but now its creating all the backups of all databases in one folder, e.g testDb, Invenrory DB, i am not sure how can i specify in the above script to create testDb in Test folder and InventoryDB in Inventory folder Commented Aug 13, 2014 at 9:00
  • I don't see where you're actually creating the backup. Either way, you're using dynamite where a match would do. Specifically, changing the default backup directory for the entire server just to perform one backup is not generally considered a good idea. You can specify a location for each backup you take if the server's default isn't good enough. TL;DR: don't change the server default on each backup. Commented Aug 13, 2014 at 15:13

2 Answers 2

Reset to default
1

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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/

Improve this answer

1 Comment

i have edit my question, still not getting the new backup location using above script, do i need to mention computer name and ["Backupdirectory"]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.