SQL Error Msg 3201, Level 16, State 2

Sample Error Message:

Msg 3201, Level 16, State 2, Line 2
Cannot open backup device '\\1.1.1.1\d$\backup\TEST_MSCRM.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Resolution:

  1. Get the SQL Service run-as account
    $sqlServiceRunas=(Get-WMIObject win32_service |?{$_ -like "*MSSQLSERVER*"}).StartName
  2. Grant SQL Service account Full access to parent folder of backup directory
    $backupFile='\\1.1.1.1\d$\backup\TEST_MSCRM.bak'
    $parentDirectory=split-path $backupFile -parent
    $acl = Get-ACL $parentDirectory
    $allowFullAccesss=New-Object System.Security.AccessControl.FileSystemAccessRule($sqlServiceRunas,"Full","Allow")
    $acl.AddAccessRule($allowFullAccesss)
    Set-Acl $parentDirectory $acl
  3. Since the referenced path is an ‘Administrative Share’, the SQL account must be added to the local ‘Administrators’ group of the File Server
    $backupFile='\\1.1.1.1\d$\Backup\TEST_MSCRM.bak'
    [regex]$regexFileServer='^\\\\([\d\w\.\-]+)\\'
    $fileServer=.{$address=$regexFileServer.Match($backupFile).Groups[1].Value
    [System.Net.Dns]::GetHostByAddress($address).HostName
    }
    $session=new-pssession $fileServer
    if($session){
    invoke-command -session $session -scriptblock{
    param($principleName,$groupName)
    write-host "Adding $principleName into $groupName";
    Add-LocalGroupMember -Group $groupName -Member $principleName;
    write-host "$env:computername group $groupName now has these members:`r`n";
    get-localgroupmember $groupName|ft -autosize
    } -args $sqlServiceRunas,'Administrators'
    remove-pssession $session
    }else{
    write-warning "Unable to connect to $fileServer"
    }

Leave a Reply

Your email address will not be published. Required fields are marked *