Regular database backup is a very important task. This is even more important if you have encountered data loss, Server failure, or other backup files. However, if manual backup is performed each time, it is very troublesome. Is there any way to enable automatic backup? Of course. How to do it? Please come with me. The following uses SQL Server 2005 as an example. 1. Create a directory to back up files. For example, create a backup directory on the elastic drive, and then create a directory under the directory to back up the database. For example, if I have two databases (test and kr) for automatic backup, I need to create two subdirectories in backup: Test and KR to make it clearer. 2. the backupdatabase creates a full backup at every day, and then performs a differential backup every several hours. If the database is small, a file is generated every day, that is, the Bak file for full backup and the file for differential backup are the same file. If the database is large, we recommend that you generate an independent file for each differential backup. The script is as follows (the following is a file generated every day, if it is the second case, you need to modify it):/********* proc: backupdatabase use: additional information: create by: Mark create Date: 2007/6/4 exam: exec backupdatabase **********/create proc backupdatabase as begin declare @ SQL nvarchar (4000) if convert (char (5), getdate (), 108) = '00: 00 'in in set @ SQL = 'backup database [test] to disk = n' + '''' + 'e: /backup/test/test' + convert (char (8), getdate (), 112) + '. bak '+ ''' +' with noformat, noi Nit, name = n' + ''' + 'test-fullbackup '+ ''' +', Skip, norewind, nounload, stats = 10 'end else begin set @ SQL = 'backup database [test] to disk = n' + ''' + 'e: /backup/test/test' + convert (char (8), getdate (), 112) + '. bak '+ ''' +' with differential, noformat, noinit, name = n' + ''' + 'test-'+ convert (char (5 ), getdate (), 108) + '''' + ', Skip, norewind, nounload, stats = 10'end exec (@ SQL) end3. create an automatic backup job (job) 3. 1. Choose object Resource Manager> SQL Server proxy> job, right-click the job, and choose create job from the shortcut menu )...], Enter the job name in the general options. Enter "autobacktest" 3.2, select "Step", and click "CREATE (n )...], In the displayed "job step properties" dialog box, enter "autobackup" in the step name, select the database you want to back up, for example, test, and enter "Exec backupdatabase" in the command, click OK, select plan, and click Create (n )...], In the "job plan attributes" dialog box displayed, enter "everyday" in the name, select "repeated execution" in the Plan type, and select the check before "enabled. 3.4 select "Daily" in the frequency execution, and set "execution interval" to "1" day. Set the daily frequency as follows: Start Time: 0: 00: 00, End Time: 23: 59: 59. Set the execution interval to 2 hours. Select "no end date" for the duration, and click "OK ]. 3.5 The other options are secondary and can be left unspecified. Click OK ]. In this way, you can set the SQL Server Agent service to "automatic ".
