Резервное копирование SQL Server в общую папку

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

Во-первых, нужно убедиться, что учетная запись, из под которой стартует SQL Server Agent, имеет доступ в локальную папку, куда Вы делаете первичную копию, и в общую сетевую папку.

Мой вариант работает через Backup Device. Можно резервировать БД сразу в файл, но, если в промежутках между копированием БД, Вы в тот же файл делаете копирование Transaction Log, то проще через Backup Device.

Представим себе, что имя БД - DatabaseName, имя устройства резервного копирования - BackupDeviceName, локальный диск для резервного копирования - R:, папка на нем для локального резервного копирования - BackupDir, путь для удаленного резервного копирования - \\BackupServer\BackupDir\

Рекомендую именовать BackupDeviceName подобно DatabaseName и использовать разные BackupDir для разных БД.

Распишу основной SQL Agent Job по шагам:

1. Удаление старого Backup Device (Transact SQL script)

EXEC master.dbo.sp_dropdevice @logicalname = N'BackupDeviceName'

2. Создание нового Backup Device (Transact SQL script)

DECLARE @MakeBackupDevSQLQuery NVARCHAR(MAX);
SET @MakeBackupDevSQLQuery = N'EXEC master.dbo.sp_addumpdevice
@devtype = N''disk'',
@logicalname = N''BackupDeviceName'',
@physicalname = N''R:\BackupDir\DatabaseName.'+REPLACE(CONVERT(NVARCHAR(MAX), current_timestamp, 20),':','')+'.bak'''
EXEC sp_executesql @MakeBackupDevSQLQuery

3. Резервное копирование БД (Transact SQL script)

BACKUP DATABASE [DatabaseName] TO [BackupDeviceName]

4. Удаление старых локальных резервных копий (PowerShell)

get-item "R:\BackupDir\*" | Where-Object {$_.lastwritetime -le (get-date).AddDays(-10)} | ForEach-Object {del $_}

5. Сетевое копирование и удаление старых сетевых копий (PowerShell)

R:
cd BackupDir
dir |? { !( test-path \\BackupServer\BackupDir\$_ ) } | ForEach { cp $_ \\BackupServer\BackupDir }
dir \\BackupServer\BackupDir |? {$_.CreationTime -lt (get-date).AddDays(-10)} | del