Использование скрипта PowerShell для удаления старых файлов на SQL Server

Данный материал является переводом оригинальной статьи "MSSQLTips : Using a PowerShell Script to delete old files for SQL Server".

Многие клиенты используют в SQL Server собственные хранимые процедуры или сторонние инструменты для бэкапов БД в рабочей среде, вместо использования Планов обслуживания БД. Одним из моментов, является управление числом файлов бэкапов, существующих на диске, чтобы не исчерпать всё дисковое пространство.  Есть несколько методик удаления устаревших файлов, но в данной статье мы покажем, как можно делать это через PowerShell.

Хотя есть много процессов удаления файлов, что можно использовать в SQL Server, мы покажем, как можно делать это через PowerShell.

PowerShell это мощный инструмент сценариев, позволяющий администраторам и разработчикам автоматизировать администрирование серверов и разработку приложений. Скрипты PowerShell поддерживают более сложную логику, чем скрипты Transact-SQL, давая администраторам SQL Server создавать надежные скрипты для автоматизации и администрирования.

Ниже некоторые из наиболее используемых процессов, чтобы удалять старые файлы бэкапов:

• Использование планов обслуживания
• Использование функции CLR
• Использование команды ForFiles
• Использование скрипта VBS

Здесь мы покажем два разных варианта выполнить скрипт PowerShell, который будет удалять файлы, основываясь на том, насколько старый файл из расчета в днях.

Примечание: чтобы удалить файлы из папки бэкапа, аккаунт должен иметь права на чтение, запись и удаление подпапок и файлов.

Вариант 1 - Использование SSMS / T-SQL

Главное преимущество использования скрипта PowerShell то, что можно легко запустить его изнутри SSMS, или любой хранимой процедуры и не требуется развертывания скрипта на сервере.

Для запуска скрипта PowerShell с использованием SSMS или из хранимой процедуры, должна быть включена опция XP_CMDSHELL. Можно включить ее, используя скрипт ниже.

-- Скрипт включения XP_CMDSHELL
-- Чтобы изменить дополнительные параметры.
EXEC sp_configure 'show advanced options', 1
GO
-- Чтобы обновить текущее настроенное значение для расширенных параметров.
RECONFIGURE
GO
-- Чтобы включить функцию.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Чтобы обновить текущее настроенное значение для расширенных параметров.
RECONFIGURE
GO

В приведенном далее скрипте вы должны передать значения (ниже):

• targetpath - здесь вы должны передать полный путь к файлу, который вы хотите удалить..
• olddays - удалить файлы старше числа дней. Например, если хотите удалить файлы старше 5 дней, передайте -5
• extension - расширение файлов, которые нужно удалить.

-- Скрипт для удаления старых файлов
DECLARE @pscmd varchar(1000)
DECLARE @targetpath varchar(8000)
DECLARE @olddays int
DECLARE @extension varchar(5)
DECLARE @cmdstring varchar(1000)
-- Задание значений параметрам, вы можете настроить по своим нуждам
SET @targetpath = 'D:\Dtest\'
SET @olddays = -5 -- Передайте дни с отрицательным значением 
SET @extension = 'txt'
SET @pscmd = '"& '+
'Get-ChildItem ' + Quotename(@targetpath,'''') + ' | '+
'where {$_.lastWriteTime -lt ((Get-Date).AddDays('+ CAST(@olddays as varchar) +')) 
-and ($_.Extension -match ' + QUOTENAME(@extension,'''')+ ') } | ' +
'Remove-Item  -force " '
SET @cmdstring = ' ""powershell.exe" '+ @pscmd
exec master..xp_cmdshell @cmdstring

Можно сделать задание агента по расписанию, просто вставляя код выше в шаг задания. Убедитесь, что аккаунт SQL Agent Service имеет разрешения на Чтение, Запись и Удаление в каталоге.

Вариант 2 – Создание и развертывание файла скрипта PowerShell

Скопируйте скрипт ниже и сохраните его, как "deleteoldfiles.ps1".

#----- declare parameters -----#
param([string]$targetpath=$(Throw `
"Parameter missing: -targetpath targetpath"),`
[int]$days=$(Throw "Parameter missing: `
-days days"), `
[string]$Extension=$(Throw "Parameter missing: -Extension Extension"))

$Now = Get-Date
$LastWrite = $Now.AddDays(-$days)
#----- get files based on lastwrite filter in the specified folder ---#
#----- Remove the write-host line from the below code if you want to schedule it using SQL Agent Job ---#
$Files = Get-Childitem $targetpath -Include $Extension -Recurse | Where {$_.LastWriteTime -le "$LastWrite"}
foreach ($File in $Files)
    {
    if ($File -ne $NULL)
        {
        write-host "Deleting File $File" -ForegroundColor "Red"
        Remove-Item $File.FullName | out-null
        }
    else
        {
        Write-Host "No files to delete!" -foregroundcolor "blue"
        }
    }

Необходимо передать параметры ниже в скрипт PowerShell.

• targetpath - Здесь надо передать полный путь к расположению файла, который вы хотите удалить.
• days - удалить файлы старше числа дней.
• Extension - расширение файлов, которые нужно удалить.

SL "D:\PowershellScript"
.\deleteoldfiles.ps1 -targetpath "D:\SQLMonitor\SQLMon\DelFiletest" -days "3" -Extension "*.txt"

Можно задать расписание задания SQL Agent для способа выше. Если вы хотите использовать вариант с файлом скрипта, вы должны удалить раздел Write-Host из файла скрипта.

Вариант 3 -  Задание SQL Agent со скриптом PowerShell

Можно также выполнять скрипт PowerShell напрямую из задания SQL Agent без развертывания файла скрипта. Мы немного изменили код, вместо передачи параметров, как в файле скрипта, назначаем значения.

#----enter path---#
$targetpath = "C:\jspace\"
#----enter the days---#
$days = 5
#----extension of the file to delete---#
$Extension = "*.txt"
$Now = Get-Date
$LastWrite = $Now.AddDays(-$days)
#----- get files based on lastwrite filter in the specified folder ---#
$Files = Get-Childitem $targetpath -Include $Extension -Recurse | Where 
{$_.LastWriteTime -le "$LastWrite"}
foreach ($File in $Files)
    {
    if ($File -ne $NULL)
        {
      
        Remove-Item $File.FullName | out-null
        }
    }

Можно задать расписание для этого кода напрямую в шаге задания SQL Agent без установки файла скрипта.