SQL Server自动备份并压缩
这里有两个方法，其中第一个比较繁琐但是比较详细，第二个较为简单，推荐用第二个方法！
方法一：
SQL Server代理没有启动，我们先把其启动，然后新建立一个作业，名称命名为“MyDb完全备份”，在分类下面选择“数据库维护”，然后新建立作业第一个步骤，步骤名为“对数据进行完全备份”，然后在命令框中输入如下的SQL代码：
如果
DECLARE @strSql VARCHAR(1000)
,@strSqlCmd VARCHAR(1000)
,@timeDateDiff INT
SET @timeDateDiff = DATEDIFF(week,0,GETDATE())
SET @timeDateDiff = CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN @timeDateDiff -1
ELSE @timeDateDiff END
SET @strSql='D:\DataBase\BackData\MyDb_' -- 备份目录及备份的文件头
+CONVERT(CHAR(8),DATEADD(week, @timeDateDiff,0),112) -- 完全备份日期
+'_0100' -- 完全备份时间
+'完全备份'
SET @strSqlCmd= @strSql+'.BAK' --备份文件的扩展名
BACKUP DATABASE [MyDb]
TO DISK = @strSqlCmd WITH INIT
,NOUNLOAD
,NAME = N'MyDb 备份'
,NOSKIP
,STATS = 10
,NOFORMAT
,@strSqlCmd VARCHAR(1000)
,@timeDateDiff INT
SET @timeDateDiff = DATEDIFF(week,0,GETDATE())
SET @timeDateDiff = CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN @timeDateDiff -1
ELSE @timeDateDiff END
SET @strSql='D:\DataBase\BackData\MyDb_' -- 备份目录及备份的文件头
+CONVERT(CHAR(8),DATEADD(week, @timeDateDiff,0),112) -- 完全备份日期
+'_0100' -- 完全备份时间
+'完全备份'
SET @strSqlCmd= @strSql+'.BAK' --备份文件的扩展名
BACKUP DATABASE [MyDb]
TO DISK = @strSqlCmd WITH INIT
,NOUNLOAD
,NAME = N'MyDb 备份'
,NOSKIP
,STATS = 10
,NOFORMAT
操作如图一：
然后开始执行对数据库的压缩，在步骤中再新建一个作业，步骤名为“压缩数据库”，然后在命令框中输入如下的SQL代码：
DECLARE @strSql VARCHAR(1000)
,@strSqlCmd VARCHAR(1000)
,@timeDateDiff INT
,@strWeekDay VARCHAR(20)
SET @timeDateDiff= DATEDIFF(week,0,GETDATE())
SET @timeDateDiff= CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN @timeDateDiff-1
ELSE @timeDateDiff END
SET @strSql='D:\DataBase\BackData\MyDb_' -- 备份目录及备份的文件头
+CONVERT(CHAR(8),DATEADD(week,@timeDateDiff,0),112) -- 完全备份日期
+'_0100' -- 完全备份时间
+'完全备份'
SET @strWeekDay= CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN '星期天'
WHEN 2 THEN '星期一'
WHEN 3 THEN '星期二'
WHEN 4 THEN '星期三'
WHEN 5 THEN '星期四'
WHEN 6 THEN '星期五'
WHEN 7 THEN '星期六' END
SET @strSqlCmd= 'ECHO 压缩开始日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
SET @strSqlCmd= 'RAR.EXE A -R '+@strSql+'.RAR '+@strSql+'.BAK >> D:\DataBase\BackData\CompressDataBase\MyDb__'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
PRINT LEN(@strSqlCmd)
PRINT (@strSqlCmd)
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
SET @strSqlCmd= 'ECHO 压缩日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
,@strSqlCmd VARCHAR(1000)
,@timeDateDiff INT
,@strWeekDay VARCHAR(20)
SET @timeDateDiff= DATEDIFF(week,0,GETDATE())
SET @timeDateDiff= CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN @timeDateDiff-1
ELSE @timeDateDiff END
SET @strSql='D:\DataBase\BackData\MyDb_' -- 备份目录及备份的文件头
+CONVERT(CHAR(8),DATEADD(week,@timeDateDiff,0),112) -- 完全备份日期
+'_0100' -- 完全备份时间
+'完全备份'
SET @strWeekDay= CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN '星期天'
WHEN 2 THEN '星期一'
WHEN 3 THEN '星期二'
WHEN 4 THEN '星期三'
WHEN 5 THEN '星期四'
WHEN 6 THEN '星期五'
WHEN 7 THEN '星期六' END
SET @strSqlCmd= 'ECHO 压缩开始日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
SET @strSqlCmd= 'RAR.EXE A -R '+@strSql+'.RAR '+@strSql+'.BAK >> D:\DataBase\BackData\CompressDataBase\MyDb__'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
PRINT LEN(@strSqlCmd)
PRINT (@strSqlCmd)
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
SET @strSqlCmd= 'ECHO 压缩日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
操作如图二：
完成后我们可以看到操作步骤的对话框，如图三，数据完全备份的步骤：
我们对照上图，注意两点，第一个是步骤1“成功时”这一列的显示，当成功的时候转到下一步，“失败时”当失败的时候失败后退出，步骤2“成功时”当成功的时候成功后退出，“失败时”当失败时失败后退出。确保两个步骤对数据操作的正常。
再执行“调度”一栏，主要实现在什么时候执行这些作业，我们定在每周日一点的时候开始执行，如图四：
这样就可以建立好对数据库的整个完全备份了。
有时我们数据在遭到破坏的时候，而在恢复到上次的整个备份时，就会产生很多丢失的数据了，这时我们就必须还得建立另外一种备份的机制—差异备份。
步骤还和上面一样，我们建立一个作业，命名为“MyDb差异备份”，在步骤里面同样是建立两个步骤，分别是差异备份和差异压缩，步骤一在命令框中输入内容如下：
DECLARE @strSql VARCHAR(1000)
,@strSqlCmd VARCHAR(1000)
,@timeDateDiff INT
SET @timeDateDiff = DATEDIFF(week,0,GETDATE())
SET @timeDateDiff = CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN @timeDateDiff -1
ELSE @timeDateDiff END
SET @strSql='D:\DataBase\BackData\MyDb_' -- 备份目录及备份的文件头
+CONVERT(CHAR(8),DATEADD(week, @timeDateDiff,0),112) -- 完全备份日期
+'_0100' -- 完全备份时间
+'差异备份'
+'_'+CONVERT(CHAR(8),GETDATE(),112) -- 差异备份日期
+'_0300' -- 差异备份时间
SET @strSqlCmd= @strSql+'.BAK' --备份文件的扩展名
BACKUP DATABASE [webEIMS2008]
TO DISK = @cSqlCmd WITH INIT
,NOUNLOAD
,DIFFERENTIAL
,NAME = N'MyDb差异备份'
,NOSKIP
,STATS = 10
,NOFORMAT
,@strSqlCmd VARCHAR(1000)
,@timeDateDiff INT
SET @timeDateDiff = DATEDIFF(week,0,GETDATE())
SET @timeDateDiff = CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN @timeDateDiff -1
ELSE @timeDateDiff END
SET @strSql='D:\DataBase\BackData\MyDb_' -- 备份目录及备份的文件头
+CONVERT(CHAR(8),DATEADD(week, @timeDateDiff,0),112) -- 完全备份日期
+'_0100' -- 完全备份时间
+'差异备份'
+'_'+CONVERT(CHAR(8),GETDATE(),112) -- 差异备份日期
+'_0300' -- 差异备份时间
SET @strSqlCmd= @strSql+'.BAK' --备份文件的扩展名
BACKUP DATABASE [webEIMS2008]
TO DISK = @cSqlCmd WITH INIT
,NOUNLOAD
,DIFFERENTIAL
,NAME = N'MyDb差异备份'
,NOSKIP
,STATS = 10
,NOFORMAT
我们可以看到，差异备份除了文件名命名格式不一样外，就在备份执行SQL语句时增加了了下DIFFERENTIAL参数，然后再执行。
步骤二在命令框中执行如下：
View Code
DECLARE @strSql VARCHAR(1000)
,@strSqlCmd VARCHAR(1000)
,@timeDateDiff INT
,@strWeekDay VARCHAR(20)
SET @timeDateDiff= DATEDIFF(week,0,GETDATE())
SET @timeDateDiff= CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN @timeDateDiff-1
ELSE @timeDateDiff END
SET @strSql='D:\DataBase\BackData\MyDb_' -- 备份目录及备份的文件头
+CONVERT(CHAR(8),DATEADD(week,@timeDateDiff,0),112) -- 完全备份日期
+'_0100' -- 完全备份时间
+'差异备份'
+'_'+CONVERT(CHAR(8),GETDATE(),112) -- 差异备份日期
+'_0300' -- 差异备份时间
SET @strWeekDay= CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN '星期天'
WHEN 2 THEN '星期一'
WHEN 3 THEN '星期二'
WHEN 4 THEN '星期三'
WHEN 5 THEN '星期四'
WHEN 6 THEN '星期五'
WHEN 7 THEN '星期六' END
SET @strSqlCmd= 'ECHO 压缩开始日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
SET @strSqlCmd= 'RAR.EXE A -R '+@strSql+'.RAR '+@strSql+'.BAK >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
PRINT LEN(@strSqlCmd)
PRINT (@strSqlCmd)
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
SET @strSqlCmd= 'ECHO 压缩结束日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
,@strSqlCmd VARCHAR(1000)
,@timeDateDiff INT
,@strWeekDay VARCHAR(20)
SET @timeDateDiff= DATEDIFF(week,0,GETDATE())
SET @timeDateDiff= CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN @timeDateDiff-1
ELSE @timeDateDiff END
SET @strSql='D:\DataBase\BackData\MyDb_' -- 备份目录及备份的文件头
+CONVERT(CHAR(8),DATEADD(week,@timeDateDiff,0),112) -- 完全备份日期
+'_0100' -- 完全备份时间
+'差异备份'
+'_'+CONVERT(CHAR(8),GETDATE(),112) -- 差异备份日期
+'_0300' -- 差异备份时间
SET @strWeekDay= CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN '星期天'
WHEN 2 THEN '星期一'
WHEN 3 THEN '星期二'
WHEN 4 THEN '星期三'
WHEN 5 THEN '星期四'
WHEN 6 THEN '星期五'
WHEN 7 THEN '星期六' END
SET @strSqlCmd= 'ECHO 压缩开始日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
SET @strSqlCmd= 'RAR.EXE A -R '+@strSql+'.RAR '+@strSql+'.BAK >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
PRINT LEN(@strSqlCmd)
PRINT (@strSqlCmd)
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
SET @strSqlCmd= 'ECHO 压缩结束日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'
EXEC master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT
这时我们已经建立好了步骤，只是现在建立作业调度的时候有些变化，我们看图五：
对比完全备份建立的作业调度，在这里我们可以看到，我们选择的时间是除了周日以外的每天夜里3点的时候，自动执行此次调度。
当然时间是自己灵活分配的，如数据发生的变化比较大，我们可以选择每天，然后频率选择发生周期性短一点，这样我们数据在遭到破坏的时候，我们就可以及时的恢复了。
如果在SQL Server2000中，我们可以建立如上的作业就可以对数据进行备份了，而对于SQL Server2005，还有一点细微的变化，因为它默认是不支持xp_cmdshell执行命令的，SQL Server 已封锁元件 'xp_cmdshell' 的 程序 'sys.xp_cmdshell' 之存取，因为此元件已经由此伺服器的安全性组态关闭。系统管理员可以使用sp_configure来启用 'xp_cmdshell' 的使用。所以我们得恢复其执行命令：
用下面一句话就可以了解决了。
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
利用自动压缩WINRAR实现SQL SERVER
面为您介绍的方法可以实现SQL SERVER自动压缩，有了SQL SERVER自动压缩，就不必每天再浪费时间进行手动的压缩了。
当SQL数据库大于2G时XP_MAKECAB扩展存储过程压缩失败，结合WINDOWS 操作系统的任务计划，建立命令行下的批处理文件(.BAT)，实现每天 SQL SERVER自动压缩，如下：
1、SQL SERVER自动备份
2、COPY WINRAR目录到以下批处理文件所在的目录
3、用文本编辑器建立批处理文件（.BAT)，如下内容：
当SQL数据库大于2G时XP_MAKECAB扩展存储过程压缩失败，结合WINDOWS 操作系统的任务计划，建立命令行下的批处理文件(.BAT)，实现每天 SQL SERVER自动压缩，如下：
1、SQL SERVER自动备份
2、COPY WINRAR目录到以下批处理文件所在的目录
3、用文本编辑器建立批处理文件（.BAT)，如下内容：
@echo off
echo.
echo.
color a
cls
@echo off
echo.
echo 欢迎来到数据库备份压缩批处理_dudumao
echo.
echo.
echo.
echo. ....正在自动压缩备份数据....
echo ------------------------------------------------------------------------------
echo.
echo.
echo.
echo.
echo.
@echo off
if not exist zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak goto existfile --检查是否存在未被压缩数据库备份文件
goto backup
:backup
echo 已经找到备份好的备份文件zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak
echo .
echo !!!提示:正在压缩备份文件....!!!
echo .
echo !!!提示:正在将备份文件zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak压缩到G:\Cabfiles\zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%2000.RAR文件中....... --只是显示屏幕而已,中间的文件名没实际用处
WinRAR\winrar a -as -ibck G:\Cabfiles\zsimcmis_db_.rar -m3 -agyyyymmddhhmmss zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak --什么不知道意思，WINRAR命令行的参数参考一下。
goto end
:existfile
echo.
echo !!!备份失败!!!
echo 没有找到已经备份好的备份文件zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak
echo.
echo.
echo.
pause
goto end
:end
echo 操作完毕
echo.
echo.
color a
cls
@echo off
echo.
echo 欢迎来到数据库备份压缩批处理_dudumao
echo.
echo.
echo.
echo. ....正在自动压缩备份数据....
echo ------------------------------------------------------------------------------
echo.
echo.
echo.
echo.
echo.
@echo off
if not exist zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak goto existfile --检查是否存在未被压缩数据库备份文件
goto backup
:backup
echo 已经找到备份好的备份文件zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak
echo .
echo !!!提示:正在压缩备份文件....!!!
echo .
echo !!!提示:正在将备份文件zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak压缩到G:\Cabfiles\zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%2000.RAR文件中....... --只是显示屏幕而已,中间的文件名没实际用处
WinRAR\winrar a -as -ibck G:\Cabfiles\zsimcmis_db_.rar -m3 -agyyyymmddhhmmss zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak --什么不知道意思，WINRAR命令行的参数参考一下。
goto end
:existfile
echo.
echo !!!备份失败!!!
echo 没有找到已经备份好的备份文件zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak
echo.
echo.
echo.
pause
goto end
:end
echo 操作完毕
其中：XP system c:\> md d:\%Date:~0,4%%Date:~5,2%%Date:~8,2%
2k system c:\> md d:\%Date:~4,4%%Date:~9,2%%Date:~12,2%
4、在WIN的任务计划中，建立运行计划，命令就是运行上以批处理文件就是OK！不必每天手工压缩了。。。
方法二：
建立两个步骤，第一步备份出BAK数据库文件，第二步打包压缩数据库备份文件后并删除原备份文件
执行备份的命令：
declare @filename varchar(200)
set @filename='D:\'+convert(char(10),getdate(),120)+'.bak' --设置备份文件的路径和文件名
print @filename
backup database[DatabaseName] to disk=@filename with NOINIT,NOUNLOAD,NAME='backup',NOSKIP,STATS=10,NOFORMAT --执行备份
set @filename='D:\'+convert(char(10),getdate(),120)+'.bak' --设置备份文件的路径和文件名
print @filename
backup database[DatabaseName] to disk=@filename with NOINIT,NOUNLOAD,NAME='backup',NOSKIP,STATS=10,NOFORMAT --执行备份
压缩并删除源文件的命令：
declare @file varchar(200)
set @file='c:\winrar.exe a -ep -df D:\DatebaseName_'+convert(char(10),getdate(),120)+'.rar D:\'+convert(char(10),getdate(),120)+'.bak' --将压缩备份文件并删除源文件的winrar命令行命令
exec master..xp_cmdshell @file --执行命令
set @file='c:\winrar.exe a -ep -df D:\DatebaseName_'+convert(char(10),getdate(),120)+'.rar D:\'+convert(char(10),getdate(),120)+'.bak' --将压缩备份文件并删除源文件的winrar命令行命令
exec master..xp_cmdshell @file --执行命令
winrar命令行的压缩命令是：[winrar路径] a [参数] [压缩后的路径] [需要压缩的文件路径]，这是本文需要用到的命令格式。
a就是winrar压缩文件的命令参数
-ep参数的作用是不压缩文件的完整路径，也就是指压缩指定的文件。
-df参数的作用就是压缩后删除源文件
后面命令的意思就是把D盘根目录下以当天日期命名的备份文件压缩到D盘根目录，并命名为当天日期的rar文件
一般默认下winrar的程序都是安装在C:\Program Files\WinRAR下的，但是由于其中的Program Files目录名中有空格，CMD命令行中是不支持有空格的名字的，所以把程序复制到一个简单的路径方便调用-ep参数的作用是不压缩文件的完整路径，也就是指压缩指定的文件。
-df参数的作用就是压缩后删除源文件
后面命令的意思就是把D盘根目录下以当天日期命名的备份文件压缩到D盘根目录，并命名为当天日期的rar文件
