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

admin

2011年2月21日 23:32 本文热度 7587

我们通常在维护数据库的时候，都会建立一个备份的机制，在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

操作如图一：

图一建立作业对数据库进行完全备份

然后开始执行对数据库的压缩，在步骤中再新建一个作业，步骤名为“压缩数据库”，然后在命令框中输入如下的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

操作如图二：

　　图二建立作业对数据进行压缩

完成后我们可以看到操作步骤的对话框，如图三：

　　图三数据完全备份的步骤

我们对照上图，注意两点，第一个是步骤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

我们可以看到，差异备份除了文件名命名格式不一样外，就在备份执行SQL语句时增加了了下DIFFERENTIAL参数，然后再执行。

步骤二在命令框中执行如下：

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

　　这时我们已经建立好了步骤，只是现在建立作业调度的时候有些变化，我们看图五：

　　图五建立差异备份作业调度

对比完全备份建立的作业调度，在这里我们可以看到，我们选择的时间是除了周日以外的每天夜里3点的时候，自动执行此次调度。

当然时间是自己灵活分配的，如数据发生的变化比较大，我们可以选择每天，然后频率选择发生周期性短一点，这样我们数据在遭到破坏的时候，我们就可以及时的恢复了。

如果在SQL Server2000中，我们可以建立如上的作业就可以对数据进行备份了，而对于SQL Server2005，还有一点细微的变化，因为它默认是不支持xp_cmdshell执行命令的，SQL Server 已封锁元件 'xp_cmdshell' 的程序 'sys.xp_cmdshell' 之存取，因为此元件已经由此伺服器的安全性组态关闭。系统管理员可以使用sp_configure来启用 'xp_cmdshell' 的使用。所以我们得恢复其执行命令，用下面命令就可以了解决了。

-- 开启 xp_cmdshell
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- 关闭 xp_cmdshell
exec sp_configure 'show advanced options', 1;
RECONFIGURE;

该文章在 2023/10/10 11:12:58 编辑过


全部评论1
	admin 2014年9月7日 16:36 企业管理器里面的方法： 1、打开企业管理器 2、打开要处理的数据库 3、点击最上面菜单>工具>SQL查询分析器，打开SQL查询分析器 4、在输入窗口里面输入： SQL Server 2005版： DUMP TRANSACTION [clicksun] WITH NO_LOG; BACKUP LOG [clicksun] WITH NO_LOG; DBCC SHRINKDATABASE([clicksun]); SQL Server 2008及之后版本： alter DATABASE [clicksun] SET RECOVERY SIMPLE; DBCC SHRINKFILE ('clicksun_log' , 0, truncateONLY); DBCC SHRINKDATABASE([clicksun], 0); 注意上面的“clicksun_log”是数据库操作日志文件，名称并不一定是：数据库名_log，点晴MIS系统的clicksun数据库日志文件名为：oa_log，稳当起见，应该需要先临时查出来： select name from SYS.database_files where type_desc='LOG' 查看数据库日志大小　　sqlServer 如何查看数据库日志文件的大小　　查询分析器执行命令： `dbcc sqlperf(logspace)` 该评论在 2023/10/10 10:15:31 编辑过

关键字查询

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。

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

全部评论1

查看数据库日志大小