/*
Skripta za restore baza iz zadanog direktorija
potrebno je postaviti parametre:
@backupPath - direktorij u kome se nalaze full backup baza
@dataPath - direktorij u koji ce se staviti mdf i ldf restoranih baza

Pokrenuti ako nije omogućen xp_cmdshell:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
*/

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @backupPath NVARCHAR(500)
DECLARE @dataPath NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
DECLARE @logicalNameData NVARCHAR(255)
DECLARE @logicalNameLog NVARCHAR(255)
DECLARE @cmd NVARCHAR(500)

DECLARE @fileList TABLE
(
backupFile NVARCHAR(255)
)

DECLARE @fileListOnly TABLE
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128) ,
Size numeric (20,0),
MaxSize numeric (20,0),
FileId bigint,
CreateLSN numeric (25,0),
DropLSN numeric (25,0),
UniqueId uniqueidentifier,
ReadOnlyLSN numeric (25,0),
ReadWriteLSN numeric (25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric (25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint varbinary(32)
)

-- 2 - Initialize variables
SET @backupPath = 'C:\EskulapWin\Arhiva\'
SET @dataPath = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.EDBOREL\MSSQL\DATA\'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile) EXEC master.sys.xp_cmdshell @cmd

-- 4 - check for log backups
DECLARE restoreFiles CURSOR FOR SELECT backupFile FROM @fileList
OPEN restoreFiles

-- 5 - Loop through all the files for the database
FETCH NEXT FROM restoreFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @fileListOnly

SET @cmd = 'RESTORE FILELISTONLY FROM DISK = ''' + @backupPath + @backupFile + ''''
PRINT @cmd
INSERT INTO @fileListOnly EXEC sp_executesql @cmd

SELECT @logicalNameData = LogicalName FROM @fileListOnly WHERE [Type] = 'D'
SELECT @logicalNameLog = LogicalName FROM @fileListOnly WHERE [Type] = 'L'

SET @cmd = 'RESTORE DATABASE ' + REPLACE(@backupFile, '.bak', '') + CHAR(10) +
'FROM DISK = ''' + @backupPath + @backupFile + ''' WITH ' + CHAR(10) +
'MOVE ''' + @logicalNameData + ''' TO ''' + @dataPath + REPLACE(@backupFile, '.bak', '') + '_Data.mdf'', ' + CHAR(10) +
'MOVE ''' + @logicalNameLog + ''' TO ''' + @dataPath + REPLACE(@backupFile, '.bak', '') + '_Log.ldf'''
PRINT @cmd

EXEC sp_executesql @cmd

FETCH NEXT FROM restoreFiles INTO @backupFile
END

CLOSE restoreFiles
DEALLOCATE restoreFiles