CREATE PROCEDURE [dbo]. [sp_dbbackup_everynight_local] @cycle int,---save period @islocal int,---is local 0 means 1 means no @savepath NVARCHAR,---file save path \ \ 192.168.19.102\databaseback@dbprefix NVARCHAR (,---) generates a file prefix @server VARCHAR (,---server IP 192.168.19.102@username varchar,---Login Server user name Administrator@password VARCHAR---login server password soft_2016as BEGIN DECLARE @backpath2 V Archar (+) DECLARE @command VARCHAR (200)--Creating a temporary table create table #Filetabel ( FILEPATH VARCHAR (+) NULL)--Call the DOS command to log on to the server IF @IsLocal = 1 Beginset @command = ' net use ' + @SavePath + ' + @PassWord + '/user: ' + @Server + ' \ ' + @UserName EXEC master. xp_cmdshell @command END-Adds all the file names of the shared directory to the temporary table SET @command = ' dir/b ' + @SavePath INSERT into # Filetabel EXEC Master. xp_cmdshell @command DELETE from #Filetabel WHERE FILEPATH are NULL while E Xists (SELECT * from #Filetabel) BEGIN DECLARE @fileName VARCHAR SET @fileName = (SELECT TOP 1 * FROM #Filetabel ORDER by FILE PATH)--Define variable get file time DECLARE @fileTime VARCHAR DECLARE @fDa Tetime DATETIME IF @fileName is not NULL BEGIN SET @fileTime = SU Bstring (@fileName, 8) SET @fDateTime = CONVERT (DATETIME, @fileTime) DECL is @lastTime DATETIME SET @lastTime = DATEADD (day, [email protected], CONVERT (VARCHAR), GETDATE (), 111)) IF @fDateTime <= @lastTime BEGIN-delete backup DECLARE @filePath VARCHAR SET @filePath = ' del ' + @SavePath + ' \ ' + @fileName EXEC Master. xp_cmdshell @filePath END--delete this piece of data DELETE from #Filetabel WHERE FILEPATH = @fileName End end-Delete temporary table drop table #Filetabel --Back up the database to the server SET @backpath2 = @SavePath + ' \ ' + @DBPrefix + ' _ ' + RE Place (CONVERT (VARCHAR), GETDATE (), +), '-', ') + ' _back.bak ' BACKUP DATABASE [LGS] to [email protected] with INIT, FORMAT END GO
Executes exec [dbo]. [Sp_dbbackup_everynight_local] 5,0, ' F:\LGSCMS_20141117_Back ', ' LGS ', ', ', '
SQL Server Scheduled backup database