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

Source: Internet
Author: User
Tags management studio sql server management 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.