To create a database backup job by using SQL Server Management Studio

In the project, you often need to back up the database, if you can only click on the button ("Start Backup Database" button), and then nothing, the database is automatically backed up, or the database on the server automatically back up once a time, that's how good. SQL Server jobs can meet this requirement!

SQL Server jobs can execute the specified SQL script at the required time, and below I use the job backup Northwind data as an example of how to create a job backup database with SQL Server R2.

First step: Create a SQL Server job

Step two: Set job execution steps

The SQL script for backing up the database is as follows:

--define variables to assign the QL script of the backup database to the variableDeclare @SqlBackupDataBase  as nvarchar( +)Set @SqlBackupDataBase=N'BACKUP DATABASE Northwind to DISK ="'D:\TestBackupJob\Northwind-'+CONVERT(varchar( One),GETDATE(), the)+REPLACE(CONVERT(varchar( A),GETDATE(),108),':',"')+'. bak" "--backup file format: Northwind-20150531323410.bakexecsp_executesql@SqlBackupDataBase --call system stored procedure, execute SQL

View Code

Note: The third parameter of the CONVERT () function is the time style ID; Replace the colon in time with the Replace () function (because the file name in Windows cannot contain the character)

Step three: Set up the job execution plan

At last

Perform the job manually, verify that the database was backed up successfully, and eject the following error:

The error is obvious: The SQL Server Agent service is not started,

Start this service:

To manually execute the job again, back up the database successfully:

Check the disk path of the backup database and find that the Northwind database is automatically backed up every 30 seconds.

Such as:

The above is the use of SQL Server database creation job to automatically back up the database, very practical.

To create a database backup job by using SQL Server Management Studio