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.
