Резервное копирование и восстановление базы данных из резервной копии это два неотъемлемых процесса практически каждой информационной системы, особенно если идет непрерывное ее развитие. Резервное копирование и восстановления применяют при многих процессах, например: для обеспечения защиты данных, восстановления после сбоя, анализе данных, при обучении и демонстрациях, тестировании, разработке. При регулярной необходимости иметь актуальную копию данных в резервной\тестовой среде – это процесс требует автоматизации. В статье ниже рассмотрим 2 варианта автоматизации резервного копирования и восстановления базы данных штатными средствами MS SQL и PowerShell.

Резервное копирование и восстановление базы данных средствами T-SQL

Резервное копирование базы данных (T-SQL)
Для создания резервных копий баз данных Microsoft SQL Server  используется Команда BACKUP DATABASE.  Это одна из самых важных команд администрирования, обеспечивающая защиту данных от потерь. Она содержит немало параметров ее синтаксис показан ниже:

BACKUP DATABASE { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
[ WITH 
  { 
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ] 
    [ [ , ] { CHECKSUM | NO_CHECKSUM } ] 
    [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ] 
    [ [ , ] DESCRIPTION = { 'text' | @text_variable } ] 
    [ [ , ] DIFFERENTIAL ] 
    [ [ , ] EXPIREDATE = { date | @date_var } | RETAINDAYS = { days | @days_var } ] 
    [ [ , ] PASSWORD = { password | @password_variable } ] 
    [ [ , ] { FORMAT | NOFORMAT } ] 
    [ [ , ] { INIT | NOINIT } ] 
    [ [ , ] { NOSKIP | SKIP } ] 
    [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ] 
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] 
    [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ] 
    [ [ , ] { NOREWIND | REWIND } ] 
    [ [ , ] { NOUNLOAD | UNLOAD } ] 
    [ [ , ] STATS [ = percentage ] ] 
    [ [ , ] COPY_ONLY ]
    [ [ , ] COMPRESSION | NO_COMPRESSION ]
    [ [ , ] ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , SERVER CERTIFICATE = Encryptor_Name ) ]
  }
]

Ключевые параметры команды BACKUP DATABASE


Ниже пример самой простой команды полного резервное копирования базы данных со сжатием

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH COMPRESSION, STATS = 10;

Более сложный пример с параметрами и выводом результата

-- Параметры для резервного копирования
DECLARE @DatabaseName NVARCHAR(128) = 'AdventureWorks'; -- Имя базы данных
DECLARE @BackupPath NVARCHAR(256) = 'C:\Backup\AdventureWorks_Full.bak'; -- Путь для сохранения резервной копии

-- Команда BACKUP DATABASE
BACKUP DATABASE @DatabaseName
TO DISK = @BackupPath
WITH FORMAT, -- Форматирование нового файла резервной копии
     MEDIANAME = 'SQLServerBackups', -- Имя носителя
     NAME = 'Full Backup of ' + @DatabaseName, -- Имя набора резервных копий
     STATS = 10; -- Отображение прогресса каждые 10%

PRINT 'Резервное копирование базы данных ' + @DatabaseName + ' завершено. Файл сохранен в ' + @BackupPath;

Где,
@DatabaseName — имя базы данных, которую нужно заархивировать.
@BackupPath — путь, куда будет сохранен файл резервной копии (.bak).
WITH FORMAT — форматирует новый файл резервной копии, перезаписывая старый, если он существует.
STATS = 10 — выводит информацию о прогрессе каждые 10%.


Восстановление базы данных из резервной копии (T-SQL)
Для восстановления баз данных Microsoft SQL Server из резервных копий используется команда RESTORE DATABASE. Это важная команда для аварийного восстановления, миграции данных и создания тестовых сред. Она содержит еще больше параметров чем BACKUP DATABASE, ее синтаксис показан ниже:

RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,...n ] ]
[ WITH 
  {
    [ RECOVERY | NORECOVERY | STANDBY = {standby_file_name | @standby_file_name_var } ]
    [ , ] [ CHECKSUM | NO_CHECKSUM ]
    [ , ] [ CONTINUE_AFTER_ERROR ]
    [ , ] [ FILE = { backup_set_file_number | @backup_set_file_number_var } ]
    [ , ] [ KEEP_REPLICATION ]
    [ , ] { MOVE 'logical_file_name' TO 'operating_system_file_name' } [ ,...n ]
    [ , ] [ MEDIANAME = { media_name | @media_name_var } ]
    [ , ] [ MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ , ] [ PASSWORD = { password | @password_variable } ]
    [ , ] { NOREWIND | REWIND }
    [ , ] { NOUNLOAD | UNLOAD }
    [ , ] [ REPLACE ]
    [ , ] [ RESTART ]
    [ , ] [ RESTRICTED_USER ]
    [ , ] [ STATS [ = percentage ] ]
    [ , ] [ STOP { AT = { date_time | @date_time_var } | BEFORE = { date_time | @date_time_var } } ]
    [ , ] [ STOPATMARK = { 'mark_name' | 'lsn:lsn_number' } [ AFTER datetime ] ]
    [ , ] [ STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' } [ AFTER datetime ] ]
  }
]

Ключевые параметры команды RESTORE DATABASE


Ниже пример самой простой команды восстановления из резервной копии базы данных

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH RECOVERY, REPLACE, STATS = 5;

Более сложный скрипт восстановления из резервной копии:

-- Параметры для восстановления
DECLARE @DatabaseName NVARCHAR(128) = 'AdventureWorks'; -- Имя базы данных
DECLARE @BackupPath NVARCHAR(256) = 'C:\Backup\AdventureWorks_Full.bak'; -- Путь к резервной копии
DECLARE @DataFilePath NVARCHAR(256) = 'C:\Data\AdventureWorks.mdf'; -- Путь к файлу данных (.mdf)
DECLARE @LogFilePath NVARCHAR(256) = 'C:\Data\AdventureWorks_log.ldf'; -- Путь к файлу журнала (.ldf)

-- Проверка существования резервной копии
IF NOT EXISTS (SELECT 1 FROM sys.master_files WHERE physical_name = @BackupPath)
BEGIN
    PRINT 'Файл резервной копии не найден: ' + @BackupPath;
    RETURN;
END;

-- Остановка использования базы данных, если она активна
IF DB_ID(@DatabaseName) IS NOT NULL
BEGIN
    ALTER DATABASE @DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END;

-- Восстановление базы данных
RESTORE DATABASE @DatabaseName
FROM DISK = @BackupPath
WITH REPLACE, -- Замена существующей базы данных
     MOVE 'YourDatabaseName' TO @DataFilePath, -- Перемещение файла данных
     MOVE 'YourDatabaseName_log' TO @LogFilePath, -- Перемещение файла журнала
     STATS = 10; -- Отображение прогресса каждые 10%

-- Возврат базы данных в многопользовательский режим
ALTER DATABASE @DatabaseName SET MULTI_USER;

PRINT 'Восстановление базы данных ' + @DatabaseName + ' завершено.';

где,
@DatabaseName — имя базы данных, которую нужно восстановить.
@BackupPath — путь к файлу резервной копии (.bak).
@DataFilePath и @LogFilePath — пути к файлам данных (.mdf) и журналов (.ldf) соответственно.
WITH REPLACE — позволяет заменить существующую базу данных, если она уже существует.
MOVE — указывает новые пути для файлов данных и журналов (необходимо, если пути отличаются от исходных).
STATS = 10 — выводит информацию о прогрессе каждые 10%.