IE2017

Быстрое восстановление последнего бэкапа MS SQL

Администрирование - Архивирование (backup)

Скрипт T-SQL находит последний (по дате) бэкап БД в папке и разворачивает, куда укажем

Очень удобно поднимать последный бэкап из папки с бэкапами. Использую его в шедулере агента SQL, чтобы паралельно иметь актуальную тестовую БД и не нажимать кучу кнопок. 

Хорошо если БД не очень большая, однако если размеры не позволят быстро копировать/восстанавливать полную БД, то нужно дополнить/изменить запрос для восстановления разностной резервной копии.

Как создать задание агента MS SQL тут.


-- Удаляем процессы пользователей восстанавливаемой БД

DECLARE @sql AS varchar(20), @spid AS int
SELECT @spid = min(spid)  
FROM master..sysprocesses  
WHERE dbid = db_id('TEST_TEST') 
AND spid != @@spid    

WHILE (@spid IS NOT NULL)
BEGIN
    PRINT 'Killing process ' + cast(@spid AS varchar) + ' ...'
    SET @sql = 'kill ' + cast(@spid AS varchar)
    EXEC (@sql)

    SELECT 
        @spid = min(spid)  
    FROM 
        master..sysprocesses  
    WHERE 
        dbid = db_id('TEST_TEST') 
        AND spid != @@spid
END 

PRINT 'Process completed...'

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
      DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);

-- Находим последний бэкап 

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree 'F:\BackupDB\DB',1,1;

DECLARE @LastBackupFile nvarchar(512)
  , @LastBackupPath nvarchar(512);

SELECT top 1 @LastBackupFile = #DirectoryTree.subdirectory FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.BAK'
ORDER BY id DESC;
SELECT @LastBackupPath = 'F:\BackupDB\DB\' + @LastBackupFile;
PRINT 'Founded last flile:' + @LastBackupFile;

-- Восстанавливаем бэкап в указанную БД
 
USE [master] 
RESTORE DATABASE [TEST_TEST] FROM  DISK = @LastBackupPath WITH  FILE = 1,  
MOVE N'DB_name' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TEST_TEST.mdf',  
MOVE N'DB_name_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TEST_TEST_log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5
GO

См. также

Комментарии
1. Armando Armando (Armando) 1378 21.02.17 22:50 Сейчас в теме
Вместо удаления процессов можно сделать короче:

в начале:
ALTER DATABASE [TEST_TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

в конце:
ALTER DATABASE [TEST_TEST] SET MULTI_USER
DrAku1a; promogiantworm; +2 Ответить 3
2. Канат Джумадылов (Fox-trot) 52 22.02.17 09:56 Сейчас в теме
странно что у вас и бекапы не хранятся в папке C:\Program Files\... ;)
3. Александр Монагаров (promogiantworm) 55 22.02.17 11:17 Сейчас в теме
4. Александр Монагаров (promogiantworm) 55 22.02.17 11:20 Сейчас в теме
(2)это же пример, путь может быть какой угодно
5. klom klom (klom) 22.02.17 11:21 Сейчас в теме
(1) согласен, проще и нагляднее
6. Arhal (arhal) 09.03.17 14:41 Сейчас в теме
(1) Если не отключить в свойствах базы Auto update statistics asynchronously, то однопользовательский режим установиться, но в любой момент может быть отменен фоновым потоком. По факту, задание прекращается с ошибкой.
Информация с msdn.microsoft.com:
"Перед заданием параметра SINGLE_USER проверьте, чтобы параметру AUTO_UPDATE_STATISTICS_ASYNC было присвоено значение OFF. Если этот параметр имеет значение ON, то фоновый поток, используемый для обновления статистики, соединится с базой данных и доступ к базе данных в однопользовательском режиме будет невозможен. "
7. Npoen Dzogchen (Dansur) 260 29.06.17 08:21 Сейчас в теме
зачем убивать процессы? если перевод в SINGLE MODE делает то же самое но однй командой?

кстати если нет файла бэкапа а нужна максиимально свежая копия на этому же сервере то можно например так:

BACKUP DATABASE [__имяРабочейБазы__] TO DISK = N'F:\Backup\имяРабочейБазы_Autobackup.bak' WITH NOFORMAT, INIT, NAME = N'__имяРабочейБазы__-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

USE [master]
ALT ER DATABASE [__имяБазыСвежейКопии__] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [__имяБазыСвежейКопии__] FROM DISK = N'F:\Backup\имяРабочейБазы_Autobackup.bak' WITH FILE = 1, MOVE N'__имяРабочейБазы__' TO N'F:\DataBases\__имяБазыСвежейКопии__.mdf', MOVE N'__имяРабочейБазы__' TO N'F:\LogFiles\__имяБазыСвежейКопии__.ldf', NOUNLOAD, REPLACE, STATS = 5
ALT ER DATABASE [__имяБазыСвежейКопии__] SET MULTI_USER

GO
8. Александр Монагаров (promogiantworm) 55 29.06.17 11:56 Сейчас в теме
(7) Можно и не убивать, это Вам решать, тут пример как с помощью языка T-SQL автоматически найти по дате какой файл бэкапа последний и подсунуть его, а не указывать конкретно какое-то имя файла.
9. Иван Филимонов (DarkAn) 360 18.07.17 14:28 Сейчас в теме
А чем не устраивает стандартный механизм? Через мастер восстановления БД? Он тебе подтянет еще и разностные копии и копии ЖТ.
10. Александр Монагаров (promogiantworm) 55 18.07.17 15:33 Сейчас в теме
Оставьте свое сообщение