The simple remote machine backup database function, through this stored procedure, tells the remote other machine database backup to the local. The main principles are:
1. Using xp_cmdshell to execute Windows commands, map the local shared directory to the network drive of the remote machine.
2. Back up the database to a network drive via SQL script.
3. Delete the mapped network drive through the xp_cmdshell execution command.
When using, be careful to turn on the xp_cmdshell configuration (see the Notes section of the stored procedure).
/******************************************************************************************************** function: remote machine BACKUP Database * * danny,li** Date: 2013-09-04**----------------------------------------------------------------------------------- ----------------* * eg:exec [proc_remotebackupdb] ' DataBaseName ', ' \\DannyPc\ShareFolder ', ' domain\danny ', ' 123456 ' * * *******************************************************************************************************/CREATE PROCEDURE [dbo].[proc_remotebackupdb] @databaseName varchar( -),--the name of the database that needs to be backed up @shareFolderPath varchar( -),--shared directory path (for example: \\DannyPc\ShareFolder) @shareFolderAccount varchar( -),--shared directory read-write permission account name (for example: Domain\danny) @shareFolderPassword varchar( -)--shared directory read-write rights account password asBEGIN --Enable xp_cmdshell configuration --sp_configure ' SHOW advanced OPTIONS ', 1 --RECONFIGURE --GO --sp_configure ' xp_cmdshell ', 1 --RECONFIGURE --GO DECLARE @sqlStr VARCHAR( +); --Map Network Drives SET @sqlStr = 'EXEC MASTER. xp_cmdshell"'NET use L:'+ @shareFolderPath +' "'+ @shareFolderPassword +'"/user:'+ @shareFolderAccount +" ";'; --backing up the database DECLARE @BackupFile VARCHAR( $); SET @BackupFile = @databaseName + CONVERT(VARCHAR( -),GETDATE(), A)+ '. BAK'; SET @sqlStr = @sqlStr + 'BACKUP DATABASE'+ @databaseName +'to DISK ="'l:\'+ @BackupFile +" "With INIT;'; --Remove Network Drive mappings SET @sqlStr = @sqlStr + 'EXEC MASTER. xp_cmdshell"'NET use L:/delete"';'; --Execute SQL statement PRINT(@sqlStr); EXEC(@sqlStr);END
SQL Server database backup (different machine)