Executing SQL script from remote computer from command prompt

kraghum 21 Reputation points
2021-07-17T12:11:16.71+00:00

We have Two Servers

Server 1--> Our APplication servers
Server 2--> MSSQL DB server

this is a Training env setup so we restore it every week, There is a PowerShell script that does this restoration, The script stops/start services on "Server" then there is step for Drop and restoring DB which we are doing manually from MSSQL studio

# copy golden volumes to current volume location

ROBOCOPY PARAMETERS

/mir - Mirrors a directory tree

robocopy $VolumeSource $VolumeDestination /mir /v /s /b
robocopy $Volume1Source $Volume1Destination /mir /v /s /b

Pop up box notifying user that the database needs to be restored.

Script can continue when user selects OK

$Shell = New-Object -ComObject "WScript.Shell"
$Button = $Shell.Popup("Continue only after the database has been restored", 0, "Restore Database", 0)

start only DB service to import the golden database

Start-Service $DBServices

Restore the database using the SQL file

Invoke-Sqlcmd -InputFile "C:\Users\serviceamefusn\Desktop\ServerRefresh\DBBak.sql" | Out-File -filePath "C:\Temp\RestoreLogSqlCmd.rpt" -verbose

Invoke-Sqlcmd -InputFile "C:\Users\serviceamefusn\Desktop\ServerRefresh\ClusterRestore.sql" | Out-File -filePath "C:\Temp\RestoreClusterLogSqlCmd.rpt" -verbose

loop through each service, if its stopped, start it

foreach($ServiceName in $Services)
{
$arrService = Get-Service -Name $ServiceName
#write-host $ServiceName
while ($arrService.Status -ne 'Running')
{
Start-Service $ServiceName
write-host $ServiceName 'is currently' $arrService.status
write-host $ServiceName' is Starting'
Start-Sleep -seconds 20
$arrService.Refresh() Blockquote

So the SQL scripts C:\Users\serviceamefusn\Desktop\ServerRefresh\DBBak.sql" and ClusterRestore.sql, currently exeuting as mannual method from MS-SQL mgmt studio

Is there way we can do it directly from PowerShell script? so that One click will complete all required steps

Thanks in Advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,959 questions
{count} votes

Accepted answer
  1. Dan Guzman 9,236 Reputation points
    2021-07-19T09:37:14.747+00:00

    So the SQL scripts C:\Users\serviceamefusn\Desktop\ServerRefresh\DBBak.sql" and ClusterRestore.sql, currently exeuting as mannual method from MS-SQL mgmt studio

    Is there way we can do it directly from PowerShell script? so that One click will complete all required steps

    If I understand correctly, you are executing the PowerShell Invoke-Sqlcmd commands from an interactive SSMS PowerShell session on Server1 in the context of the Server2 target database server. Those same commands can be executed directly from any PowerShell session on Server1 but you need to specify the -Server argument in order to run the SQL scripts against the remote Server2 instance. You should be able to include these in your existing PowerShell script file:.

    Invoke-Sqlcmd -InputFile "C:\Users\serviceamefusn\Desktop\ServerRefresh\DBBak.sql" -Server "Server2" | Out-File -filePath "C:\Temp\RestoreLogSqlCmd.rpt" -verbose 
    Invoke-Sqlcmd -InputFile "C:\Users\serviceamefusn\Desktop\ServerRefresh\ClusterRestore.sql" -Server "Server2" | Out-File -filePath "C:\Temp\RestoreClusterLogSqlCmd.rpt" -verbose
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    2021-07-19T08:55:31.527+00:00

    Come on, Cris! Surely, we should be able to help people to run SQL Powershell under this tag? Invoke-SqlCmd is certainly something that ships with some component of SQL Server.

    I did not want to stick my head in to this thread, because I don't use Powershell much myself, but I know that much that it is perfectly possible to do what Kraghum is asking for. What I don't know for sure is that I don't know exactly what to install. I have an idea that downloading and installing SSMS should be enough, but I am not sure.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.