英文:
How do I make a SQL Server Job connect to another server to run a Query?
问题
我正在尝试创建一个SQL Server作业，该作业关闭高可用性，将数据库更改为简单恢复模型，运行备份以清除事务日志，执行数据库收缩操作，将数据库设置为完整恢复模型，运行完全备份，然后重新启用高可用性。我已经编写了所有这些脚本，但我没有办法通过作业在次要服务器上运行所需的脚本。
进一步澄清，如果这还不足够：
主服务器上有一个数据库。
次要服务器需要从主服务器上备份该数据库以实现高可用性。
这需要我在主服务器和次要服务器上运行脚本。
为了实现这一点，我需要能够连接到次要服务器并在主服务器的SQL Server代理作业中运行脚本。
我应该如何操作？我需要编写什么内容以便在SQL Server的T-SQL（或任何其他SQL Server作业步骤类型）中实现此目标。
我尝试过使用:Connect，但除非我将其加载到sqlcmd中，否则它不起作用，而且每当我尝试通过SQL Agent作业运行sqlcmd时，它都会卡住。我还尝试过通过sqlcmd（sqlcmd -S（服务器名称））连接到服务器并在服务器上本地运行脚本，但这也没有奏效。
英文:
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.
答案1
得分: 0
我创建了一个链接服务器，然后运行了以下命令：
EXEC server2.master.sys.sp_executesql N'RESTORE DATABASE [Database1] FROM DISK = N'\fileshare\folder\Database1.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5'
这是我用于所有步骤的语法。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论