/*
Скрипт для восстановления базы из бэкапов, сделанных с помощью решения Ola Hallengren (https://ola.hallengren.com)
Сделано по мотивам http://jason-carter.net/professional/restore-script-from-backup-directory-modified.html
и http://www.codepimp.org/2015/04/automated-restore-using-ola-hallengrens-sql-server-backup/
На github есть готовые решения для восстановления из ola-бэкапов, это просто еще один вариант со своими особенностями:
- восстановление баз на любом (не исходном) сервере
- восстановление в указанный каталог
- восстановление на момент времени
- восстановление не только от последнего полного бэкапа, то есть можно указать любую дату в прошлом, были бы бэкапы
- скрипт умеет обрывать соединения с конечной базой перед восстановлением
- скрипт сделан своими руками для тренировки :)

Для работы требуется разрешить xp_cmdshell:
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE;  
GO
*/

SET NOCOUNT ON;
		
---Set stored procedure variables
DECLARE @SourceDBName sysname = 'БизнесШкола' -- Имя исходной базы
DECLARE @DestinationDBName sysname -- Имя конечной базы. Если не задано, то будет @SourceDBName + '_'. Пример: 'НоваяБаза'
DECLARE @RootPath NVARCHAR(500) = '\\srv-vm3\SQL Backup' -- Каталог с бэкапами. Можно указывать общий путь (как в скриптах ola), либо полный путь для базы. Примеры: '\\КаталогБэкапов' или '\\КаталогБэкапов\ИмяСервера\ИмяБазы'
DECLARE @ServerName NVARCHAR(100) -- Если @RootPath указан без имени сервера и базы, то имя сервера используется для вычисления полного пути к бэкапам. Если не указан, то берется имя текущего сервера
DECLARE @RestoreDateTime NVARCHAR(500) -- Момент времени для восстановления. Примеры: '20150402001601'
DECLARE @DestinationFolder NVARCHAR(500) -- Каталог для размещения файлов БД. Если не задан, то будет использован исходное расположение. Пример: 'C:\Temp'
DECLARE @DropConnections int = 1 -- закрывать соединения
DECLARE @DebugLevel int = 0 -- null или 0 - выполнение восстановления, 1 - вывод текста срипта для восстановления без выполнения, 2 - вывод значений переменных и текста скрипта без выполнения
				
--Variable declaration.
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
DECLARE @SQL VARCHAR(MAX)
DECLARE @backupDBName NVARCHAR(255)

--Define version number
DECLARE @ProductVersion NVARCHAR(128) = CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductVersion'))
DECLARE @ProductVersionNumber TINYINT = SUBSTRING(@ProductVersion, 1, (CHARINDEX('.', @ProductVersion) - 1))

--Preparation
SET @backupDBName = REPLACE(@SourceDBName, ' ', '')
IF @DestinationDBName IS NULL SET @DestinationDBName = @SourceDBName + '_'
IF @DebugLevel IS NULL SET @DebugLevel = 0
IF @ServerName IS NULL SET @ServerName = @@SERVERNAME
IF (SELECT CHARINDEX(@SourceDBName, @RootPath)) = 0 SET @RootPath = @RootPath + '\' + @ServerName + '\' + @backupDBName

IF DB_ID(@DestinationDBName) IS NOT NULL AND @DropConnections = 1
	BEGIN
		SET @cmd = 'ALTER DATABASE [' + @DestinationDBName + ']'
			+ CHAR(13) + 'SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
			+ CHAR(13) + 'DROP DATABASE [' + @DestinationDBName + ']'
			+ CHAR(13)
		IF @DebugLevel = 0 EXEC sp_executesql @cmd
		ELSE PRINT @cmd
	END

--========== FULL BACKUP RESTORE ==========--
SET @backupPath = @RootPath + '\FULL\'
IF @DebugLevel > 1 PRINT '-- Restore full backup from ' + @backupPath;
		
--Get the list of backups files
SET @cmd = 'DIR /b ' + '"' + @backupPath + '"'
INSERT INTO @fileList (backupFile) EXEC master.sys.xp_cmdshell @cmd
		
--Find latest full backup
IF @RestoreDateTime IS NULL         
	SELECT @lastFullBackup = MAX(backupFile) FROM @fileList
	WHERE backupFile LIKE '%_FULL_%' AND backupFile LIKE '%' + @backupDBName + '%'
ELSE
	BEGIN
		DECLARE @currentLogBackup VARCHAR(255), @previousLogBackup VARCHAR(255), @startDateTime VARCHAR(255), @DateTimeValue VARCHAR(255);
		DECLARE backupFiles CURSOR FOR
			SELECT backupFile FROM @fileList
			WHERE backupFile LIKE '%_FULL_%' AND backupFile LIKE '%' + @backupDBName + '%'
			ORDER BY backupFile
		OPEN backupFiles
		FETCH NEXT FROM backupFiles INTO @backupFile
		WHILE @@FETCH_STATUS = 0
			BEGIN
				IF @RestoreDateTime > REPLACE(LEFT(RIGHT(@backupFile, 19), 15), '_', '') SET @lastFullBackup = @backupFile
				ELSE BREAK
				FETCH NEXT FROM backupFiles INTO @backupFile
			END
		CLOSE backupFiles
		DEALLOCATE backupFiles
	END
IF @lastFullBackup IS NULL BEGIN PRINT '--No backups found before ' + @RestoreDateTime + ', aborted' RETURN END

--Generate cmd
SET @cmd = 'RESTORE DATABASE [' + @DestinationDBName + '] FROM DISK = ''' + @backupPath + @lastFullBackup + ''' WITH REPLACE, NORECOVERY'
IF @SourceDBName != @DestinationDBName OR @DestinationFolder IS NOT NULL
	BEGIN
		IF OBJECT_ID('tempdb..#FileList') IS NOT NULL DROP TABLE #FileList
		CREATE TABLE #FileList (LogicalName VARCHAR(128), [PhysicalName] VARCHAR(128), [Type] VARCHAR, [FileGroupName] VARCHAR(128), [Size] VARCHAR(128), 
			[MaxSize] VARCHAR(128), [FileId]VARCHAR(128), [CreateLSN]VARCHAR(128), [DropLSN]VARCHAR(128), [UniqueId]VARCHAR(128), [ReadOnlyLSN]VARCHAR(128), [ReadWriteLSN]VARCHAR(128), 
			[BackupSizeInBytes]VARCHAR(128), [SourceBlockSize]VARCHAR(128), [FileGroupId]VARCHAR(128), [LogGroupGUID]VARCHAR(128), [DifferentialBaseLSN]VARCHAR(128),
			[DifferentialBaseGUID]VARCHAR(128), [IsReadOnly]VARCHAR(128), [IsPresent]VARCHAR(128), [TDEThumbprint]VARCHAR(128)
		)
		IF @ProductVersionNumber in (13) ALTER TABLE #FileList ADD [SnapshotUrl]NVARCHAR(360) -- sql version dependency
		DECLARE @Path VARCHAR(1000) = '' + @backupPath + @lastFullBackup + ''
		DECLARE @LogicalNameData VARCHAR(128), @LogicalNameLog VARCHAR(128), @StorageFolder VARCHAR(128)
		INSERT INTO #FileList EXEC('RESTORE FILELISTONLY FROM DISK=''' + @Path + '''')
 		SET @LogicalNameData=(SELECT LogicalName FROM #FileList WHERE Type='D')
		SET @LogicalNameLog=(SELECT LogicalName FROM #FileList WHERE Type='L')
		IF @DestinationFolder IS NULL
			BEGIN
				SET @DestinationFolder = (SELECT PhysicalName FROM #FileList where Type='D')
				SET @DestinationFolder = SUBSTRING(@DestinationFolder, 0, LEN(@DestinationFolder) - LEN(REVERSE(SUBSTRING(REVERSE(@DestinationFolder), 0, CHARINDEX('\', REVERSE(@DestinationFolder))))))
			END
		SET @cmd = @cmd + ', '
			+ 'MOVE ''' + @LogicalNameData + ''' TO ''' + @DestinationFolder + '\' + @DestinationDBName + '.mdf'', ' 
			+ 'MOVE ''' + @LogicalNameLog + ''' TO ''' + @DestinationFolder + '\' + @DestinationDBName + '.ldf''; ' 
		IF OBJECT_ID('tempdb..#FileList') IS NOT NULL DROP TABLE #FileList
	END
		
--Execute the full restore command
IF @DebugLevel = 0 EXEC sp_executesql @cmd
ELSE PRINT @cmd + CHAR(13)
		
--========== DIFF BACKUP RESTORE ==========--
SET @backupPath = @RootPath + '\DIFF\'
IF @DebugLevel > 1 PRINT '-- Restore diff backup from ' + @backupPath
		
--Find the latest differential backup
SET @cmd = 'DIR /b ' + '"' + @backupPath + '"'
INSERT INTO @fileList (backupFile) EXEC master.sys.xp_cmdshell @cmd
SELECT @lastDiffBackup = MAX(backupFile) FROM @fileList WHERE backupFile LIKE '%_DIFF_%' AND backupFile LIKE '%' + @backupDBName + '%'

--Check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
    BEGIN
        SET @cmd = 'RESTORE DATABASE [' + @DestinationDBName + '] FROM DISK = ''' + @backupPath + @lastDiffBackup + ''' WITH REPLACE, NORECOVERY'
		IF @DebugLevel > 1 PRINT '-- @lastFullBackup = ' + @lastFullBackup
        IF @DebugLevel = 0 EXEC sp_executesql @cmd
        ELSE PRINT @cmd + CHAR(13)
    END
		
--========== LOG RESTORE ==========--
SET @backupPath = @RootPath + '\LOG\'
IF @DebugLevel > 1 PRINT CHAR(13) + '-- Restore log from ' + @backupPath
IF @lastDiffBackup IS NULL SELECT @startDateTime = REPLACE(LEFT(RIGHT(@lastFullBackup, 19), 15), '_', '')
ELSE SELECT @startDateTime = REPLACE(LEFT(RIGHT(@lastDiffBackup, 19), 15), '_', '')
		
--Get the list of log files that are relevant to the backups being used
SET @cmd = 'DIR /b ' + '"' + @backupPath + '"'
INSERT  INTO @fileList (backupFile) EXEC master.sys.xp_cmdshell @cmd
DECLARE backupFiles CURSOR FOR
	SELECT backupFile FROM @fileList 
    WHERE backupFile LIKE '%_LOG_%' AND backupFile LIKE '%' + @backupDBName + '%' AND REPLACE(LEFT(RIGHT(backupFile, 19), 15), '_', '') > @startDateTime
	ORDER BY backupFile
OPEN backupFiles
		
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
SET @previousLogBackup = REPLACE(LEFT(RIGHT(@backupFile, 19), 15), '_', '')
SET @lastFullBackup = REPLACE(LEFT(RIGHT(@lastFullBackup, 19),15),'_','')
IF @RestoreDateTime < @startDateTime BEGIN PRINT '-- Invalid @RestoreDateTime - it must be a value greater than the last full or diff backup' RETURN END
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @currentLogBackup = REPLACE(LEFT(RIGHT(@backupFile, 19), 15), '_', '')
        IF @RestoreDateTime IS NULL
            BEGIN
                IF @currentLogBackup > @startDateTime
                    BEGIN
                        SET @cmd = 'RESTORE LOG [' + @DestinationDBName + '] FROM DISK = ''' + @backupPath + @backupFile + ''' WITH REPLACE, NORECOVERY'
                        IF @DebugLevel = 0 EXEC sp_executesql @cmd
                        ELSE PRINT @cmd
                    END
            END
        ELSE
            IF @currentLogBackup < @RestoreDateTime
                BEGIN
                    SET @cmd = 'RESTORE LOG [' + @DestinationDBName + '] FROM DISK = ''' + @backupPath + @backupFile + ''' WITH NORECOVERY'
                    IF @DebugLevel = 0 EXEC sp_executesql @cmd
                    ELSE PRINT @cmd
                END
            ELSE
        IF (@RestoreDateTime > @previousLogBackup AND @RestoreDateTime < @currentLogBackup) OR @RestoreDateTime < @previousLogBackup
            BEGIN
                SET @DateTimeValue = CONVERT(VARCHAR, CONVERT(DATETIME, SUBSTRING(@RestoreDateTime, 1,8)), 104) + ' ' + SUBSTRING(@RestoreDateTime,9,2) + ':' + SUBSTRING(@RestoreDateTime,11,2) + ':' + SUBSTRING(@RestoreDateTime,13,2)
                SET @cmd = 'RESTORE LOG [' + @DestinationDBName + '] FROM DISK = ''' + @backupPath + @backupFile + ''' WITH NORECOVERY, STOPAT = ''' + @DateTimeValue + ''''
                IF @DebugLevel = 0 EXEC sp_executesql @cmd
                ELSE PRINT @cmd
				BREAK
            END
        SET @previousLogBackup = @currentLogBackup 
        FETCH NEXT FROM backupFiles INTO @backupFile
    END
        
CLOSE backupFiles
DEALLOCATE backupFiles

--End with recovery so that the database is put back into a working state.
SET @cmd = CHAR(13) + 'RESTORE DATABASE [' + @DestinationDBName + '] WITH RECOVERY'
IF @DebugLevel = 0 EXEC sp_executesql @cmd
ELSE PRINT @cmd