Copy Code code as follows:
--full BackUp
--use Master
CREATE proc [dbo]. [Bakup_database]
As
Declare @strPsw varchar (50)
Declare @strUsr varchar (50)
Declare @strCmdShell varchar (300)
Declare @strDataBaseName varchar (20)
Declare @FullFileName Varchar (200)
Declare @FileFlag varchar (50)
Declare @ToFileName varchar (200)
Declare @SQLStr varchar (500)
Declare @SQLStr2 varchar (500)
Declare @FlagDel varchar (20)
Set
@FileFlag =replace (CONVERT (char (), GETDATE (), 20), ': ', ', ', '-')--backup file naming rule: date-time. bak
Set @strUsr = ' sofmti_td\administrator '-need to fill in Domain name \ username (Windows login name of target machine)
Set @strPsw = ' sofmit '-need to fill in the Windows login password (such as: Soondy)
Set @strCmdShell = ' net use \\192.168.0.22\c$ ' + @strPsw + '/user: ' + @strUsr--Need to complete IP (IP address of target machine, such as: 192.168.2.178)
Set @strDataBaseName = ' liliandb '--fill in the database name (for example: Soondy)
Set @FullFileName = ' E:\SqlServer automatic backup file \ ' + ' liliandb_backup_ ' + @FileFlag + '. BAK '
--Fill in the directory of local backup temporary files, because you need to back up to the local and copy to the target machine (such as: Save directory for E:\SoondyTest\)
Set @ToFileName = ' \\192.168.0.22\D$\OneCardBak\ '-need to fill in the IP (IP address of the target machine) and the saved directory (such as: \\192.168.2.178\c$\Test\)
Set @FlagDel = ' False '--fill in true to delete local backup temporary files, fill False or other characters to indicate retention of the file
Set @SQLStr = ' Copy ' + @FullFileName + ' + @ToFileName
Set @SQLStr2 = ' del ' + @FullFileName
BackUp DataBase @strDataBaseName to disk= @FullFileName with Init
EXEC master.. xp_cmdshell @strCmdShell--trying to connect to the target machine
EXEC Master.. xp_cmdshell @SQLStr--Copy to the target machine
if (@FlagDel = ' True ') exec master. xp_cmdshell @SQLStr2--delete local backup temp files
