Главная
База знаний
Microsoft SQL Server
Резервное копирование и восстановление
Авто-восстановление MSSQL-базы из последнего бэкапа

Авто-восстановление MSSQL-базы из последнего бэкапа

Это вариант на чистом T-SQL: скрипт кладётся в задание SQL Server Agent или в Maintenance Plan, PowerShell не нужен. Ту же задачу можно решить средствами PowerShell — см. Восстановление базы по расписанию из бэкапа — PowerShell.

Инструкция для SQL Server Agent / Maintenance Plan, который по расписанию восстанавливает тестовую базу из самого свежего .bak файла.

Полный код скрипта

DECLARE 
    -- Имя базы данных, которую нужно восстановить
    @databaseName       SYSNAME        = N'test_restore',

    -- Папка, где лежат файлы резервных копий
    @backupDir          NVARCHAR(260)  = N'H:\SQL\SQL_bak\base1c_test',

    -- Маска файлов резервных копий
    @backupMask         NVARCHAR(50)   = N'*.bak',

    -- Папка, куда будет восстановлен файл данных MDF
    @dataDir            NVARCHAR(260)  = N'D:\SQL\SQL_data',

    -- Папка, куда будет восстановлен файл журнала LDF
    @logDir             NVARCHAR(260)  = N'D:\SQL\SQL_log',

    -- Логическое имя файла данных внутри бэкапа
    @dataLogicalName    SYSNAME        = N'unf_test_exchange',

    -- Логическое имя файла журнала внутри бэкапа
    @logLogicalName     SYSNAME        = N'unf_test_exchange_log',

    -- Полный путь к будущему MDF-файлу
    @dataFileName       NVARCHAR(260),

    -- Полный путь к будущему LDF-файлу
    @logFileName        NVARCHAR(260),

    -- Полный путь к найденному самому свежему файлу бэкапа
    @backupFile         NVARCHAR(4000),

    -- Команда DIR для поиска последнего файла бэкапа
    @cmd                NVARCHAR(4000),

    -- Динамический SQL-запрос для восстановления базы
    @restoreQuery       NVARCHAR(MAX);

SET @dataFileName = @dataDir + N'\' + @databaseName + N'.mdf';
SET @logFileName  = @logDir  + N'\' + @databaseName + N'_log.ldf';

CREATE TABLE #FileList
(
    FileName NVARCHAR(4000)
);

SET @cmd = N'DIR "' + @backupDir + N'\' + @backupMask + N'" /B /O-D';

INSERT INTO #FileList
EXEC xp_cmdshell @cmd;

SELECT TOP (1)
    @backupFile = @backupDir + N'\' + FileName
FROM #FileList
WHERE FileName IS NOT NULL
  AND FileName NOT LIKE N'%File Not Found%'
  AND FileName NOT LIKE N'%Файл не найден%';

DROP TABLE #FileList;

IF @backupFile IS NULL
BEGIN
    THROW 50001, N'Не найден файл бэкапа для восстановления.', 1;
END;

SET @restoreQuery = N'
' + CASE 
        WHEN DB_ID(@databaseName) IS NOT NULL THEN
N'ALTER DATABASE ' + QUOTENAME(@databaseName) + N' 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
        ELSE N''
    END + N'

RESTORE DATABASE ' + QUOTENAME(@databaseName) + N'
FROM DISK = N''' + REPLACE(@backupFile, '''', '''''') + N'''
WITH 
    REPLACE,
    MOVE N''' + REPLACE(@dataLogicalName, '''', '''''') + N''' TO N''' + REPLACE(@dataFileName, '''', '''''') + N''',
    MOVE N''' + REPLACE(@logLogicalName, '''', '''''') + N''' TO N''' + REPLACE(@logFileName, '''', '''''') + N''';

ALTER DATABASE ' + QUOTENAME(@databaseName) + N' 
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
';

BEGIN TRY
    PRINT @backupFile;
    PRINT @restoreQuery;

    EXEC sp_executesql @restoreQuery;
END TRY
BEGIN CATCH
    IF DB_ID(@databaseName) IS NOT NULL
    BEGIN
        DECLARE @multiUserQuery NVARCHAR(MAX);

        SET @multiUserQuery = N'
ALTER DATABASE ' + QUOTENAME(@databaseName) + N' 
SET MULTI_USER WITH ROLLBACK IMMEDIATE;';

        EXEC sp_executesql @multiUserQuery;
    END;

    THROW;
END CATCH;

1. Проверить logical names в бэкапе

Перед настройкой скрипта нужно узнать логические имена файлов внутри резервной копии.

RESTORE FILELISTONLY
FROM DISK = N'H:\SQL\SQL_bak\base1c_test\backup_file.bak';

В результате нужны значения из колонки LogicalName:

для файла данных, обычно строка с типом D;
для файла журнала, обычно строка с типом L.

Эти значения нужно указать в переменных:

@dataLogicalName = N'unf_test_exchange',
@logLogicalName  = N'unf_test_exchange_log',

2. Проверить доступ SQL Server Agent к папкам

Job выполняется от имени службы SQL Server Agent, например:

NT Service\SQLSERVERAGENT

У этой учетной записи должен быть доступ:

на чтение к папке с .bak файлами;
на запись к папке MDF-файлов;
на запись к папке LDF-файлов.

Пример папок из скрипта:

H:\SQL\SQL_bak\base1c_test
D:\SQL\SQL_data
D:\SQL\SQL_log

3. Проверить xp_cmdshell

Скрипт использует xp_cmdshell, чтобы найти самый свежий .bak файл через команду DIR.

Проверить, включен ли xp_cmdshell:

EXEC sp_configure 'xp_cmdshell';

Если нужно включить:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

4. Настроить переменные скрипта

В начале скрипта нужно проверить и при необходимости заменить:

@databaseName       = N'test_restore',
@backupDir          = N'H:\SQL\SQL_bak\base1c_test',
@backupMask         = N'*.bak',
@dataDir            = N'D:\SQL\SQL_data',
@logDir             = N'D:\SQL\SQL_log',
@dataLogicalName    = N'unf_test_exchange',
@logLogicalName     = N'unf_test_exchange_log',

Назначение переменных:

@databaseName - имя базы, которую нужно восстановить;
@backupDir - папка с резервными копиями;
@backupMask - маска файлов бэкапа;
@dataDir - папка для MDF-файла;
@logDir - папка для LDF-файла;
@dataLogicalName - логическое имя файла данных внутри бэкапа;
@logLogicalName - логическое имя файла журнала внутри бэкапа.

5. Создать или изменить Job / Maintenance Plan

Вариант через Maintenance Plan:

Открыть SQL Server Management Studio.
Перейти в Management -> Maintenance Plans.
Создать новый план или открыть существующий.
Добавить задачу Execute T-SQL Statement Task.
Вставить скрипт восстановления.
Настроить расписание.
Сохранить план.

6. Проверить запуск вручную

Перед включением расписания лучше запустить job вручную.

После запуска проверить:

job завершился успешно;
база появилась или обновилась;
база находится в режиме MULTI_USER;
MDF и LDF файлы лежат в нужных папках;
в истории job нет ошибок ALTER DATABASE, RESTORE DATABASE или доступа к файлам.

Проверка режима базы:

SELECT 
    name,
    user_access_desc,
    state_desc
FROM sys.databases
WHERE name = N'test_restore';

7. Частые ошибки

Неверное имя базы

Если указать не то значение в @databaseName, скрипт восстановит бэкап в другую базу.

Проверить:

@databaseName = N'test_restore'

Неверные logical names

Если logical names не совпадают с именами внутри бэкапа, RESTORE DATABASE завершится ошибкой.

Проверить через:

RESTORE FILELISTONLY
FROM DISK = N'H:\SQL\SQL_bak\base1c_test\backup_file.bak';

Нет доступа к файлам

Если SQL Server Agent не видит бэкап или не может создать MDF/LDF, нужно проверить права учетной записи службы SQL Server Agent.

База осталась в SINGLE_USER

Если восстановление прервалось, база может остаться в SINGLE_USER.

Вернуть вручную:

ALTER DATABASE [test_restore]
SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Связанные статьи

Восстановление базы по расписанию из бэкапа — PowerShell — та же задача через PowerShell + T-SQL: удобнее, когда нужно переименовать логические файлы и сменить владельца базы;
Создание планов резервного копирования и обслуживания MS SQL — откуда берутся .bak-файлы, из которых восстанавливаемся;
Восстановление базы данных через SSMS — разовое восстановление вручную, без расписания.

Авто-восстановление MSSQL-базы из последнего бэкапа

Полный код скрипта

1. Проверить logical names в бэкапе

2. Проверить доступ SQL Server Agent к папкам

3. Проверить xp_cmdshell

4. Настроить переменные скрипта

5. Создать или изменить Job / Maintenance Plan

6. Проверить запуск вручную

7. Частые ошибки

Неверное имя базы

Неверные logical names

Нет доступа к файлам

База осталась в SINGLE_USER

Связанные статьи

Расчёт стоимости

Спасибо за оставленную заявку!

Заявка на обслуживание

Спасибо за оставленную заявку!

Заказать звонок

Спасибо за оставленную заявку!