Restore database query works with SSMS but not Invoke-Sqlcmd

PowerShell Help
1

The following query executes successfully in the SSMS console but not from PowerShell, so its seems not to be an SQL syntax issue. The SQL database is being copied & restored on a different SQL instance, hence the two MOVE statements.

$queryString = @"
USE master
RESTORE DATABASE [Activity] FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL12.TEST\MSSQL\Backup\Activity.bak’ WITH REPLACE,
MOVE ‘Activity’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.TEST\MSSQL\Data\Activity.mdf’,
MOVE ‘Activity_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.TEST\MSSQL\Data\Activity.ldf’;
"@

$resultDataSet = Invoke-Sqlcmd -Query $queryString -ServerInstance ‘localhost\TEST’ -Database ‘Activity’

Error message:

ERROR: Invoke-Sqlcmd : Cannot open database “Activity” requested by the login. The login failed.
ERROR: Login failed for user ‘MyDomain\Administrator’.

Thus the database was not restored.

Troubleshooting steps:

The SSMS console is logged in with the same Windows Authentication account ‘MyDomain\Administrator’ which has the SQL Sysadmin role and its SQL Login has the Master database as its default database.

I dropped the SQL database successfully (to eliminate overwrite issues) but still encountered the Login error that prevents restoring the database. It did prove that Invoke-Sqlcmd with the ‘MyDomain\Administrator’ credentials logged in successfully from PowerShell.

$queryString = @"
USE [master]
ALTER DATABASE Activity SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE Activity;
"@

$resultDataSet = Invoke-Sqlcmd -Query $queryString -ServerInstance ‘localhost\TEST’ -Database ‘Activity’

2

Not sure but you care try following steps:

  1. try replacing -Database ‘Activity’ with -Database ‘Master’
  2. open the powershell in admin mode and then run the script

Can you successfully run simple Select statement using invoke-sqlcmd keeping all other things same?

3

Thank you for your prompt offer of help, Pramod Singla!

  1. The query attempts to overwrite the System Database “Master” with the backup files from the Activity database.
  2. I am running the PowerShell session using “Run As Administrator”.

From that same PowerShell session, I can successfully execute Invoke-Sqlcmd queries that contain SELECT, CREATE DATABASE, or DROP DATABASE statements, using that same ‘MyDomain\Administrator’ user account that has the Sysadmin SQL role in the SQL instance that hosts the Activity database. It is only the RESTORE DATABASE statement query that is failing, though cutting & pasting that query string into the SSMS console executes successfully using the same ‘MyDomain\Administrator’ credentials, so the SQL syntax is correct, just the Invoke-Sqlcmd execution of that syntax that is failing.

For others that encounter this issue, as a workaround I offer the following lines of code that can restore the database from that same PowerShell session using SMO objects. I just wish to alert the PowerShell authorities that the Invoke-Sqlcmd cmdlet does not seem to execute the RESTORE-DATABASE statement.

Load the SMO Assemblies that contain the SMO Object Classes.

[void] [Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

Specify the destination path for the MDF and LDF files for the Activity database in the destination SQL instance.

$activityData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“Activity”, $mdfPath)
$activityLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“Activity_log”, $ldfPath)

$relocateArray = @($activityData, $activityLog)

Restore-SqlDatabase -ServerInstance ‘localhost\TEST’ -Database ‘Activity’ -BackupFile $backupPath -RestoreAction Database -ReplaceDatabase -RelocateFile $relocateArray