Executing stored procedure in power shell script

Question

Hi,

I have a stored procedure "sp_TestBackupDatabase" and executing in powershell like below:

$query = "exec sp_TestBackupDatabase @dataBaseName=" + $dataBase + ", @backupType=" + $backupType + ", @backupFolder='" + $backupFolder + "'"
$emailContent += sqlcmd.exe -S serverame\SQLEXPRESS2014 -E -V 11 -d master -Q $query
$emailContent += "nLASTEXITCODE: $LASTEXITCODEn`n"

When I directly execute the stored procedure, its working fine. But in powershell, I am getting exit code as 16 and error message says stored procedure "sp_TestBackupDatabase" could not be found.

Can anyone let me know what is wrong with command "sqlcmd.exe -S serverame\SQLEXPRESS2014 -E -V 11 -d master -Q $query" ?
I have recently reinstalled newer version of SQL server management studio and changed the path of power shell script.
Before these changes, this script was working fine.

I basically want to take back up of database and delete back ups older than two days. So any point of time I should have only two back ups.
This I want it to automate through task or any mechanism. If you have any better solution please let me know.

Kindly waiting for your response.

Thanks,
Santosh

Answer 1

Thanks OlafHelper-2800. I was connecting to wrong DB instance. It worked after changing.

However, I want to copy to folder "E:\DBBackup". I am passing the value to @backupFolder as "E:\DBBackup". But the back up is getting copied to "E:\" drive only.
I am using the stored procedure mentioned in the Microsoft site: https://raw.githubusercontent.com/microsoft/mssql-support/master/sample-scripts/backup_restore/SQL_Express_Backups.sql

Can suggest what could be the issue ?
Kindly waiting for your response.

Answer 2

Hi @Santosh Umarani ,

-S connect to the database engine by specifying the instance name, protocol, IP address, TCP/IP port number and so one, perhaps you are connected to the wrong object?
https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

Answer 3

sqlcmd.exe -S serverame\SQLEXPRESS2014 -E -V 11 -d master -Q $query

With the parameter "-d master" you connect to the system database "master"; is the stored procedure in there? By the error message I guess not.
If not, then use the database name where you created the stored procedure.

P.S.: Using SQL Server PowerShell is easier to handle.