Figured it out! Made it harder than it needed to be.
sqlcmd -E -S Source-d master -Q "DECLARE @sql nvarchar(4000); SET @sql = 'BACKUP DATABASE DB TO DISK = ''\' + host_name() + '\BackupFolder\DB_FULL.bak'' WITH INIT, COPY_ONLY;'; EXEC (@sql);" -b
sqlcmd to backup on one server and write to another
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 95%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EB%3C/text%3E%3C/svg%3E)
bukkybu
386
Reputation points
I have a sql agent job that runs a CmdExec step on server 'Destination'. It executes a full backup on server 'Source' and needs to write the backup to server 'Destination'. This works perfectly when the server names are hard coded. However, both the 'Source' and 'Destination' servers participate in separate AGs. I have the following script that calls the 'Source' listener. That part works. I need it to write the backup to whichever 'Destination' AG replica is running the sql agent job. How do I set the 'Destination' machine name in the script -Q? Here's what I have so far, but it obviously is getting the machine name of the 'Source' server that the script is running on rather than the 'Destination' server, the server the sql agent job is running on. Any assistance is greatly appreciated.
\Destination\BackupFolder\DB_FULL.bak
sqlcmd -E -S Source_AG_listener -d master -Q "DECLARE @DestinationServer nvarchar(100) = convert(nvarchar(100), SERVERPROPERTY('MachineName')); DECLARE @BackupPath NVARCHAR(1000) = '\' + @DestinationServer + '\BackupFolder\DB_FULL.bak'; BACKUP DATABASE DB TO DISK= @BackupPath with init, copy_only;" -b
SQL Server | SQL Server Transact-SQL
Answer recommended by moderator
1 additional answer
Sort by: Most helpful
-
Q&A Assist2026-02-04T23:00:50.94+00:00 AI generated content. This question contains an answer created with AI from Q&A Assist. Learn moreTo dynamically set the destination server name in your
sqlcmdscript, you can use theSERVERPROPERTYfunction to retrieve the machine name of the server where the SQL Agent job is running. However, since you want to ensure that the backup is written to the correct AG replica on the destination server, you will need to modify your script to refer to the destination server directly rather than relying onSERVERPROPERTY('MachineName')which retrieves the machine name of the source server.Instead, you can pass the destination server name as a parameter to your script. Here’s how you can adjust your command:
sqlcmd -E -S Source_AG_listener -d master -Q "DECLARE @DestinationServer nvarchar(100) = 'Destination_Server_Name'; DECLARE @BackupPath NVARCHAR(1000) = '\\' + @DestinationServer + '\BackupFolder\DB_FULL.bak'; BACKUP DATABASE DB TO DISK= @BackupPath WITH INIT, COPY_ONLY;" -bIn this script, replace
'Destination_Server_Name'with the actual name of the destination server or use a variable that holds the name of the destination server where the job is running. This way, the backup will be correctly directed to the specified destination server's backup folder.Make sure that the SQL Server service account has the necessary permissions to write to the specified backup folder on the destination server.