Back up the database of the SQL Server Remote Server to the target machine)

Before use, you must activate a writable shared folder on the target machine.
The stored procedure is as follows:
Create procedure sp_backup_to_remote
(
@ Database varchar (128), -- Name of the database to be backed up
@ Remoteip varchar (15) -- back up the IP address of the target machine
@ Sharename varchar (128), -- share name of the target machine
@ Sharepath varchar (128), -- path in the shared directory of the target machine, which is backed up here
@ Username varchar (20), -- connect to the user shared by the target machine. This user must have the write permission to this shared user.
@ Password varchar (20), -- connect to the password shared by the target machine
@ Backupfile varchar (128) -- name of the file backed up to the target machine
)
As
Declare @ result int
Declare @ errormessage varchar (512)

/** Add the used backup device */
-- Backup device name
Declare @ devicename varchar (128)
Set @ devicename = @ username + '@' + @ remoteip + '\' + @ sharename + '\' + @ sharepath + '\'
-- Backup device path
Declare @ devicepath varchar (512)
Set @ devicepath = '\' + @ remoteip + '\' + @ sharename + '\' + @ sharepath + '\' + @ backupfile
-- Add a backup device
Exec @ result = sp_addumpdevice 'disk', @ devicename, @ devicepath
If @ result = 1
Begin
Set @ errormessage = 'failed to add the backup device. The physical device named '+ ''' + @ devicepath + ''' +' already exists. To ensure successful backup, manually delete the master. DBO. in the sysdevices table, the phyname value is '+ ''' + @ devicepath + ''' +. '
Raiserror (@ errormessage, 16, 1)
Return 0
End

/** Add a shared connection */
-- Add the shared connection command
Declare @ addshare varchar (512)
Set @ addshare = 'net use \ '+ @ remoteip +' \ '+ @ sharename + ''+ @ password +'/User: '+ @ username +' @ '+ @ remoteip
-- Add a shared connection
Exec @ result = xp_mongoshell @ addshare
If @ result = 1
Begin
Set @ errormessage = 'failed to add shared connection and backup failed. '
Raiserror (@ errormessage, 16, 1)
Return 0
End

/** Back up the database */
Backup Database @ database to @ devicename

/** Delete a shared connection */
-- Delete the shared connection command
Declare @ deleteshare varchar (512)
Set @ deleteshare = 'net use \ '+ @ remoteip +' \ '+ @ sharename +'/delete'
-- Delete the shared connection
Exec @ result = xp_cmdshell @ deleteshare
If @ result = 1
Begin
Set @ errormessage = 'failed to delete the shared connection. '
Raiserror (@ errormessage, 16, 1)
Return 0
End

/** Delete the used backup device */
exec @ result = sp_dropdevice @ devicename
If @ result = 1
begin
set @ errormessage = 'failed to delete the backup device. Manually delete the device whose phyname value is '+ ''' + @ devicepath + ''' +' in the master. DBO. sysdevices table. The '
raiserror (@ errormessage, 16, 1)
return 0
end
Print' command is complete. '
return 1
go

It is very convenient to call this stored procedure. You can call this stored procedure on the target machine (local), remote server, or another machine, when calling this stored procedure on the target machine or local machine or another machine, you must add an SQL Server registration for the remote server and then use . master. DBO. sp_backup_to_remote.