How write a powershell script to automate data sync from physical server to VPS server

connection to remote server

#server IP: 176.57.189.102
#server: VMI690251

$remote_SQLServer = "41.211.48.78\edcu-lab" 
$local_SQLServer = "176.57.189.102\VMI690251" 

$SQLDBName = "Sentinel Surveillance"
$SQLUser = "sentinel"
$SQLPassword = "P@ssw0rd"

# fetch data from remote server

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $remote_SQLServer; Database = $SQLDBName; User ID= $SQLUser; Password= $SQLPassword" 
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = ” SELECT * FROM [COVID_Screning]”
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

# Assign the Sql Command object Sql Adapter object
$SqlAdapter.SelectCommand = $SqlCmd

# Declare DataSet object
$DataSet = New-Object System.Data.DataSet

# Execute Sql Adapter object to fill the DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0]
$SqlConnection.Close()

#----------------------------------------------------------------
#updating the local server with fetched data

#$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
#$SqlConnection.ConnectionString = "Server = $local_SQLServer; Database = $SQLDBName; User ID= $SQLUser; Password= $SQLPassword" 
#$SqlConnection.Open()
#$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
#$sqlQuery = "BULK INSERT [Sentinel Surveillance].dbo.[COVID_Screening] FROM $DataSet.Tables[0]"
#$SqlCmd.CommandText = $sqlQuery
#$SqlCmd.Connection = $SqlConnection 
#$sqlCmd.EndExecuteNonQuery()
#$Connection.Close()

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.