SQL server database backup and replication (4): method 1 for automatic SQL SERVER backup

We usually create BackupIn SQL Server, We can achieve this through the following method:

Steps for automatic backup in SQL Server 2005 database:

1. Open SQL Server Management Studio

2. Start the SQL Server proxy

3. Click job> create job.

4. Enter the job name in "regular"

5. Create a step, select the T-SQL type, and enter the following statement in the following command (the red part should be changed according to your actual situation, D: sql2005ack changed to your own backup path, change sq_test to the name of the database to be backed up)

　　

If the SQL Server Agent is not started, start it first, and create a new job named "MyDbFull 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:

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:

Figure 1 create a job to completely back up the database and then start 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:

Figure 2 create a job to compress the data, and then we can see the operation step dialog box, 3:

Figure 3 steps for full data backup

For comparison, pay attention to two points. The first one is the display of the "successful" column in step 1. When the column is successful, go to the next step, "Upon failure": exit after failure upon failure; Step 2 "upon success": exit after success upon success; "upon failure": exit after failure upon 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:

Figure 4 Create scheduling so that the entire full backup of the database can be established.

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: execute the following command 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 + '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: Figure 5 Comparison Between Differential backup Job Scheduling and full backup Job Scheduling. here we can see that we chose three o'clock every night except Sunday, this scheduling is automatically executed. 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;