Automatic Backup of SQL Server databases

I. Use a job

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 step, type select T-SQL, enter the following statement in the following command

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

(Change D: \ 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.

The above information from Baidu space: http://hi.baidu.com/_zhangchengfeng/blog/item/5c13c118a96657b14aedbc49.html

II. Automatic Backup Using SQL2005 maintenance plan(Not recommended)

The SQL2005 maintenance plan contains the backup database task, but it is not automatically named by date, which is inconvenient. The following method is to solve the problem by using the stored procedure.

To use the SQL2005 maintenance plan function, you must first confirm that the SQL Server Agent service is in the starting status, then open SQL Server Management Studio, and expand the "Management" in the object Resource Manager ", right-click the maintenance plan and choose new maintenance plan. Name the maintenance plan, for example, TestDB_Backup, select the default sub-plan "Subplan_1" and click the sub-plan Settings button above to set the sub-plan execution mode and time:

The setting page appears. The setting is executed once every day,

Click OK and save it. Then, create a query page and create a stored procedure. The Code is as follows:

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [BackupDatabase] (@ FolderPath varchar (500 ))
As
DECLARE @ FullPath varchar (1000)
Set @ FullPath = @ FolderPath + 'testdb _ '+ convert (VARCHAR (4), year (getdate () + right ('0' + convert (VARCHAR (2 ), MONTH (getdate (), 2) + right ('0' + convert (VARCHAR (2), DAY (getdate (), 2) + '. bak'
Backup database [TestDB] to disk = @ FullPath WITH INIT
Return

 

After setting, switch back to the maintenance plan settings page and drag a execute T-SQL statement task in the toolbox on the left ":

 

Double-click execute T-SQL statement task after dragging, enter the statement that executes the stored procedure to complete the daily automatic backup work, the Code is as follows:

Use [TestDB]
Exec BackupDatabase 'e: \ SqlData \ TestDB \'

Click OK and save the maintenance plan. To ensure that the maintenance plan does not have any problems, run the preceding statement to test the plan.

This method is similar to SQL2005 in SQL2000.

To sum up, the second method is too cumbersome, so it is not recommended.