Four automatic backup methods for mssqlserver databases

Method 1: Job

Steps for automatic backup in SQL Server 2005 database:

1. Open the SQL Server Management Studio http://www.111cn.net

2. Start the SQL Server proxy

3. Click Job> create job.

4. Enter the job name in "regular"

5. Create step, type select T-SQL, enter the following statement in the following command

The code is as follows:	Copy code
DECLARE @ strPath NVARCHAR (200) Set @ strPath = convert (NVARCHAR (19), getdate (), 120) Set @ strPath = REPLACE (@ strPath ,':','.') Set @ strPath = 'F: bak' + 'hpxx' + @ strPath + '. Bak' Backup database [hpxx] to disk = @ strPath with noinit, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT

(D: change the bak to your backup path, and change databasename to the name of the database you want to back up)

6. Add a plan and set the frequency and time.

OK.

 
Method 2:
SQL SERVER 2005 automatic backup maintenance plan
In SQL Server 2005, you can use maintenance plans to automatically back up databases, reducing the workload of database administrators. The usage is as follows:

(1) start SQL server Management Studio and select Manage> maintenance plan in the object Resource Manager window.

 

2) right-click maintenance plan and choose maintenance plan wizard from the shortcut menu.

 

In the displayed dialog box, click next.

 

3) the select target server dialog box is displayed, as shown in the figure. In the name text box, enter the name of the maintenance plan. In the description text box, enter the description of the maintenance plan; in the server text box, enter the name of the server you want to use. Finally, select the correct ID card and click next.

 

(4) The Select maintenance task dialog box is displayed, as shown in the following figure.

You can select a variety of maintenance tasks: Check database integrity, shrink the database, regenerate or organize indexes, update statistics, clear historical records, and execute SQL

Server proxy job, backup database, etc. In this example, select the backup database (complete) check box. The settings for other maintenance tasks are similar.

 

(5) click Next. The select maintenance task sequence dialog box is displayed, as shown in the following figure. If multiple maintenance tasks exist, you can click the move up and move down buttons to set the order of maintenance tasks. After the settings are complete, click next.

 

(6) The define task dialog box is displayed, as shown in the following figure. Under Database

Select the name of the database to be backed up from the drop-down list box. In the backup components area, select backup database or backup database file; in the [target] area, you can add backup files and devices, and set whether to append backup data to the backup files. Click next after setting.

 

(7) The Select plan attribute dialog box is displayed, as shown in the following figure. Click change.

 

8) the create job plan dialog box is displayed, as shown in the figure. In this dialog box, you can set the time and frequency of backing up the database. After the setting is complete, click OK to return to the following dialog box, click next.

 

(9) the "select report option" dialog box is displayed, as shown in figure 18.24. In this dialog box, you can choose to manage the maintenance plan report, which can be written to a file, you can also email the database administrator. Click next after setting.

 

10) The Complete Wizard dialog box is displayed, as shown in the following figure. Click finish to create the maintenance plan.

 

11) after creating the maintenance plan, check whether the SQL server proxy is started. In the object Resource Manager window, right-click SQL

Server proxy, and select start in the shortcut menu that appears.

If the backup fails, check whether the SQL Server2005 intergration Services service is not installed. You must first import the job.

Method 3

The creation process is as follows:

A. open SQL Server Management Studio, connect to your instance, and open "object Resource Manager, press F8 to expand the instance -- manage -- maintenance plan -- right-click "maintenance plan" -- create maintenance plan -- enter the maintenance plan name -- the window for creating the maintenance plan will appear.

B. In the left toolbox (if not, press Ctrl + Alt + X), drag the "backup database task" to the yellow area in the middle.
   
C. double-click this task to display the design attribute window. In the database, select the database you want to back up, set the backup to there, and set the backup file name, after the design, click "OK", you can also click "T-SQL" to see what statements SQL will roughly use to deal with your needs.

D. Click the "..." button after "plan" to set the execution time plan.

Method 4

1. Create a job
2. Add general
3. Add step, select T-SQL enter the following statement (databasename change to your own database name. D: bak change to your own backup path)

The code is as follows:	Copy code
DECLARE @ strPath NVARCHAR (200) Set @ strPath = convert (NVARCHAR (19), getdate (), 120) Set @ strPath = REPLACE (@ strPath ,':','.') Set @ strPath = 'd: bak '+ @ strPath +'. Bak' Backup database [databasename] to disk = @ strPath with noinit, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT

 

4. Add a plan and set the frequency and time.
5. Remember to enable the SQL Server Agent Service