How to make a SQL Server database automatically backed up and compressed into a RAR file

1. Open Xm_cmdshell Service First

The xp_cmdshell extended stored procedure executes the command string as the operating system command shell and returns all output as a text line. Since xp_cmdshell can execute any operating system command, once a SQL Server administrator account (such as SA) is compromised, an attacker can take advantage of xp_cmdshell to execute operating system commands in SQL Server, such as: Create a system administrator, It means that the system's highest authority is in the hands of others. Because of the security implications, xp_cmdshell is turned off by default in SQL Server 2005.

Two ways to enable xp_cmdshell

1. Open the Perimeter application Configurator
Features of the perimeter application configurator,
Instance Name database Enginexp_cmdshell-> enabled

2.sp_configure
--Open the xp_cmdshell section
--------------------------------------------------
--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


--part of executing shell commands through xp_cmdshell
--------------------------------------------------
Exec xp_cmdshell ' bcp '
GO


--Close the xp_cmdshell section
-----------------------------------------------------
--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

3, create a new job, named "MyDb Full backup ", under the classification select "Database Maintenance", and then create a new job first step, the step is named "Data in."

DECLARE @strSql VARCHAR (1000)

, @strSqlCmd VARCHAR (1000)

, @timeDateDiffINT

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 '--backup directory and file header for backup

+convert (CHAR (8), DATEADD (week, @timeDateDiff, 0), 112)--Full backup date

+ ' _0100 '--Full backup time

+ ' full backup '

SET @strSqlCmd = @strSql + '. BAK '--the extension of the backup file

BACKUP database[liangjialun219]

To DISK = @strSqlCmd Withinit

, Nounload

, NAME = N ' LiangJiaLun219 backup '

, Noskip

, STATS = 10

, Noformat

Operation One:

4. Then start compressing the database, create a new job in the step, name "Compress database", and then enter the following SQL code in the Command box:

DECLARE @strSql VARCHAR (1000)

, @strSqlCmd VARCHAR (1000)

, @timeDateDiffINT

, @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 '--backup directory and file header for backup

+convert (CHAR (8), DATEADD (week, @timeDateDiff, 0), 112)--Full backup date

+ ' _0100 '--Full backup time

+ ' full backup '

SET @strWeekDay = Case DATEPART (weekday,getdate ()) If 1 Then ' Sunday '

When 2 Then ' Monday '

When 3 Then ' Tuesday '

When 4 Then ' Wednesday '

When 5 Then ' Thursday '

When 6 then ' Friday '

When 7 Then ' Saturday ' END

SET @strSqlCmd = ' ECHO compression start date: ' +convert (VARCHAR), GETDATE (), + ' [email protected]+ ' >> E:\DataBackup\ compressdatabase\liangjialun219 ' +convert (CHAR (6), DATEADD (week, @timeDateDiff, 0),) + '. txt '

EXEC Master.dbo.xp_cmdshell@strsqlcmd,no_output

SET @strSqlCmd = ' RAR. EXE a-r ' [email protected]+ '. RAR ' [Email protected]+ '. BAK >> E:\DataBackup\CompressDataBase\LiangJiaLun219 ' +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 compression date: ' +convert (VARCHAR), GETDATE (), + "[Email protected]+ ' >>e:\databackup\ compressdatabase\liangjialun219 ' +convert (CHAR (6), DATEADD (week, @timeDateDiff, 0),) + '. txt '

EXEC Master.dbo.xp_cmdshell@strsqlcmd,no_output

After completion we can see the steps of the dialog box, three, data full backup step:

6. Set up Scheduled Tasks

7, if the execution of code first must install WinRar and then in the environment variable---the system environment variable path in the path of the Rar.exe to add (such as: C:/Program files/winrar/) So you can, optimistic only need to add the path ... We do not need Rar.exe; These steps are for the red place below, because to execute Rar.exe it is not an internal program or an internal command ...