Быстрое восстановление последнего бэкапа 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) 1375 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) 53 22.02.17 11:17 Сейчас в теме
4. Александр Монагаров (promogiantworm) 53 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, то фоновый поток, используемый для обновления статистики, соединится с базой данных и доступ к базе данных в однопользовательском режиме будет невозможен. "
Оставьте свое сообщение