SQL Server uses the job to delete outdated backup files

Enjoy the following link: http://blog.csdn.net/xieyufei/article/details/33770067
(note here The main instructions on how to set up delete outdated backup files)

First of all, SQL Server uses the job to delete outdated backup files general idea: ① back up the database file when the name to have rules, such as: "Ptm_workorder_" + The current time (month day) + "_" + current hour [emphasis: Whatever you name the specification is to take time]

② Deleting an outdated backup file The essence is to use the SQL statement to delete the specified file (which is why the backup file name takes time)

Next specific share how to delete outdated backup files: (1) This is the job, my first idea is to modify the job script changes more can, unfortunately the level is limited (the overall look is not quite understand, Meng), here to provide a simple way: Click on the job you want to modify, view properties

The command is the SQL statement that the job executes automatically (the overall structure is much clearer, and you can look at the job script if you are uncomfortable).

The command contents are as follows:

DECLARE @bakFile varchar ($), @delFile varchar ($), @str varchar, @strtest varchar (200);

SET @bakFile = ' C:\Program files\microsoftsql server\mssql10_50.mssqlserver\mssql\\backup\\rrd_workorder\pmt_ Workorder_ ' + CONVERT (varchar (+), GETDATE (), GETDATE) + + ' _ ' + CAST (DATEPART (HOUR, ()) as varchar (2)) + '. Bak ';

Backup DATABASE [Rrd_workorder] to DISK = @bakFile with Noformat, noinit, NAME = N ' rrd_workorder-full database backup ', SKIP, Norew IND, nounload, STATS = 10;

SET @delFile = ' C # ' program Files ' \ ' Microsoft SQL Server ' \mssql10_50.mssqlserver\mssql\\backup\rrd_workorder\pmt_ Workorder_ ' + CONVERT (varchar), GETDATE () -3, GETDATE) + + ' _ ' + CAST (DATEPART (HOUR, ()) as varchar (2)) + '. Bak ';

Set @str = ' del ' [email protected]

EXEC Master.dbo.xp_cmdshell @str

GO

1. The backup was removed based on the exec Master.dbo.xp_cmdshell ' del D:\test.txt ' evolution.
2. CONVERT (varchar), GETDATE ()-3, 112) Gets the time 3 days ago (not expired).
   In this encounter a pit sharing is to delete the backup file path contains a space, the solution is to add double quotation marks "" on both sides of the space, as above.

SQL Server uses the job to delete outdated backup files