I'm attempting to create a SQL Server Job that shuts off high availability, changes a database to the simple recovery model, run a backup to clear the transaction log, do a database shrink, set a database to full recovery model, run a full backup, then turn high availability back on. I already have all of this scripted out, however I don't have a way to run the needed scripts on the secondary server through the job.
To further clarify if that's not enough: Primary Server has a Database. Secondary Server needs the Database from the Primary Server backed up on it for High Availability. This requires me to run scripts on both the Primary Server and the Secondary Server. In order to do this, I need to be able to connect to the secondary server and run a script in a SQL Server Agent Job on the primary server.
How would I go about doing this? What do I need to write in order for SQL Server's T-SQL (Or any other SQL Server Job-step Type).
I've tried :Connect but that won't work unless I load it into sqlcmd, and it always gets stuck when I try to run sqlcmd through a SQL Agent Job. I've also tried making it connect to the server through sqlcmd (sqlcmd -S (server_name)) and running a script on the server locally, but that hasn't worked either.
I created a linked server then ran the following:
EXEC server2.master.sys.sp_executesql N'RESTORE DATABASE [Database1] FROM DISK = N''\\fileshare\folder\Database1.bak'' WITH NORECOVERY, NOUNLOAD, STATS = 5'
This is the syntax I am using for all of the steps.