SQL SERVER settings to automatically back up and delete old database files

Open SQL Server MANAGEMENT STUDIO and start the SQL Server Agent service (note that the startup type of SQL Server Agent is automatically set in Control Panel-Administrative Tools-services). Click "Job-New job" after launch, pop up a job Properties window, in the "General" column can be named for the job, assume that "backup data." First step of backing up data--"Back up the Day data"
In the "Steps" section, create a new step named "Back-of-the-day Data", type "T-SQL",The database selects the database you want to manipulate (assuming "TESTDB"), and the command window fills in the backup SQL statement. Assuming that the backup data is placed in "D:\BACKUP", the backup file is named "Sql-2009-3-26." BAK ", then 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
In the advanced "actions to perform on success" option in the Step properties, select Go to Next, so the steps to "back up the Day data" are already established.
Second step of backing up data--"Delete old backup"
We can set up only 5 days of backup data, then you must delete the data backup file 5 days ago. In the steps section of the Backup Data Job Properties window, create a second step named "Delete old backup." The same type is "T-SQL", and the command window fills in 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 "D:\BACKUP" 5 days ago. Bak or. trn formatted file without specifying what the file name is. Because the backup file for SQL Server contains the time attribute inside. In the step properties of advanced "action to perform when successful" select "Exit report successful Job" so that the second step has been established.
Third, the backup data job properties of the Plan section, set the execution time of the job
Create a new job schedule named daily automatic backup and delete, and then select the period of execution, such as starting 1 o'clock in the morning every day.
The last job to save the entire backup data is to automatically back up the database and delete the old data on a daily basis.

SQL SERVER settings to automatically back up and delete old database files