sqlserver数据库备份方法

-zip压缩) Create PROCEDURE [dbo].[BackUpDB7z] @dbname sysname --数据库名 AS declare @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 datetime declare @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 @descr=@dbname+cast(year(@date) as nvarchar)+‘年‘+cast(month(@date) as nvarchar)+‘月‘+cast(day(@date) as nvarchar)+‘日完全备份‘ select @backupfile=@dbname+‘.bak‘ select @backuplogfile=@dbname+‘.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 @archivefile=@dbname+‘_‘+@date_str+‘.7z‘ select @archivelogfile=@dbname+‘_‘+@date_str+‘.Log.7z‘ if (@localpath<>‘‘ and right(@localpath,1)<>‘\‘) select @target_localpath=@localpath+‘\‘ else select @target_localpath=@localpath select @target_backupfile=@target_localpath+@backupfile select @target_backuplogfile=@target_localpath+@backuplogfile select @target_archivefile=@target_localpath+@archivefile select @target_archivelogfile=@target_localpath+@archivelogfile --收缩数据库 --dump transaction @dbname with no_log --DBCC SHRINKDATABASE (@dbname, 0,TRUNCATEONLY) --备份数据库 backup database @dbname to disk=@target_backupfile with FORMAT , description=@descr --备份日志 backup log @dbname to disk=@target_backuplogfile with FORMAT exec [ClearDbLog] @dbname --压缩数据库 select @cmd=@cmd_7z+‘ ‘+@7z_opt+‘ ‘+@target_archivefile+‘ ‘ +@target_backupfile --执行命令 exec xp_cmdshell @cmd --压缩日志 select @cmd=@cmd_7z+‘ ‘+@7z_opt+‘ ‘+@target_archivelogfile+‘ ‘ +@target_backuplogfile --执行命令 exec xp_cmdshell @cmd --传送压缩文件到远程服务器 if @remotepath<>‘‘ begin select @cmd=@cmd_tool+‘ ‘+@target_archivefile+‘ -u ‘+@ftp_user+‘:‘+@ftp_pw+‘ ‘+@ftp_opt+‘‘+@remotepath --print(@cmd) --执行命令 exec xp_cmdshell @cmd select @cmd=@cmd_tool+‘ ‘+@target_archivelogfile+‘ -u ‘+@ftp_user+‘:‘+@ftp_pw+‘ ‘+@ftp_opt+‘‘+@remotepath --print(@cmd) --执行命令 exec xp_cmdshell @cmd end --删除本地备份bak文件 select @cmd=‘del ‘+@target_backupfile --print(@cmd) --执行命令 exec xp_cmdshell @cmd select @cmd=‘del ‘+@target_backuplogfile --print(@cmd) --执行命令 exec xp_cmdshell @cmd --删除本地备份7z文件 select @cmd=‘del ‘+@target_archivefile --print(@cmd) --执行命令 exec xp_cmdshell @cmd select @cmd=‘del ‘+@target_archivelogfile --print(@cmd) --执行命令 exec xp_cmdshell @cmd

 

 

 

--清除数据库日志

CREATE PROCEDURE [dbo].[ClearDbLog] 
@DataBase sysname 
AS
BEGIN
    -- 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 ‘+@DataBase+‘ WITH NO_LOG;
    DBCC SHRINKDATABASE (‘+@DataBase+‘, 0,TRUNCATEONLY);
    ‘
    
    set @sql2008=‘
    ALTER DATABASE ‘+@DataBase+‘ SET RECOVERY SIMPLE;
    DBCC SHRINKDATABASE (‘+@DataBase+‘, 0,TRUNCATEONLY);
    ALTER DATABASE ‘+@DataBase+‘ SET RECOVERY FULL;
    ‘
    --print(@sql);
    exec(@sql2008);
END






GO

 

sqlserver数据库备份方法

标签：