This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](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)
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
Programming language used to interact with SQL Server databases
Answer recommended by moderator
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
To dynamically set the destination server name in your sqlcmd script, you can use the SERVERPROPERTY function 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 on SERVERPROPERTY('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;" -b
In 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.