SQL statement used to delete an SQL Server backup file

After the project is launched, an automatic backup is created for each database on the database server to ensure the security of customer data. A new backup file is generated every day and stored in the backup folder, in addition, a sub-folder named by the database location is created in the folder, and the backup file is placed in this folder. After a long time, the server space is insufficient, originally, an OA database was very large, with 4 GB or so, which would consume about 4 GB of space every day. Therefore, we had to delete the previously backed up files every day for a long time, finally, this statement is obtained:

Exec xp_cmdshell 'forfiles/p c: backup/S/M *. bak/D-30/C "CMD/C del/Q/f @ file "'

/D-30 indicates the file 30 days ago,

/S indicates recursive subfolders search

*. Bak indicates querying all Bak-terminated file names

C: backup indicates that the root directory to be searched is c: \ backup.

 

Note that you must enable xp_cmdshell in the SQL Server peripheral configurator.