USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('[dbo].[DB_Restore]') IS NOT NULL
BEGIN
DROP PROC [dbo].[DB_Restore];
END
GO

/*******************************************************************/
-- Proc_Type Date_Modified Created_By Description 
-- Automation 19-Oct-2012 UDAY Initial
--
-- @DBName: Database Name to be restored
-- @BackupFile_Path: Fully qualified Backup file name
-- EXECUTE [DB_Restore] 'Test','C:\U_World\Shared\Test_bkp.bak'
/*******************************************************************/
CREATE PROCEDURE [dbo].[DB_Restore](
@DBName SYSNAME,
@BackupFile_Path VARCHAR(255))
AS 
BEGIN 
SET NOCOUNT ON
DECLARE @DBFilename VARCHAR(100),
@DBLogFilename VARCHAR(100),
@DBDataFile VARCHAR(100),
@v_strDBLogFile VARCHAR(100),
@ExecSQL NVARCHAR(1000),
@ExecSQL1 NVARCHAR(1000),
@MoveSQL NVARCHAR(4000),
@ReplaceFlag NVARCHAR(50),
@Temp NVARCHAR(1000),
@ListSQL NVARCHAR(4000),
@ServerVersion NVARCHAR(20),
@RestorePath VARCHAR(500)

BEGIN TRY

   /**** Update @ReplaceFlag depends on database existence****/
SET @ReplaceFlag = ''   

IF exists (select name from sys.databases where name = @DBName)
BEGIN
SET @ReplaceFlag = ', REPLACE'
END 


/*** Create table ##FILE_LIST to hold file infor from backup file ***/
SET @ListSQL = ''
SET @ListSQL = @ListSQL + 'IF (EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME = ''##FILE_LIST''))'
SET @ListSQL = @ListSQL + 'BEGIN'
SET @ListSQL = @ListSQL + '   DROP TABLE ##FILE_LIST '
SET @ListSQL = @ListSQL + 'END '

SET @ListSQL = @ListSQL + 'CREATE TABLE ##FILE_LIST ('
SET @ListSQL = @ListSQL + '   LogicalName VARCHAR(64),'
SET @ListSQL = @ListSQL + '   PhysicalName VARCHAR(130),'
SET @ListSQL = @ListSQL + '   [Type] VARCHAR(1),'
SET @ListSQL = @ListSQL + '   FileGroupName VARCHAR(64),'
SET @ListSQL = @ListSQL + '   Size DECIMAL(20, 0),'
SET @ListSQL = @ListSQL + '   MaxSize DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   FileID bigint,'
SET @ListSQL = @ListSQL + '   CreateLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   DropLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   UniqueID UNIQUEIDENTIFIER,'
SET @ListSQL = @ListSQL + '   ReadOnlyLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   ReadWriteLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   BackupSizeInBytes DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   SourceBlockSize INT,'
SET @ListSQL = @ListSQL + '   filegroupid INT,'
SET @ListSQL = @ListSQL + '   loggroupguid UNIQUEIDENTIFIER,'
SET @ListSQL = @ListSQL + '   differentialbaseLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   differentialbaseGUID UNIQUEIDENTIFIER,'
SET @ListSQL = @ListSQL + '   isreadonly BIT,'
SET @ListSQL = @ListSQL + '   ispresent BIT'

SELECT @ServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)

IF @ServerVersion LIKE '10.%' OR @ServerVersion LIKE '11.%' OR @ServerVersion LIKE '12.%'
BEGIN
SET @ListSQL = @ListSQL + ', TDEThumbpr DECIMAL'
END

SET @ListSQL = @ListSQL + ')'
EXEC (@ListSQL)
PRINT @BackupFile_Path;
INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile_Path + '''')
ALTER TABLE ##FILE_LIST ADD D_File VARCHAR(150)

/*** Get the default file location. It help us when database is not already exists ***/
SELECT TOP(1) @RestorePath =  physical_name FROM sys.master_files;

SET  @RestorePath = REPLACE(@RestorePath, RIGHT(@RestorePath, CHARINDEX('\', REVERSE(@RestorePath))-1),'')

/*** Capture information od data files - MDF, NDF and LDF ***/
/*** We have two cases 1. Database is already there is instance ***/
/*** 2. New database Restore (ELSE) ***/
IF EXISTS (SELECT 1 FROM SYS.SYSDATABASES WHERE NAME=@DBName)
BEGIN
DECLARE @DataFile VARCHAR(150),
@LogFile VARCHAR(150);

SELECT TOP 1 @DataFile = physical_name 
FROM sys.master_files 
WHERE Database_ID = DB_ID(@DBName) AND TYPE=0;

SELECT TOP 1 @LogFile = physical_name 
FROM sys.master_files 
WHERE Database_ID = DB_ID(@DBName) AND TYPE=1;

UPDATE ##FILE_LIST 
SET D_File = @DataFile 
WHERE TYPE='D' AND FileID=1;


UPDATE ##FILE_LIST 
SET D_File = LEFT(@DataFile,LEN(@DataFile)-4)+'_'+CONVERT(VARCHAR(5),FILEID)+'.ndf'
WHERE TYPE='D'AND FileID=1;


UPDATE ##FILE_LIST 
SET D_File =LEFT(@LogFile,LEN(@LogFile)-4)+'_'+CONVERT(VARCHAR(5),FILEID)+'_log.ldf'
WHERE TYPE='L';

END
ELSE 
BEGIN
UPDATE ##FILE_LIST 
SET D_File=@RestorePath+@DBName+'.mdf' 
WHERE TYPE='D'AND FileID=1;

UPDATE ##FILE_LIST 
SET D_File=@RestorePath+@DBName+'_'+CONVERT(VARCHAR(5),FILEID)+'.ndf'
WHERE TYPE='D'AND FileID=1


UPDATE ##FILE_LIST 
SET D_File=@RestorePath+@DBName+'_'+CONVERT(VARCHAR(5),FILEID)+'_log.ldf'
WHERE TYPE='L'

END

    /*** Loop through the all files mdf,ldf and ndf ****/
    /*** Form a string "MOVE" To MDF, MOVE LDF etc ***/
DECLARE CurFiles CURSOR FOR 
SELECT 'MOVE N''' + LogicalName + ''' TO N''' + D_File + '''' 
FROM ##FILE_LIST

SET @MoveSQL = ''

OPEN CurFiles 
FETCH NEXT FROM CurFiles into @Temp


WHILE @@Fetch_Status= 0
BEGIN
SET @MoveSQL = @MoveSQL + @Temp + ', '
FETCH NEXT FROM CurFiles into @Temp
END

CLOSE CurFiles
DEALLOCATE CurFiles

/*** Kill all connections to the database before restore ***/
PRINT 'Killing active connections to the database : ' + @DBName + CHAR(10)

SET @ExecSQL = ''
SELECT   @ExecSQL = @ExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
FROM     master.dbo.sysprocesses
WHERE    DB_NAME(dbid) = @DBName AND DBID <> 0 AND spid <> @@spid
AND status <> 'background'
AND status IN ('runnable','sleeping')

EXEC (@ExecSQL)

/*** Restore Database ***/ 
PRINT 'Restoring ' + @DBName + '; database from ' + @BackupFile_Path + CHAR(10)

SET @ExecSQL = 'RESTORE DATABASE [' + @DBName + ']'
SET @ExecSQL = @ExecSQL + ' FROM DISK = ''' + @BackupFile_Path + ''''
SET @ExecSQL = @ExecSQL + ' WITH FILE = 1,'
SET @ExecSQL = @ExecSQL + @MoveSQL
SET @ExecSQL = @ExecSQL + ' NOREWIND, '
SET @ExecSQL = @ExecSQL + ' NOUNLOAD, STATS=10 '
SET @ExecSQL = @ExecSQL + @ReplaceFlag

PRINT '/**************************************************/'
PRINT '/****** Start Restore Operation *******************/'
PRINT '/**************************************************/'

EXEC (@ExecSQL)

PRINT CHAR(10)
PRINT '/**************************************************/'
PRINT '/****** Restore Operation Completed ***************/'
PRINT '/**************************************************/'

END TRY
BEGIN CATCH
PRINT 'Restore Failed with error' + ERROR_MESSAGE()

IF (EXISTS (SELECT 1 FROM TEMPDB..sysobjects WHERE NAME ='##FILE_LIST'))
BEGIN
DROP TABLE ##FILE_LIST
END
RETURN
END CATCH

/*** Drop the temp table  ***/
IF (EXISTS (SELECT 1 FROM TEMPDB..sysobjects WHERE NAME ='##FILE_LIST'))
BEGIN
   DROP TABLE ##FILE_LIST
END

PRINT CHAR(10)+'Database ' + @DBName +' Restored Successfully '
SET NOCOUNT OFF
END