Option 1: Use the GUI

Run Ssms.exe > login as sa > Right-click SQL Server instance > Properties > Database Settings > modify the field next to ‘Backup’ > OK > restart SQLSERVER when it’s safe to do so

Option 2: PowerShell
?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# Localhost version
$newBackupDirectory='Z:\Backup'
 
function changeSqlBackupDirectory($newBackupDirectory){
    $ErrorActionPreference='stop'
    function confirmation($content,$testValue="I confirm",$maxAttempts=3){
        $confirmed=$false;
        $attempts=0;       
        $content|write-host
        write-host "Please review this content for accuracy.`r`n"
        while ($attempts -le $maxAttempts){
            if($attempts++ -ge $maxAttempts){
                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                break;
                }
            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
            if ($userInput.ToLower() -eq $testValue.ToLower()){
                $confirmed=$true;
                write-host "Confirmed!`r`n";
                break;               
            }elseif($userInput -like 'cancel'){
                write-host 'Cancel command received.'
                $confirmed=$false
                break
            }else{
                Clear-Host;
                write-host $($content|out-string).Trim()
                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                }
            }
        return $confirmed;
    }
 
    try{
        $sqlFolder=(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').MSSQLSERVER
        $registrySqlDefaults="REGISTRY::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\$sqlFolder\MSSQLServer"
        $sqlDefaults=get-itemproperty $registrySqlDefaults
        $backupDirectory=$sqlDefaults.BackupDirectory
        write-host "Default backup directory prior to changes: '$backupDirectory'"
         
        $confirmed=confirmation "Change default backup from $backupDirectory to $newBackupDirectory"
        if($confirmed){
            Set-Itemproperty -path $registrySqlDefaults -Name 'BackupDirectory' -value $newBackupDirectory
            $newSqlDefaults=get-itemproperty $registrySqlDefaults
            $changedBackupDirectory=$newSqlDefaults.BackupDirectory
            write-host "Default backup directory has been changed to: '$changedBackupDirectory'"
 
            write-warning 'Refreshing the SQLSERVER service for changes to become effective.'
            import-module sqlps
            $sqlServer=(get-item "SQLSERVER:\SQL\$env:computername").ManagedComputer
            $defaultInstance=$sqlServer.Services['MSSQLSERVER']
            #$defaultInstance.stop()
            $defaultInstance.refresh() # Refresh seems to be sufficient in lieu of a restart
            #$defaultInstance.start()
            return $true
        }else{
            write-host "No changes have been made as process has been cancelled by $env:username"
            return $false
        }
    }catch{
        write-warning $_
        return $false
    }
}
 
changeSqlBackupDirectory $newBackupDirectory
?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
$setBackupDirectory='Z:\Backup'
$sqlServers='sql01','sql02','sql03'
$adminCredential=get-credential
 
function setSqlBackupDirectory($sqlServers,$setBackupDirectory,$adminCredential){
    function changeSqlBackupDirectory($newBackupDirectory){
        $ErrorActionPreference='stop'
        function confirmation($content,$testValue="I confirm",$maxAttempts=3){
            $confirmed=$false;
            $attempts=0;       
            $content|write-host
            write-host "Please review this content for accuracy.`r`n"
            while ($attempts -le $maxAttempts){
                if($attempts++ -ge $maxAttempts){
                    write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                    break;
                    }
                $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                if ($userInput.ToLower() -eq $testValue.ToLower()){
                    $confirmed=$true;
                    write-host "Confirmed!`r`n";
                    break;               
                }elseif($userInput -like 'cancel'){
                    write-host 'Cancel command received.'
                    $confirmed=$false
                    break
                }else{
                    Clear-Host;
                    write-host $($content|out-string).Trim()
                    write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                    }
                }
            return $confirmed;
        }
 
        try{
            $sqlFolder=(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').MSSQLSERVER
            $registrySqlDefaults="REGISTRY::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\$sqlFolder\MSSQLServer"
            $sqlDefaults=get-itemproperty $registrySqlDefaults
            $backupDirectory=$sqlDefaults.BackupDirectory
            write-host "Default backup directory prior to changes: '$backupDirectory'"
             
            $confirmed=confirmation "Change default backup from $backupDirectory to $newBackupDirectory"
            if($confirmed){
                Set-Itemproperty -path $registrySqlDefaults -Name 'BackupDirectory' -value $newBackupDirectory
                $newSqlDefaults=get-itemproperty $registrySqlDefaults
                $changedBackupDirectory=$newSqlDefaults.BackupDirectory
                write-host "Default backup directory has been changed to: '$changedBackupDirectory'"
 
                write-warning 'Refreshing the SQLSERVER service for changes to become effective.'
                import-module sqlps
                $sqlServer=(get-item "SQLSERVER:\SQL\$env:computername").ManagedComputer
                $defaultInstance=$sqlServer.Services['MSSQLSERVER']
                #$defaultInstance.stop()
                $defaultInstance.refresh() # Refresh seems to be sufficient in lieu of a restart
                #$defaultInstance.start()
                return $true
            }else{
                write-host "No changes have been made as process has been cancelled by $env:username"
                return $false
            }
        }catch{
            write-warning $_
            return $false
        }
    }
 
    $results=@{}
    foreach ($sqlServer in $sqlServers){
        $result=invoke-command -computername $sqlServer -Credential $adminCredential -scriptblock{
                param($changeSqlBackupDirectory,$newBackupDirectory)
                [scriptblock]::create($changeSqlBackupDirectory).invoke($newBackupDirectory)
            } -Args ${function:changeSqlBackupDirectory},$setBackupDirectory       
        write-host "$sqlServer`: $result"
        $results+=@{$sqlServer=$result}
    }
    return $results
}
 
setSqlBackupDirectory $sqlServers $setBackupDirectory $adminCredential
Categories: