Remote execute question

• as_1234

  SSCrazy

  Points: 2842

  More actions

  July 13, 2018 at 8:53 am

  #366129

  All,

  I'm working on a script to copy a backup to a remote sql server and restore it. The main purpose is to test the backup. The restore part of the script is as follows:

  EXEC sp_addlinkedserver @server=N'SqlBackupRestore', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'Server2' ,@catalog=N'CSStatus';

  exec sp_serveroption 'SqlBackupRestore','RPC OUT','true'

  exec ('RESTORE DATABASE CSRestore FROM DISK=''d:\Restorefolder\restore.bak''

  with move ''CSSystem_Data'' to ''D:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\DATA\CSRestore.mdf'',

  move ''CSSystem_Data2'' to ''D:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\DATA\CSRestore2.mdf'',

  move ''CSSystem_Log'' to ''D:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\DATA\CSRestore2.ldf''

  ') at SQLBACKUPRESTORE

  exec sp_dropserver SqlBackupRestore

  I would appreciate any help with the following questions:

  Is it possible to capture the message output from the exec command? Eventually the script would run from a SQL agent task and, I think, with the above command the restore could fail and the task would still show as successful?
  Is there a better of achieving the remote restore? The reason I'm running it from the server that took the backup is so that it runs when the backup is complete but, again, there might be a better way?
  The reason for the 'with move' is because both servers are involved in an availability group so the database name exists on the destination server.

  Thanks

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  July 13, 2018 at 10:53 am

  #1997504

  Instead of trying to execute the RESTORE remotely like this, could a SQL Agent job be setup on the remote server that does this RESTORE?  If so then all you need to do is call MSDB.dbo.sp_start_job across the link.  This way the history and any error would be in the SQL Agent logs

• as_1234

  SSCrazy

  Points: 2842

  More actions

  July 13, 2018 at 11:01 am

  #1997508

  Hello,

  Thanks for your help. I should be able to do as you have suggested.

  Thanks