Restore of database

USE your_config_db
GO

CREATE TABLE [dbo].[TempFile](
[backupFile] [nvarchar](255) NULL,
[depth] [smallint] NULL,
[file] [smallint] NULL
) ON [PRIMARY]

GO

USE your_config_db
GO

Create PROC Restore_Database @backupPath nvarchar(500), @DBName sysname, @Print_or_Run smallint = 0

AS

/*

Script made by: Christian Møller
Email: Christian@eintveit.com
Date Created: 15.08.2016

*/
Truncate table TempFile

DECLARE @cmd nvarchar(1000)
DECLARE @Fil varchar(255)
DECLARE @Restore nvarchar(2000)
DECLARE @FileID smallint
DECLARE @Mdfpath varchar(500)
DECLARE @Ldfpath varchar(500)
DECLARE @mdfname varchar(150)
DECLARE @ldfname varchar(150)


-- 2 - Initialize variables 
SET @dbName = @DBName
SET @backupPath = @backupPath

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

INSERT into TempFile
EXEC master.sys.xp_dirtree @backupPath, 0, 1

-- 4 - check for serveral backup files
DECLARE backupFiles CURSOR FOR  
   SELECT backupfile, [file]  
   FROM TempFile


Declare @i int, @count int   -- variables for counting and looping files
Set @i = 0
Set @count = 0
Select @count = COUNT(1)+1 from TempFile

OPEN backupFiles  -- Cursor

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @Fil, @fileid  

WHILE @@FETCH_STATUS = 0  
BEGIN 

If @i = 0 and @i < @count
   Begin
--Print '@i lik null, først loop'
Set @Restore = 'RESTORE DATABASE ' + @dbName + ' FROM DISK =' + 'N' + '''' +  @backupPath + @Fil + '' +  '''' + ',' 
Set @i = @i + 1
 END
Else
IF @i >= 1 and @i < @count
Begin
-- IF @i >1 and @i < @Count
Set @Restore =  @Restore + ' DISK =' + 'N' + '''' + @backupPath + @Fil + '' +  '''' + ',' 
Set @count = @count + 1 -- Increase count until finished

END-- Else

   FETCH NEXT FROM backupFiles INTO @Fil , @fileid
END 

CLOSE backupFiles  
DEALLOCATE backupFiles  


SET @Restore = LEFT(@Restore, LEN(@Restore) - 1)

select  @Mdfpath =  physical_name from sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id and db.name = @dbName and mf.type = 0
select  @mdfname=  mf.name from sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id and db.name = @dbName and mf.type = 0
Select @Ldfpath = physical_name from sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id and db.name = @dbName and mf.type = 1
select  @ldfname=  mf.name from sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id and db.name = @dbName and mf.type = 1
Set @Restore = @Restore  + ' WITH  FILE = 1, ' + 'Move ' + 'N' + '''' + @mdfname + '''' + ' To ' + 'N' + '''' + @Mdfpath + '''' + ', '
Set @Restore = @Restore + 'Move ' + 'N' + '''' + @ldfname + '''' + 'TO ' + 'N' + '''' + @Ldfpath + '''' + ', NOUNLOAD,  REPLACE,  MAXTRANSFERSIZE = 4194304,
BUFFERCOUNT = 250'
Set @Restore = 'Use Master ALTER DATABASE ' + @dbName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' + @Restore
Set @Restore = @Restore + ' ALTER DATABASE ' + @dbName + ' SET MULTI_USER'
 
If @Print_or_Run = 0 -- Default value
 
     PRINT @Restore -- Print of command
Else
 EXECUTE sp_executesql @Restore