-
Title
xp_delete_file deletes native SQL Server backup files but not LiteSpeed files -
Description
xp_delete_file deletes native SQL Server backup files but not LiteSpeed files, only xp_slssqlmaint deletes LiteSpeed backup files.
-
Resolution
This is a difference in the function of the native SQL Server task vs. the LiteSpeed maintenance plan, it is not a defect.
Example:
This native tasks script would delete all native files, in the D:\Backup folder, with the BAK extension created before December 1, 2008
exec master.dbo.xp_delete_file 0, ND:\Backup\, NBAK, 2008-12-01T10:57:26
The LiteSpeed task includes the parameter -DelBkUps for use with slssqlmaint:
execute master..xp_slssqlmaint N-D Northwind -BkUpMedia DISK -DelBkUps 1HOURS
(Note-only backup files for the databases included in the maintenance plan will be deleted)1. The two scripts below will only delete LiteSpeed-created files – these are part of the “Backup Task” in Maintenance Plan
The LiteSpeed for SQL Server syntax for Full back up databases:
execute master.dbo.xp_slssqlmaint N'-D "AdventureWorksDW" -BkUpMedia DISK -DelBkUps 1DAYS -BkUpDB
-UseDefDir -CrBkSubDir -BkExt "bak" -Logging 0 -CompressionLevel 2 -Default "%D_backup_%DATE%%T%H%M.bak"
-NOOPTOLR -Exclude Deleted Offline LogShippng '
The LiteSpeed for SQL Server syntax for Log back up databases:
execute master.dbo.xp_slssqlmaint N'-D "AdventureWorksDW" -BkUpMedia DISK -DelBkUps 1DAYS -BkUpLog
-UseDefDir -CrBkSubDir -BkExt "trn" -Logging 0 -CompressionLevel 2 -Default "%D_backup_%DATE%%T%H%M.bak"
-NOOPTOLR -Exclude Deleted Offline LogShippng '
2. To delete old LiteSpeed backup files – this one check for dependencies Full + Diff + Tlog. Check the LiteSpeed Help file under "xp_slsSmartCleanup" for examples & syntax
Delete disk full and differential backups older than 28 days, delete log backups older than 2 days, allow deletions of the copy-only backups:
exec master.dbo.xp_slsSmartCleanup
@database = '"AdventureWorksDW'
, @BackupRetainDays = 28
, @LogRetainDays = 2
3. This LiteSpeed script deletes ALL files in a folder with a particular extension (without any Native vs. LiteSpeed logic) – It’s the CleanUp Maintenance Plan task in the GUI:
execute master.dbo.xp_slssqlmaint N'-MAINTDEL -DelDeviceType DISK -DELTYPE FileAny -DELUSEAGE -DELUNIT "2" -DELUNITTYPE "DAYS" -DELEXTENSION "bak" -DELFOLDER "C:\Backup\2005\" '