CREATE TABLE #DBNAME (ID int identity (DBNAME), nvarchar (100))
CREATE TABLE #BAK (ID int identity (), BAK nvarchar (120))
DECLARE @BakDir nvarchar (+) = ' dir/b/a:d G:\sqldata\mssql\backup\ '
DECLARE @DelStr nvarchar (2000)
INSERT into #DBNAME
EXEC xp_cmdshell @BakDir
DELETE #DBNAME WHERE DBNAME is NULL
DECLARE @count int
SELECT @count = count (1) from #DBNAME
DECLARE @ID int = 1
DECLARE @DBName nvarchar (100)
DECLARE @path nvarchar (200)
While @ID <= @count
BEGIN
SET @path = ' '
SET @DBName = NULL
TRUNCATE TABLE #BAK
SELECT @DBName = DBName from #DBNAME WHERE ID = @ID
IF @DBName is not NULL
BEGIN
SET @path = ' dir/b/o:-d G:\sqldata\mssql\backup\ ' + @DBName
DECLARE @CD nvarchar (+) = ' G:\sqldata\mssql\backup\ ' + @DBName + ' \ '
INSERT into #BAK
EXEC xp_cmdshell @path
DELETE #BAK WHERE BAK is NULL
SET @DelStr = ' DEL '
IF (SELECT COUNT (1) from #BAK) > 1
BEGIN
SELECT @DelStr + = @CD +bak+ "from #BAK WHERE ID <> 1
SET @DelStr = @DelStr + ') '
SELECT @DelStr = REPLACE (@DelStr, ') ', ')
PRINT @DelStr
EXEC xp_cmdshell @DelStr
END
END
SET @ID + = 1
END
DROP TABLE #DBNAME
DROP TABLE #BAK
SQL Server deletes the backup file to keep only the latest