sqlserverDatabase Backup方法

標籤：

須事先準備一個工具curl，把它放在c盤。然後，在資料庫所在伺服器安裝7z。最後把這2個預存程序執行，在sqlserver的代理中新增作業，即可實現備份操作。

 

 

--備份指定資料庫到本地和遠程指定位置(7-zip壓縮)Create PROCEDURE [dbo].[BackUpDB7z]@dbname sysname --資料庫名ASdeclare @backupfile nvarchar(200) --本地備份檔案名declare @backuplogfile nvarchar(200) --本地記錄備份檔案名稱declare @archivefile nvarchar(200) --本地壓縮檔名declare @archivelogfile nvarchar(200) --本地壓縮檔名declare @target_localpath nvarchar(800) --本地備份路徑declare @target_backupfile nvarchar(1000) --本地備份檔案完整路徑declare @target_backuplogfile nvarchar(1000) --本地備份記錄檔完整路徑declare @target_archivefile nvarchar(1000) --本地壓縮檔完整路徑declare @target_archivelogfile nvarchar(1000) --本地日誌壓縮檔完整路徑declare @descr nvarchar(100) --備份檔案描述declare @date datetimedeclare @date_str varchar(100) --日期時間字串declare @cmd nvarchar(4000) --要執行的命令declare @log_file nvarchar(200) --declare @cmd_7z nvarchar(200)declare @7z_opt nvarchar(1000)declare @cmd_tool nvarchar(200)=‘C:\curl\curl -T‘declare @remotepath nvarchar(800)=‘‘ --遠程備份路徑declare @ftp_user nvarchar(300)=‘‘--ftp帳號declare @ftp_pw nvarchar(200)=‘‘--ftp密碼declare @ftp_opt nvarchar(1000)=‘ftp://‘declare @localpath nvarchar(800)=‘‘ --本地備份路徑--參數設定select @log_file=‘d:\shellcmd_log.txt‘select @cmd_7z=‘7z ‘select @7z_opt=‘a -t7z -mx=9 -mmt=on‘ select @date=getdate()select @[email protected]+cast(year(@date) as nvarchar)+‘年‘+cast(month(@date) as nvarchar)+‘月‘+cast(day(@date) as nvarchar)+‘日完全備份‘select @[email protected]+‘.bak‘select @[email protected]+‘.Log.bak‘select @date_str=convert(varchar(100), @date, 120)select @date_str=REPLACE(@date_str,‘-‘,‘‘)select @date_str=REPLACE(@date_str,‘ ‘,‘_‘)select @date_str=REPLACE(@date_str,‘:‘,‘‘)select @[email protected]+‘_‘[email protected]_str+‘.7z‘select @[email protected]+‘_‘[email protected]_str+‘.Log.7z‘if (@localpath<>‘‘ and right(@localpath,1)<>‘\‘)select @[email protected]+‘\‘elseselect @target_localpath=@localpathselect @[email protected]_localpath+@backupfileselect @[email protected]_localpath+@backuplogfileselect @[email protected]_localpath+@archivefileselect @[email protected]_localpath+@archivelogfile--收縮資料庫--dump transaction @dbname with no_log--DBCC SHRINKDATABASE (@dbname, 0,TRUNCATEONLY)--備份資料庫backup database @dbname to disk[email protected]_backupfile with FORMAT , description=@descr--備份日誌backup log @dbname to disk=@target_backuplogfile with FORMATexec [ClearDbLog] @dbname--壓縮資料庫select @[email protected]_7z+‘ ‘[email protected]_opt+‘ ‘[email protected]_archivefile+‘ ‘ +@target_backupfile--執行命令exec xp_cmdshell @cmd--壓縮日誌select @[email protected]_7z+‘ ‘[email protected]_opt+‘ ‘[email protected]_archivelogfile+‘ ‘ +@target_backuplogfile--執行命令exec xp_cmdshell @cmd--傳送壓縮檔到遠程伺服器if @remotepath<>‘‘beginselect @[email protected]_tool+‘ ‘[email protected]_archivefile+‘ -u ‘[email protected]_user+‘:‘[email protected]_pw+‘ ‘[email protected]_opt+‘‘+@remotepath--print(@cmd)--執行命令exec xp_cmdshell @cmdselect @[email protected]_tool+‘ ‘[email protected]_archivelogfile+‘ -u ‘[email protected]_user+‘:‘[email protected]_pw+‘ ‘[email protected]_opt+‘‘+@remotepath--print(@cmd)--執行命令exec xp_cmdshell @cmdend--刪除本地備份bak檔案select @cmd=‘del ‘+@target_backupfile--print(@cmd)--執行命令exec xp_cmdshell @cmdselect @cmd=‘del ‘+@target_backuplogfile--print(@cmd)--執行命令exec xp_cmdshell @cmd--刪除本地備份7z檔案select @cmd=‘del ‘+@target_archivefile--print(@cmd)--執行命令exec xp_cmdshell @cmdselect @cmd=‘del ‘+@target_archivelogfile--print(@cmd)--執行命令exec xp_cmdshell @cmd

 

 

 

--清除資料庫日誌CREATE PROCEDURE [dbo].[ClearDbLog] @DataBase sysname ASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;        declare @sql2005 varchar(max)    declare @sql2008 varchar(max)        set @sql2005=‘    DUMP TRANSACTION ‘[email protected]+‘ WITH NO_LOG;    DBCC SHRINKDATABASE (‘[email protected]+‘, 0,TRUNCATEONLY);    ‘        set @sql2008=‘    ALTER DATABASE ‘[email protected]+‘ SET RECOVERY SIMPLE;    DBCC SHRINKDATABASE (‘[email protected]+‘, 0,TRUNCATEONLY);    ALTER DATABASE ‘[email protected]+‘ SET RECOVERY FULL;    ‘    --print(@sql);    exec(@sql2008);ENDGO

 

sqlserverDatabase Backup方法