There are two methods here. The first method is cumbersome but detailed. The second method is relatively simple. The second method is recommended!
Method 1:
The SQL Server Agent is not started. First, we start it and create a new job named "MyDb ".Full backup", Select" database maintenance "under the category, and then create a new job. The first step is to name it" completely back up data ", and then enter the following SQL code in the Command box:
If
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 _ '-- Backup Directory and Backup File Header
+ CONVERT (CHAR (8), DATEADD (week, @ timeDateDiff, 0), 112) -- full backup date
+ '_ 100' -- full backup time
+ 'Full backup'
SET @ strSqlCmd = @ strSql + '. Bak' -- backup file extension
Backup database [MyDb]
To disk = @ strSqlCmd WITH INIT
, NOUNLOAD
, NAME = n' MyDb backup'
, NOSKIP
, STATS = 10
, NOFORMAT
Operation 1:
Then begin to execute
CompressionCreate a new job in the step, named "compress Database", and enter the following SQL code in the Command box: 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 _ '-- Backup Directory and Backup File Header
+ CONVERT (CHAR (8), DATEADD (week, @ timeDateDiff, 0), 112) -- full backup date
+ '_ 100' -- full backup time
+ 'Full backup'
SET @ strWeekDay = case datepart (WEEKDAY, GETDATE () WHEN 1 THEN 'sunday'
WHEN 2 THEN 'monday'
WHEN 3 THEN 'tues'
WHEN 4 THEN 'weday'
WHEN 5 THEN 'thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'satur' END
SET @ strSqlCmd = 'echo compression start date: '+ CONVERT (VARCHAR (20), GETDATE (), 120) + ''+ @ strWeekDay +'> D: \ DataBase \ BackData \ CompressDataBase \ mydb_'{convert(char(6},dateadd(week,@timedatediff,0},{{'.txt'
EXEC master. dbo. xp_mongoshell @ strSqlCmd, NO_OUTPUT
SET @ strSqlCmd = 'rar. exe a-R' + @ strSql + '. RAR' + @ strSql + '. BAK> D: \ DataBase \ BackData \ CompressDataBase \ done'
Print len (@ strSqlCmd)
PRINT (@ strSqlCmd)
EXEC master. dbo. xp_mongoshell @ strSqlCmd, NO_OUTPUT
SET @ strSqlCmd = 'echo compression Date: '+ CONVERT (VARCHAR (20), GETDATE (), 120) + ''+ @ strWeekDay +'> D: \ DataBase \ BackData \ CompressDataBase \ mydb_'{convert(char(6},dateadd(week,@timedatediff,0},{{'.txt'
EXEC master. dbo. xp_mongoshell @ strSqlCmd, NO_OUTPUT
Operation 2: After completing the operation, we can see the operation step dialog box. 3. Complete data backup steps: we compare them. Pay attention to the two points, the first is the display of the "successful" column in step 1. When the column is successful, it is switched to the next step. When the "failed" column fails, it exits, step 2 "upon success" exit after success and "upon failure" exit after failure. Make sure that the data operation is normal in the two steps. Execute the "scheduling" column to determine when to execute these jobs. We will start to execute these jobs every Sunday:
In this way, you can create a full backup of the database.
Sometimes when our data is damaged and the entire backup is restored to the previous one, a lot of lost data will be generated, in this case, we have to establish another backup mechanism-differential backup.
The procedure is also the same as above. We create a job named "MyDb differential backup". In this step, we also create two steps: Differential backup and differential compression, step 1 enter the following content in the Command box: 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 _ '-- Backup Directory and Backup File Header
+ CONVERT (CHAR (8), DATEADD (week, @ timeDateDiff, 0), 112) -- full backup date
+ '_ 100' -- full backup time
+ 'Differential backup'
+ '_' + CONVERT (CHAR (8), GETDATE (), 112) -- Differential backup date
+ '_ 100' -- Differential backup time
SET @ strSqlCmd = @ strSql + '. Bak' -- backup file extension
Backup database [webEIMS2008]
To disk = @ cSqlCmd WITH INIT
, NOUNLOAD
, DIFFERENTIAL
, NAME = n' MyDb differential backup'
, NOSKIP
, STATS = 10
, NOFORMAT
We can see that in addition to the different file name naming formats, DIFFERENTIAL backup adds the DIFFERENTIAL parameter when executing the SQL statement in backup, and then executes it.
Step 2 run the following command in the Command box: 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 _ '-- Backup Directory and Backup File Header
+ CONVERT (CHAR (8), DATEADD (week, @ timeDateDiff, 0), 112) -- full backup date
+ '_ 100' -- full backup time
+ 'Differential backup'
+ '_' + CONVERT (CHAR (8), GETDATE (), 112) -- Differential backup date
+ '_ 100' -- Differential backup time
SET @ strWeekDay = case datepart (WEEKDAY, GETDATE () WHEN 1 THEN 'sunday'
WHEN 2 THEN 'monday'
WHEN 3 THEN 'tues'
WHEN 4 THEN 'weday'
WHEN 5 THEN 'thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'satur' END
SET @ strSqlCmd = 'echo compression start date: '+ CONVERT (VARCHAR (20), GETDATE (), 120) + ''+ @ strWeekDay +'> D: \ DataBase \ BackData \ CompressDataBase \ mydb_'{convert(char(6},dateadd(week,@timedatediff,0},{{'.txt'
EXEC master. dbo. xp_mongoshell @ strSqlCmd, NO_OUTPUT
SET @ strSqlCmd = 'rar. exe a-R' + @ strSql + '. RAR' + @ strSql + '. BAK> D: \ DataBase \ BackData \ CompressDataBase \ done'
Print len (@ strSqlCmd)
PRINT (@ strSqlCmd)
EXEC master. dbo. xp_mongoshell @ strSqlCmd, NO_OUTPUT
SET @ strSqlCmd = 'echo compression end date: '+ CONVERT (VARCHAR (20), GETDATE (), 120) + ''+ @ strWeekDay +'> D: \ DataBase \ BackData \ CompressDataBase \ mydb_'{convert(char(6},dateadd(week,@timedatediff,0},{{'.txt'
EXEC master. dbo. xp_mongoshell @ strSqlCmd, NO_OUTPUT
Now we have set up the steps, but there are some changes in job scheduling. See figure 5:
Compared with the Job Scheduling created by full backup, we can see that the task scheduling is automatically executed at every night except Sunday.
Of course, the time is flexibly allocated by ourselves. If the data changes a lot, we can choose daily, and then the frequency will be a little shorter, so that when our data is damaged, we can recover it in time.
In SQL Server2000, we can create the preceding job to back up the data. For SQL Server2005, there are some minor changes, because it does not support xp_cmdshell command execution by default, SQL Server has blocked the 'xp _ cmdshell' program 'sys. access to xp_mongoshell, because this component has been disabled by this server's security configuration. The system administrator can use sp_configure to enable 'xp _ javasshell. So we have to restore it and execute the command:
Use the following sentence to solve the problem.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp _ Your shell', 1;
RECONFIGURE;
Automatic Compression
WINRAR
Implementation
SQL SERVERThe methods described in this section can be used to automatically compress SQL server. With the automatic compress of SQL SERVER, you do not have to waste time on manual compress every day.
When the SQL database is larger than 2 GB, The XP_MAKECAB extended storage process compression fails. Combined with the WINDOWS operating system task plan, the batch files under the command line are created (. BAT) to achieve automatic compression of SQL SERVER every day, as follows:
1. Automatic SQL SERVER backup
2. COPY the WINRAR directory to the directory where the following batch files are located
3. Use a text editor to create a batch file (. BAT) with the following content: @ echo off
Echo.
Echo.
Color
Cls
@ Echo off
Echo.
Echo welcome to database backup compression batch processing _ dudumao
Echo.
Echo.
Echo.
Echo ...... automatic compression of backup data ....
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 -- check whether there are uncompressed database backup files
Goto backup
: Backup
Echo has found the backed up file zsimcmis_db _ % date :~ 0, 4% % date :~ 5, 2% % date :~ 8, 2% 1830.bak
Echo.
Echo !!! Tip: compressing the backup file ....!!!
Echo.
Echo !!! Tip: zsimcmis_db _ % date :~ 0, 4% % date :~ 5, 2% % date :~ 1830. bak compressed to G: \ Cabfiles \ zsimcmis_db _ % date :~ 0, 4% % date :~ 5, 2% % date :~ 8, 2000. In the rarfile ...... -- only display the screen. The file name in the middle is useless.
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 -- do not know what it means. For more information about the parameters of the WINRAR command line, see.
Goto end
: Existfile
Echo.
Echo !!! Backup failed !!!
Echo does not find the backup file zsimcmis_db _ % date :~ 0, 4% % date :~ 5, 2% % date :~ 8, 2% 1830.bak
Echo.
Echo.
Echo.
Pause
Goto end
: End
Echo operation completed
Where: XP system c :\> md d :\% Date :~ 0, 4% % Date :~ 5, 2% % Date :~ 8, 2%
2 k system c: \> md d: \ % Date :~ 4,4% % Date :~ 9,2% % Date :~ 12,2%
4. In the WIN task plan, create a running plan. The command is run to process files in batches, Which is OK! You don't have to manually compress it every day...
Method 2:
Create two steps. First, back up the BAK database file. Second, compress the database backup file and delete the original backup file.
Run the BACKUP command:
Declare @ filename varchar (200)
Set @ filename = 'd: \ '+ convert (char (10), getdate (), 120) +'. Bak' -- set the path and file name of the backup file
Print @ filename
Backup database [DatabaseName] to disk = @ filename with NOINIT, NOUNLOAD, NAME = 'backup ', NOSKIP, STATS = 10, NOFORMAT -- execute backup
Command for compressing and deleting source files:Declare @ file varchar (200)
Set @ file = 'C: \ winrar.exe a-ep-df D: \ datebasename_'character convert(char(10),getdate(,,1200000'.rar D: \ '+ convert (char (10), getdate (), 120) + '. bak' -- run the command line to compress the backup file and delete the source file winrar.
Exec master.. xp_cmdshell @ file -- execute the command
The winrar command line's compression command is: [winrar path] a [parameter] [compressed path] [file path to be compressed], which is the command format required in this article.
A is the command parameter of the winrar compressed file.
-The ep parameter does not compress the complete path of the file, that is, the specified file is compressed.
-The df parameter is used to delete the source file after compression.
The following command compresses the backup file named after the day under the root directory of the d disk to the root directory of the d disk and name it the rarfile of the day.
Generally, the winrar Program is installed in C: \ Program Files \ WinRAR by default, but the Program Files directory name contains spaces, the CMD command line does not contain spaces, so copying the program to a simple path is convenient for calling.
