跨服务器 backup

左手的ㄟ右手 2022-08-20 03:26 204阅读 0赞

USE MASTER
    GO
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE WITH OVERRIDE;
    GO
    EXEC MASTER ..xp_cmdshell 'net use \\firstshare.cn\Share\FaciShareDataBackup\SQLBackupData01 "123456789" /user:FirstShare\FSSvc9999'
    GO
    
    
    -- =============================================
    -- Author:		ZHAOWENZHONG
    -- Create date: 2015-03-31
    -- Description:	Database Backup
    -- =============================================
    CREATE PROCEDURE [dbo].[PRO_BACKUP_DB]
    (
    @Instance nvarchar(128)=NULL,
    @DB_NAME SYSNAME,
    @TYPE  VARCHAR(12), --FULL DIFF LOG
    @BACK_PATH VARCHAR(512)
    )
    WITH ENCRYPTION
    AS
    --version1.1
    BEGIN
    	SET NOCOUNT ON
    
    	DECLARE @BACKUP_FULL_SQL NVARCHAR(1280)
    	DECLARE @BACKUP_DIFF_SQL NVARCHAR(1280)
    	DECLARE @BACKUP_LOG_SQL NVARCHAR(1280)
    	DECLARE @DATE_SERIAL_NUMBER NVARCHAR(16)
    
    	IF @Instance IS NULL
    	BEGIN
    		SET @Instance=@@SERVICENAME
    	END
    	SET @DATE_SERIAL_NUMBER='_'+''+CONVERT(varchar(8), GETDATE(), 112)+ replace(convert(char(5), getdate(), 108), ':', '')+'' 
    	IF @DB_NAME IS NULL
    	BEGIN
    		PRINT 'Database Does not Exist'
    		RETURN	1;
    	END
    	IF NOT EXISTS(SELECT NAME FROM MASTER.SYS.DATABASES WHERE NAME=@DB_NAME)
    	BEGIN
    		PRINT '[' + @DB_NAME + '] is not a valid database name!'
    		RETURN 1;
    	END
    
    	IF @TYPE NOT IN ('FULL','DIFF','LOG')
    	BEGIN
    		PRINT 'Database backup type must be [full], [log] or [diff]!'
    		RETURN 1;
    	END
    
    	DECLARE @EXISTS_INSTANCE_SQL NVARCHAR(512)
    	DECLARE @EXISTS_INSTANCE_FOLDER TABLE(COL NVARCHAR(512))
    	DECLARE @EXISTS_FOLDER TABLE(COL NVARCHAR(512))
    	DECLARE @EXISTS_SQL NVARCHAR(512)
    	DECLARE @EXISTS_SHAREFLODER_SQL NVARCHAR(512)
    	DECLARE @MKDIR_SQL NVARCHAR(512)
    	DECLARE @MKDIR_INSTANCE_SQL NVARCHAR(512)
    
    	SET @EXISTS_SHAREFLODER_SQL=N'EXEC MASTER..XP_CMDSHELL ''DIR'+CHAR(9)+''+@BACK_PATH+''''
    	SET @EXISTS_INSTANCE_SQL=N'EXEC MASTER..XP_CMDSHELL ''DIR'+CHAR(9)+''+@BACK_PATH+'\'+@Instance+''''
    	SET @EXISTS_SQL=N'EXEC MASTER..XP_CMDSHELL ''DIR'+CHAR(9)+''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+''''
    	SET @MKDIR_INSTANCE_SQL=N'EXEC MASTER..XP_CMDSHELL ''MKDIR'+CHAR(9)+''+@BACK_PATH+'\'+@Instance+''', NO_OUTPUT'
    	SET @MKDIR_SQL=N'EXEC MASTER..XP_CMDSHELL ''MKDIR'+CHAR(9)+''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+''', NO_OUTPUT'
    
    	INSERT INTO @EXISTS_FOLDER EXEC SP_EXECUTESQL @EXISTS_SHAREFLODER_SQL,N'@BACK_PATH NVARCHAR(128)',@BACK_PATH
    	IF EXISTS (SELECT 1 FROM @EXISTS_FOLDER WHERE COL LIKE '%File Not Found%')
    	BEGIN
    		PRINT '['+@BACK_PATH+'] is not a valid !'
    		RETURN 2;
    	END
    	INSERT INTO @EXISTS_INSTANCE_FOLDER EXEC SP_EXECUTESQL @EXISTS_INSTANCE_SQL,N'@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128)',@BACK_PATH,@Instance
    	IF EXISTS (SELECT 1 FROM @EXISTS_INSTANCE_FOLDER WHERE COL LIKE '%File Not Found%')
    	BEGIN
    		EXEC SP_EXECUTESQL @MKDIR_INSTANCE_SQL,N'@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128)',@BACK_PATH,@Instance
    	END
    	INSERT INTO @EXISTS_FOLDER EXEC SP_EXECUTESQL @EXISTS_SQL,N'@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DB_NAME NVARCHAR(128)',@BACK_PATH,@Instance,@DB_NAME
    	IF EXISTS(SELECT 1 FROM @EXISTS_FOLDER WHERE COL LIKE '%File Not Found%')
    	BEGIN
    		EXEC SP_EXECUTESQL @MKDIR_SQL,N'@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DB_NAME NVARCHAR(128)',@BACK_PATH,@Instance,@DB_NAME
    	END
    	IF RIGHT(@BACK_PATH,1)='\'
    	BEGIN
    		SET @BACK_PATH=LEFT(@BACK_PATH,LEN(@BACK_PATH)-1)
    	END
    
    
    	DECLARE @ERROR INT
    	DECLARE @BACKUP_FILENAME VARCHAR(512)
    	DECLARE @BACKUP_BEGIN DATETIME
    		IF @TYPE='FULL'
    		BEGIN
    			SET @BACKUP_FULL_SQL=N'BACKUP DATABASE'+CHAR(9)+'['+@DB_NAME+']'+CHAR(9)
    								+'TO DISK= '''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+'\'+@DB_NAME+@DATE_SERIAL_NUMBER+'.BAK'''+CHAR(9)+'WITH NOFORMAT, NOINIT,'
    								+'NAME=N'''+@DB_NAME+'-FULL Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
    			SET @BACKUP_FILENAME=@DB_NAME+@DATE_SERIAL_NUMBER+'.BAK'
    			--EXEC (@BACKUP_FULL_SQL)
    			SET @BACKUP_BEGIN=GETDATE()
    			EXEC SP_EXECUTESQL @BACKUP_FULL_SQL,N'@DB_NAME NVARCHAR(128),@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DATE_SERIAL_NUMBER NVARCHAR(16)',@DB_NAME,@BACK_PATH,@Instance,@DATE_SERIAL_NUMBER
    			SET @ERROR=@@ERROR
    		END
    		IF @TYPE='DIFF'
    		BEGIN
    			SET @BACKUP_DIFF_SQL=N'BACKUP DATABASE'+CHAR(9)+'['+@DB_NAME+']'+CHAR(9)
    								+'TO DISK= '''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+'\'+@DB_NAME+@DATE_SERIAL_NUMBER+'.DIFF'''+CHAR(9)+'WITH  DIFFERENTIAL , NOFORMAT, NOINIT,'
    								+'NAME=N'''+@DB_NAME+'-Differential Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
    			SET @BACKUP_FILENAME=@DB_NAME+@DATE_SERIAL_NUMBER+'.DIFF'
    			--EXEC (@BACKUP_DIFF_SQL)
    			SET @BACKUP_BEGIN=GETDATE()
    			EXEC SP_EXECUTESQL @BACKUP_DIFF_SQL,N'@DB_NAME NVARCHAR(128),@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DATE_SERIAL_NUMBER NVARCHAR(16)',@DB_NAME,@BACK_PATH,@Instance,@DATE_SERIAL_NUMBER
    			SET @ERROR=@@ERROR
    		END
    		IF @TYPE='LOG'
    		BEGIN
    			SET @BACKUP_LOG_SQL=N'BACKUP LOG'+CHAR(9)+'['+@DB_NAME+']'+CHAR(9)
    								+'TO DISK= '''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+'\'+@DB_NAME+@DATE_SERIAL_NUMBER+'.TRN'''+CHAR(9)+'WITH NOFORMAT, NOINIT,'
    								+'NAME=N'''+@DB_NAME+'-Transaction Log Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
    			SET @BACKUP_FILENAME=@DB_NAME+@DATE_SERIAL_NUMBER+'.TRN'
    			--EXEC (@BACKUP_LOG_SQL)
    			SET @BACKUP_BEGIN=GETDATE()
    			EXEC SP_EXECUTESQL @BACKUP_LOG_SQL,N'@DB_NAME NVARCHAR(128),@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DATE_SERIAL_NUMBER NVARCHAR(16)',@DB_NAME,@BACK_PATH,@Instance,@DATE_SERIAL_NUMBER
    			SET @ERROR=@@ERROR
    		END
    		IF @ERROR=0
    		BEGIN
    			--INSERT INTO DBA_Maintenance.dbo.db_backup_record select @DB_NAME,@BACKUP_BEGIN,getdate(), @BACK_PATH+'\'+@Instance+'\'+@DB_NAME,@TYPE,@BACKUP_FILENAME,0  --version 1.0
    			INSERT INTO DBA_Maintenance.dbo.db_backup_record select @DB_NAME,@BACKUP_BEGIN,getdate(), @BACK_PATH+'\'+@Instance+'\'+@DB_NAME+'\'+@BACKUP_FILENAME,@TYPE,0  --version 1.1
    		END
    
    	SET NOCOUNT OFF
    END
    
    GO