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
– vonPryz, Commented Jan 8, 2018 at 12:06
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
– Ranadip Dutta, Commented Jan 8, 2018 at 13:22
emphasis on those details (i.e. the login specified in the error message). — gvee
– gvee, Commented Jan 8, 2018 at 13:41

aRustyDev · Accepted Answer · 2019-08-29 22:14:14Z

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.

Rinkashikika · Accepted Answer · 2023-03-31 08:27:12Z

0

Check if login has exec permissions

answered Mar 31, 2023 at 8:27

Rinkashikika

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related