SQL Server sets automatic backup and deletion of old database files

Source: Internet
Author: User
Tags sql server management sql server management studio
Automatic Database Backup Open SQL Server Management studio and start the SQL Server proxy service (note that the SQL Server Agent Startup type is set to automatic in "Control Panel-Administrative Tools-service ). After the job is started, click "job-create job" to bring up a job attribute window. In the "General" column, you can name the job, for example, "backup data ". 1. The first step of data backup-"backing up data on the current day"
Create a new step named backup day data in the steps column, type as T-SQL ",DatabaseSelect the database you want to operate on (for example, "testdb"), and fill in the backup SQL statement in the Command window. Assume that the backup data is placed in "D: \ backup" and the backup file is named as "SQL-2009-3-26.BAK", the statement is as follows:
Declare @ filename varchar (255)
Declare @ date datetImE
Select @ date = getdate ()
Select @ filename = 'd: \ backup \ SQL-'+ Cast (datepart (yyyy, @ date) as varchar) +'-'+ Cast (datepart (mm, @ date) as varchar) + '-' + Cast (datepart (DD, @ date) as varchar) + '. bak'
Backup database [testdb] to disk = @ filename with init
Go
Select "go to next" for the advanced "operations to be performed upon success" in the step properties, so that the "backup data for the current day" step has been created.
2. Step 2 of data backup-"delete old backup"
We can set to retain only the backup data for five days, so we must delete the data backup file five days ago. In the step section of the "Backup Data" job Properties window, create the second step and name it "delete old backup ". The same type is T-SQL, fill in the Command window with the SQL statement:
Declare @ olddate datetime
Select @ olddate = getdate ()-5
Execute master. DBO. xp_delete_file 0, N 'd: \ backup ', N 'bak', @ olddate, 1
This command will delete files in. Bak or. TRN format five days ago in "D: \ backup" without specifying the file name. Because the SQL Server backup file contains the time attribute. Select exit successful job in the advanced "operations to be executed when successful" of the Step attribute, so that the second step has been created.
3. Set the job execution time in the Plan column of the Backup Data job attribute
Create a new job plan, name it "automatic daily backup and deletion", and then select the execution cycle, for example, starting at every day.
Finally, the job that saves the entire "Backup Data" will automatically back up the database and delete the old data on a daily basis.

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.