如何让SQL Server数据库自动备份并压缩成rar文件

1、 先开启xm_cmdshell服务

xp_cmdshell 扩展存储过程将命令字符串作为 *** 作系统命令 shell 执行，并以文本行的形式返回所有输出。由于xp_cmdshell 可以执行任何 *** 作系统命令，所以一旦SQL Server管理员帐号（如sa）被攻破，那么攻击者就可以利用xp_cmdshell 在SQL Server中执行 *** 作系统命令，如：创建系统管理员，也就意味着系统的最高权限已在别人的掌控之中。由于存在安全隐患，所以在SQL Server 中， xp_cmdshell 默认是关闭的。

两种方式启用xp_cmdshell

a打开外围应用配置器—>

功能的外围应用配置器—>

实例名Database Enginexp_cmdshell—>　启用

bsp_configure

--开启xp_cmdshell部分

--------------------------------------------------

-- To allow advanced options to be changed

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options

RECONFIGURE

GO

-- To enable the feature

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature

RECONFIGURE

GO

--通过xp_cmdshell执行shell命令的部分

--------------------------------------------------

Exec xp_cmdshell 'bcp '

GO

--关闭xp_cmdshell部分

-----------------------------------------------------

-- To allow advanced options to be changed

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options

RECONFIGURE

GO

-- To enable the feature

EXEC sp_configure 'xp_cmdshell', 0

GO

-- To update the currently configured value for this feature

RECONFIGURE

GO

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='E:\DataBackup\LiangJiaLun219' -- 备份目录及备份的文件头

+CONVERT(CHAR(8),DATEADD(week, @timeDateDiff,0),112) -- 完全备份日期

+'_0100' -- 完全备份时间

+'完全备份'

SET @strSqlCmd= @strSql+'BAK' --备份文件的扩展名

BACKUP DATABASE [LiangJiaLun219]

TO DISK = @strSqlCmd WITH INIT

,NOUNLOAD

,NAME = N'LiangJiaLun219 备份'

,NOSKIP

,STATS = 10

,NOFORMAT

3、　然后开始执行对数据库的压缩，在步骤中再新建一个作业，步骤名为“压缩数据库”，然后在命令框中输入如下的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='E:\DataBackup\LiangJiaLun219' -- 备份目录及备份的文件头

+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+' >> E:\DataBackup\CompressDataBase\LiangJiaLun219'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'txt'

EXEC masterdboXP_CMDSHELL @strSqlCmd,NO_OUTPUT

SET @strSqlCmd= 'RAREXE A -R '+@strSql+'RAR '+@strSql+'BAK >> E:\DataBackup\CompressDataBase\LiangJiaLun219'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'txt'

PRINT LEN(@strSqlCmd)

PRINT (@strSqlCmd)

EXEC masterdboXP_CMDSHELL @strSqlCmd,NO_OUTPUT

SET @strSqlCmd= 'ECHO 压缩日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> E:\DataBackup\CompressDataBase\LiangJiaLun219'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'txt'

EXEC masterdboXP_CMDSHELL @strSqlCmd,NO_OUTPUT

4、设定计划任务

5、如果执行一下代码 首先必须安装WinRar 然后 在环境变量中---系统环境变量 Path 中 把Rarexe 的路径加进去 （如：C：/Program Files/WinRar/） 这样就可以了。只需要加路径。。。不需要Rarexe。

下面有两个SQL语句可以达到在SQL Server 2005/2008压缩指定数据库文件和日志的大小的效果：

1、DBCC SHRINKDATABASE (Transact-SQL)

收缩指定数据库中的数据文件和日志文件的大小。

语法

DBCC SHRINKDATABASE

( 'database_name' | database_id | 0

[ ,target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

[ WITH NO_INFOMSGS ]

参数

'database_name' | database_id | 0 要收缩的数据库的名称或 ID。如果指定 0，则使用当前数据库。

target_percent 数据库收缩后的数据库文件中所需的剩余可用空间百分比。

NOTRUNCATE 通过将已分配的页从文件末尾移动到文件前面的未分配页来压缩数据文件中的数据。target_percent 是可选参数。 文件末尾的可用空间不会返回给 *** 作系统，文件的物理大小也不会更改。因此，指定 NOTRUNCATE 时，数据库看起来未收缩。 NOTRUNCATE 只适用于数据文件。日志文件不受影响。

TRUNCATEONLY 将文件末尾的所有可用空间释放给 *** 作系统，但不在文件内部执行任何页移动。数据文件只收缩到最近分配的区。如果与 TRUNCATEONLY 一起指定，将忽略 target_percent。 TRUNCATEONLY 只适用于数据文件。日志文件不受影响。

WITH NO_INFOMSGS 取消严重级别从 0 到 10 的所有信息性消息。

结果集

列名 说明

DbId 数据库引擎试图收缩的文件的数据库标识号。

FileId 数据库引擎尝试收缩的文件的文件标识号。

CurrentSize 文件当前占用的 8 KB 页数。

MinimumSize 文件最低可以占用的 8 KB 页数。这与文件的最小大小或最初创建时的大小相对应。

UsedPages 文件当前使用的 8 KB 页数。

EstimatedPages 数据库引擎估计文件能够收缩到的 8 KB 页数。

备注

若要收缩特定数据库的所有数据和日志文件，请执行 DBCC SHRINKDATABASE 命令。若要一次收缩一个特定数据库中的一个数据或日志文件，请执行 DBCC SHRINKFILE 命令。

若要查看数据库中当前的可用（未分配）空间量，请运行 sp_spaceused。

可在进程中的任一点停止 DBCC SHRINKDATABASE *** 作，任何已完成的工作都将保留。

收缩后的数据库不能小于数据库的最小大小。最小大小是在数据库最初创建时指定的大小，或是使用文件大小更改 *** 作（如 DBCC SHIRNKFILE 或 ALTER DATABASE）显式设置的最后大小。例如，如果数据库最初创建时的大小为 10 MB，后来增长到 100 MB，则该数据库最小只能收缩到 10 MB，即使已经删除数据库的所有数据也是如此。

运行 DBCC SHRINKDATABASE 而不指定 NOTRUNCATE 选项或 TRUNCATEONLY 选项等价于带 NOTRUNCATE 运行 DBCC SHRINKDATABASE *** 作，然后再带 TRUNCATEONLY 运行 DBCC SHRINKDATABASE *** 作。

要收缩的数据库不必在单用户模式下；其他的用户仍可以在数据库收缩时对其进行工作。这也包括系统数据库。

不能在备份数据库时收缩数据库。反之，也不能在数据库执行收缩 *** 作时备份数据库。

DBCC SHRINKDATABASE 的工作原理

DBCC SHRINKDATABASE 以每个文件为单位对数据文件进行收缩。然而，DBCC SHRINKDATABASE 在对日志文件进行收缩时，它将视为所有的日志文件都存在于一个连续的日志池中。文件始终从末尾开始收缩。

假设名为 mydb 的数据库有一个数据文件和两个日志文件。数据文件和日志文件分别是 10 MB，并且数据文件包含 6 MB 数据。

Microsoft SQL Server 2005 数据库引擎对每个文件计算一个目标大小。这就是文件将要收缩到的大小。将 target_percent 与 DBCC SHRINKDATABASE 一起指定时，数据库引擎计算的目标大小是收缩后文件中的 target_percent 可用空间大小。例如，如果在收缩 mydb 时将 target_percent 指定为 25，则数据库引擎将此文件的目标大小计算为 8 MB（6 MB 数据加上 2 MB 可用空间）。因此，数据库引擎将任何数据从数据文件的后 2 MB 中移动到数据文件前 8 MB 的可用空间中，然后对该文件进行收缩。

假设 mydb 的数据文件包含 7 MB 的数据。将 target_percent 指定为 30，以允许将此数据文件收缩到可用空间为 30％。但是，将 target_percent 指定为 40 却不会收缩数据文件，因为数据库引擎收缩文件的目标大小不能小于数据当前占用空间大小。您还可以用另一种方法来考虑此问题：所要求的 40％ 可用空间加上整个数据文件大小的 70％（10 MB 中的 7 MB），超过了 100％。因为所要求的可用百分比加上数据文件占用的当前百分比大于 100%（多出 10%），所以任何大于 30 的 target_size 都不会收缩此数据文件。

对于日志文件，数据库引擎使用 target_percent 来计算整个日志的目标大小；因此，target_percent 是收缩 *** 作后日志中的可用空间大小。之后，整个日志的目标大小转换为每个日志文件的目标大小。

DBCC SHRINKDATABASE 尝试立即将每个物理日志文件收缩到其目标大小。如果虚拟日志中的所有逻辑日志部分都没有超出日志文件的目标大小，则该文件将成功截断，DBCC SHRINKDATABASE 完成且不显示任何消息。但是，如果部分逻辑日志位于超出目标大小的虚拟日志中，则数据库引擎将释放尽可能多的空间，并发出一条信息性消息。该消息说明需要执行哪些 *** 作来将逻辑日志移出位于文件末尾的虚拟日志。执行该 *** 作以后，DBCC SHRINKDATABASE 可用于释放剩余空间。有关详细信息，请参阅收缩事务日志。

因为日志文件只能收缩到虚拟日志文件边界，所以不可能将日志文件收缩到比虚拟日志文件更小（即使现在没有使用该文件）。虚拟日志文件的大小在创建或扩展这些日志文件时由数据库引擎动态选择。有关虚拟日志文件的详细信息，请参阅事务日志物理体系结构。

下面有两个SQL语句可以达到在SQL

Server

2005/2008压缩指定数据库文件和日志的大小的效果：

1、DBCC

SHRINKDATABASE

(Transact-SQL)

收缩指定数据库中的数据文件和日志文件的大小。

语法

DBCC

SHRINKDATABASE

(

'database_name'

|

database_id

|

0

[

,target_percent

]

[

,

{

NOTRUNCATE

|

TRUNCATEONLY

}

]

)

[

WITH

NO_INFOMSGS

]

参数

'database_name'

|

database_id

|

0

要收缩的数据库的名称或

ID。如果指定

0，则使用当前数据库。

target_percent

数据库收缩后的数据库文件中所需的剩余可用空间百分比。

NOTRUNCATE

通过将已分配的页从文件末尾移动到文件前面的未分配页来压缩数据文件中的数据。target_percent

是可选参数。

文件末尾的可用空间不会返回给 *** 作系统，文件的物理大小也不会更改。因此，指定

NOTRUNCATE

时，数据库看起来未收缩。

NOTRUNCATE

只适用于数据文件。日志文件不受影响。

TRUNCATEONLY

将文件末尾的所有可用空间释放给 *** 作系统，但不在文件内部执行任何页移动。数据文件只收缩到最近分配的区。如果与

TRUNCATEONLY

一起指定，将忽略

target_percent。

TRUNCATEONLY

只适用于数据文件。日志文件不受影响。

以上就是关于如何让SQL Server数据库自动备份并压缩成rar文件全部的内容，包括:如何让SQL Server数据库自动备份并压缩成rar文件、如何压缩SQL Server 2005指定数据库文件和日志的大小、如何压缩SQL Server 2005指定数据库文件和日志的大小等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！