Script designed to back up sql database cannot open database [<database>] requested by the login <ComputerName\account>

Question

When I run BACKUP DATABASE landofbeds TO DISK = 'c:\temp\backups\lob_backup.bak' in SQL Server Management Studio it creates a back up and works fine.

I want to create a powershell script that does this so I've written:

$Server = 'localhost'
$Database = '<DbName>'
$Filepath = 'c:\temp\backups\lob_backup.bak'
$Query = "BACKUP DATABASE '$Database' TO DISK = '$Filepath'"

Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $Query

Anything in angular brackets (<>) is a placeholder for the actual data.

This returns an error of:

Invoke-Sqlcmd : Cannot open database "<DbName>" requested by the login. The login
failed. Login failed for user '<ComputerName\Account>'.
At line:6 char:1
+ Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $Que ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Have you checked out at Sql Server's Server Log yet? There ought to be respective error with more details. — vonPryz
The error is self explanatory. Check your login details and see if you are manually able to login from ssms with those details? — Ranadip Dutta
emphasis on those details (i.e. the login specified in the error message). — gvee

RedGrin Grumble RedGrin Grumble · Accepted Answer · 2019-08-29T22:14:14

I am having the same issue, with the same error. I know the Login Works, I had to login to SSMS with the correct creds myself.


$thisSrvr = hostname
$cred_SQL = get-credential -userName "username" -message "Creds to Run the SQL Backup"
$SqlServer = (Get-SqlDatabase -Credential $cred_SQL -ServerInstance $thisSrvr).name
$date     = get-date -UFormat "%Y%m%d"
$backups  = "D:\"

Foreach ($db in $SQLServer){
  $pw       = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($cred_SQL.Password)) #converts SecureString PW to Plain Txt.
  $ASver    = (Invoke-SQLCmd -Query "getting app version info" -ServerInstance $thisSrvr -UserName $cred_SQL.UserName -Password $pw -Database $DB).version -replace "\.", "-"
  $BAKFile  = $db + "_ROLE_" + $ASVer + "_" + $date + ".bak"
  $BAKStr   = $backups + $BAKFile
  Backup-SqlDatabase -Credential $cred_SQL -ServerInstance $thisSrvr -Database $db -BackupAction "Database" -BackupFile $BAKStr -CompressionOption "On" -Initialize | out-null
}
Clear-Variable pw -Scope Global

I am not sure where to go to look at log info in SSMS, but I can search that myself. My only thought about why this might be happening is that perhaps the login doesn't have permissions to this DB? But I'd find that weird since this login is the one that created the DB.

Script designed to back up sql database cannot open database [<database>] requested by the login <ComputerName\account>

1 Answers