如何让SQL Server数据库自动备份并压缩

1、如何让SQLServer数据库自动备份并压缩如果SQLServer代理没有启动，我们先把其启动，然后新建立一个作业，名称命名为“MyDb完全备份”，在分类下面选择数据库维护”，然后新建立作业第一个步骤，步骤名为对数据进行完全备份”，然后在命令框中输入如下的SQL代码：DECLAREstrSqlVARCHAR(IOOO),strSqlCmdVARCHAR(IOOO),timeDateDiffINTSETtimeDateDiff=DATEDIFF(week,O,GETDATE()SETtimeDateDiff=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THENtimeD

2、ateDiff-1ELSEtimeDateDiffENDSETstrSql=D:DataBaseBackDataMyDb_-备份目录及备份的文件头+CONVERT(CHAR(8),DATEADD(week,timeDateDiff,0),112)-完全备份日期+_0100-完全备份时间+完全备份SETstrSqlCmd=strSql+.BAK-备份文件的扩展名BACKUPDATABASEMyDbTODISK=strSqlCmdWITHINIT,NOUNLOAD,NAME=NMyDb备份,NOSKIP,STATS=10,NOFORMAT操作如图一:然后开始执行对数据库的压缩，在步骤中再新建一个作

3、业，步骤名为压缩数据库”，然后在命令框中输入如下的SQL代码：DECLAREstrSqlVARCHAR(IOOO),strSqlCmdVARCHAR(IOOO),timeDateDiffINT,strWeekDayVARCHAR(20)SETtimeDateDiff=DATEDIFF(week,O,GETDATE()SETtimeDateDiff=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THENtimeDateDiff-1ELSEtimeDateDiffENDSETstrSql=D:DataBaseBackDataMyDb_-备份目录及备份的文件头+CONVERT

4、(CHAR(8),DATEADD(week,timeDateDiff,0),112)-完全备份日期+_0100-完全备份时间+完全备份SETstrWeekDay=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THEN星期天WHEN2THEN星期一WHEN3THEN星期二WHEN4THEN星期三WHEN5THEN星期四WHEN6THEN星期五WHEN7THEN星期六ENDSETstrSqlCmd=ECHO压缩开始日期：+CONVERT(VARCHAR(20),GETDATE(),120)+strWeekDay+D:DataBaseBackDataCompressDataB

5、aseMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtEXECmaster.dbo.XP_CMDSHELLstrSqlCmd,NO_OUTPUTSETstrSqlCmd=RAR.EXEA-R+strSql+.RAR+strSql+.BAKD:DataBaseBackDataCompressDataBaseMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtPRINTLEN(strSqlCmd)PRINT(strSqlCmd)EXECmaster.dbo.X

6、P_CMDSHELLstrSqlCmd,NO_OUTPUTSETstrSqlCmd=ECHO压缩日期：+CONVERT(VARCHAR(20),GETDATE(),120)+strWeekDay+D:DataBaseBackDataCompressDataBaseMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtEXECmaster.dbo.XP_CMDSHELLstrSqlCmd,NO_OUTPUT操作如图二:完成后我们可以看到操作步骤的对话框，如图三:图三数据完全备份的步骤我们对照上图，注意两点，第一个是步骤1“成功时”

7、这一列的显示，当成功的时候转到下一步，失败时”当失败的时候失败后退出，步骤2“成功时”当成功的时候成功后退出，失败时当失败时失败后退出。确保两个步骤对数据操作的正常。再执行调度”一栏，主要实现在什么时候执行这些作业，我们定在每周日一点的时候开始执行，如图四:这样就可以建立好对数据库的整个完全备份了。有时我们数据在遭到破坏的时候，而在恢复到上次的整个备份时，就会产生很多丢失的数据了，这时我们就必须还得建立另外一种备份的机制一差异备份。步骤还和上面一样，我们建立一个作业，命名为“MyDb差异备份”，在步骤里面同样是建立两个步骤，分别是差异备份和差异压缩，步骤一在命令框中输入内容如下：DECLARE

8、strSqlVARCHAR(IOOO),strSqlCmdVARCHAR(IOOO),timeDateDiffINTSETtimeDateDiff=DATEDIFF(week,O,GETDATE()SETtimeDateDiff=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THENtimeDateDiff-1ELSEtimeDateDiffENDSETstrSql=D:DataBaseBackDataMyDb_-备份目录及备份的文件头+CONVERT(CHAR(8),DATEADD(week,timeDateDiff,0),112)-完全备份日期+_0100-完全备份

9、时间+差异备份+_+CONVERT(CHAR(8),GETDATE(),112)-差异备份日期+_0300-差异备份时间SETstrSqlCmd=strSql+.BAK-备份文件的扩展名BACKUPDATABASEwebEIMS2008TODISK=cSqlCmdWITHINIT,NOUNLOADDIFFERENTIAL,NAME=NMyDb差异备份,NOSKIP,STATS=10,NOFORMAT我们可以看到，差异备份除了文件名命名格式不一样外，就在备份执行SQL语句时增加了了下DIFFERENTIAL参数，然后再执行。步骤二在命令框中执行如下：DECLAREstrSqlVARCHAR(IO

10、OO),strSqlCmdVARCHAR(IOOO),timeDateDiffINT,strWeekDayVARCHAR(20)SETtimeDateDiff=DATEDIFF(week,0,GETDATE()SETtimeDateDiff=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THENtimeDateDiff-1ELSEtimeDateDiffENDSETstrSql=D:DataBaseBackDataMyDb_-备份目录及备份的文件头+CONVERT(CHAR(8),DATEADD(week,timeDateDiff,0),112)-完全备份日期+_010

11、0-完全备份时间+差异备份+_+CONVERT(CHAR(8),GETDATE(),112)-差异备份日期+_0300-差异备份时间SETstrWeekDay=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THEN星期天WHEN2THEN星期一WHEN3THEN星期二WHEN4THEN星期三WHEN5THEN星期四WHEN6THEN星期五WHEN7THEN星期六ENDSETstrSqlCmd=ECHO压缩开始日期：+CONVERT(VARCHAR(20),GETDATE(),120)+strWeekDay+D:DataBaseBackDataCompressDataBa

12、seMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtEXECmaster.dbo.XP_CMDSHELLstrSqlCmd,NO_OUTPUTSETstrSqlCmd=RAR.EXEA-R+strSql+.RAR+strSql+.BAKD:DataBaseBackDataCompressDataBaseMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtPRINTLEN(strSqlCmd)PRINT(strSqlCmd)EXECmaster.dbo.XP

13、_CMDSHELLstrSqlCmd,NO_OUTPUTSETstrSqlCmd=ECHO压缩结束日期：+CONVERT(VARCHAR(20),GETDATE(),120)+strWeekDay+D:DataBaseBackDataCompressDataBaseMyDb_+C0NVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtEXECmaster.dbo.XP_CMDSHELLstrSqlCmd,NO_OUTPUT这时我们已经建立好了步骤，只是现在建立作业调度的时候有些变化，我们看图五：图五建立差异备份作业调度对比完全备份建立的作业调度

14、，在这里我们可以看到，我们选择的时间是除了周日以外的每天夜里3点的时候，自动执行此次调度。当然时间是自己灵活分配的，如数据发生的变化比较大，我们可以选择每天，然后频率选择发生周期性短一点，这样我们数据在遭到破坏的时候，我们就可以及时的恢复了。如果在SQLServer2000中，我们可以建立如上的作业就可以对数据进行备份了，而对于SQLServer2005，还有一点细微的变化，因为它默认是不支持xp_cmdshell执行命令的，SQLServer已封锁元件xp_cmdshell的程序sys.xp_cmdshell之存取，因为此元件已经由此伺服器的安全性组态关闭。系统管理员可以使用sp_confi

15、gure来启用xp_cmdshell的使用。所以我们得恢复其执行命令：用下面一句话就可以了解决了。EXECsp_configureshowadvancedoptions,1;RECONFIGURE;EXECsp_configurexp_cmdshell,RECONFIGURE;利用WINRAR实现SQLSERVER自动压缩的方法下面为您介绍的方法可以实现SQLSERVER自动压缩，有了SQLSERVER自动压缩,就不必每天再浪费时间进行手动的压缩了。当SQL数据库大于2G时XP_MAKECAB扩展存储过程压缩失败，结合WINDOWS操作系统的任务计划，建立命令行下的批处理文件(.BAT),实现

16、每天SQLSERVER自动压缩,如下：1、SQLSERVER自动备份2、COPYWINRAR目录到以下批处理文件所在的目录3、用文本编辑器建立批处理文件(.BAT),如下内容echooffecho.echo.coloraclsechooffecho.echo欢迎来到数据库备份压缩批处理echo.echo.echo.echo.正在自动压缩备份数据.echoecho.echo.echo.echo.echo.echooffifnotexistzsimcmis_db_%date:0,4%date:5,2%date:8,2%1830.bakgotoexistfile-检查是否存在未被压缩数据库备份文件g

17、otobackup:backupecho已经找到备份好的备份文件zsimcmis_db_%date:0,4%date:5,2%date:8,2%1830.bakecho.echo!提示:正在压缩备份文件.!！echo.echo!提示:正在将备份文件zsimcmis_db_%date:0,4%date:5,2%date:8,2%1830.bak压缩到G:Cabfileszsimcmis_db_%date:0,4%date:5,2%date:8,2%2000.RAR文件中-只是显示屏幕而已，中间的文件名没实际用处WinRARwinrara-as-ibckG:Cabfileszsimcmis_db_.rar-m3-agyyyym