The Method of Setting up Periodic Automatic Backup for SQL Server 2008 Database


1, explain

In daily work, we use the maintenance plan of SQL SQL SERVER 2008 to backup the database regularly and automatically. On the one hand, we can backup the database to ensure data security, on the other hand, we can reduce the burden on maintenance personnel. SQL Server 2008 itself has regular automatic backup function, we can achieve very simple and efficient automatic backup function only through simple configuration.

Let’s go over the details.

2. Open the SQL Server proxy service

To realize automatic backup function, first of all, ensure that the “SQL Server (proxy) service” of SQL Server has been opened.

If you do not see this “SQL Server Agent” node, you can open it as follows

Find “Microsoft SQL Server 2008 R2” in the start menu, and then select “SQL Server Configuration Manager” in “Configuration Tools”

Select “SQL Server Service” in the “Sql Server Configuration Manager” window, then right-click “SQL Server Agent” on the right, and select “Start”

3. Establish backup plan

Suppose there is now a database called “test”, which is backed up regularly.

Right-click “Maintenance Plan” under the “Management” node, and then select “Maintenance Plan Wizard”, as shown below.

If the following error window prompt pops up at this time

Solve the problem as follows

“Start”= “Run”. Open the console window.

If it’s a 32-bit operating system, run the following commands

c:\windows\system32\regsvr32 “C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dts.dll”

If it’s a 64-bit operating system, run the following commands

c:\windows\syswow64\regsvr32 “C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dts.dll”

Then close Microsoft SQL Server Management Studio and open it again.

If there are no errors, the Maintenance Plan Wizard window appears

Click the Next button

Enter the name of the maintenance plan, and then click “Change…” “Set the time for backing up the database, as shown below

When the execution time is set, click OK, go back to the original window, and then click Next.

Check Backup Database (Complete) in the new window, and then click Next.

This window specifies the order in which tasks are executed. Since we only set one task, we don’t need to worry about it. We just “go next”.

Select the database “test” we want to backup

It’s better to check “Create subdirectories for each database” and “Verify backup integrity”, regardless of expiration time and compression, and then click “Next”

This step is mainly how to handle the backup log. We can put it in the same directory as backup or use the default directory. If we put it in the backup file directory, it will be more convenient to check. Click Next.

The final point of “finish” will complete the automatic backup settings.

Finally, there will be our maintenance plan settings window, at which time the backup plan has not been implemented.

The backup plan can be seen in the “Maintenance Plan” node under “Management” and the “Job” node under “SQL Server Agent”.

4. Implement backup plan immediately

We can also execute our backup plan without waiting for the scheduled time, as follows

In “Maintenance Plan”, find the backup plan named “Auto Backup Database” which we just established. Right-click “Execute”

When the execution is successful, the original backup directory D: Program Files Microsoft SQL Server MSSQL 10_50. MSSQL MSSQL Backup sees the folder named after the backup data, and after opening the folder, you can see the backup file.

Depending on the settings, a backup of each database is placed in a folder named after the database.

The figure above shows the backup file generated after the backup plan is executed.

If the backup process report log is configured by default, the backup log can be seen in the log folder of the database. As shown below, each backup will produce a log of a text file.

You can see the backup log when you open it

If the execution fails and the error message is as follows, the SQL Server proxy service is not opened.

5. Restore Backup

Right-click on the database to be restored (in this case, “test”), Task= “Restore=” database…

Selection of restore point according to time

Backup takes time as the restore point, and can be restored to different backups by choosing different “target time points”. If we modify “target time points”, the name of the selected backup set for restoring in the lower part will be changed accordingly. The following figure is a dialog box for changing the target time point.

Click “Options” and check “Overwrite Existing Databases” and “Restrict Access to Restored Databases”

Point “OK”

6. Modify backup plan

Under Maintenance Plan, right-click on the new “Auto Backup Database” and select “Modify”, as shown below.

In the open new tab, you can modify the backup behavior, backup time, backup log storage location, etc.

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]