SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[uspRefreshDB]') IS NULL
  EXEC ('CREATE PROCEDURE [dbo].[uspRefreshDB] AS RETURN 0;');
GO


ALTER   PROC [dbo].[uspRefreshDB]
-- Database to restore
@dbName sysname,
-- Directory where full backups stored
@FullBackupDir NVARCHAR(500) = '\\BackupNAS\sql_backups\Regular\Full\',
-- Directory where diff backups stored
@DiffBackupDir NVARCHAR(500) = '\\BackupNAS\sql_backups\Regular\Diff\'

AS 

----------------------------------------------------------------------------------------------------
  --// Source:  https://avolok.github.io                                                          //--  
  --// GitHub:  https://github.com/avolok/scripts                                                 //--
  --// Version: 2019-09-15                                                                        //--
----------------------------------------------------------------------------------------------------

SET NOCOUNT ON 

-- 1 - Variable declaration 

DECLARE @cmd NVARCHAR(4000) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 
DECLARE @commandDecored NVARCHAR(500)

DECLARE @commandList TABLE
(
	CommandID INT IDENTITY PRIMARY KEY
,	CommandText NVARCHAR(max)
,	CommandStatus INT 
)

-- 2 - Check if the feature XP_CMDSHELL is enabled
IF NOT (
SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM  sys.configurations
WHERE  name = 'xp_cmdshell' ) = 1 
BEGIN
    RAISERROR ('The feature [xp_cmdshell] is not enabled. It is required to run an automated restore. Terminating...', 16,1)
	RETURN;
END



-- 3 - get list of files 
-- full
--SET @DiffBackupDir = '\\backupshare\sql_backups\Regular\Full\NLC1DWHSQLCLS1\' 

SET @cmd = 'DIR /b "' + @FullBackupDir + '"'

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


-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  AND backupFile LIKE '%[_]'+ @dbName + '[_]full%' 



IF (@lastFullBackup IS NULL )
BEGIN    
	PRINT CONCAT('No backup found for database: [', @dbName, ']. Terminating...')
	RETURN
END
ELSE
BEGIN
    PRINT CONCAT('Restoring database: ', QUOTENAME(@dbName), '. 
Full backup to be restored: ', @lastFullBackup, '
')
END



IF DB_ID(@dbName) IS NOT NULL AND   DATABASEPROPERTYEX(@dbName, 'Status') = 'ONLINE'
BEGIN
	SET @cmd = CONCAT('ALTER DATABASE   ', QUOTENAME(@dbName), ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' ) 
    INSERT INTO @commandList (CommandText  )  VALUES  ( @cmd )
END





SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  
       + @FullBackupDir + @lastFullBackup + ''' WITH NORECOVERY, REPLACE, STATS=20' 


INSERT INTO @commandList (CommandText  )  VALUES  ( @cmd )



-- 4 - Find latest diff backup 

-- clean the filelist
delete @fileList


SET @cmd = 'DIR /b "' + @DiffBackupDir + '"'

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

SELECT @lastDiffBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
   AND backupFile LIKE '%[_]'+ @dbName + '[_]Diff%' 
   

-- check to make sure there is a diff backup 
IF @lastDiffBackup IS NOT NULL 
BEGIN 
   SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  
       + @DiffBackupDir + @lastDiffBackup + ''' WITH NORECOVERY, STATS=20' 

   INSERT INTO @commandList (CommandText  )  VALUES  ( @cmd )

   SET @lastFullBackup = @lastDiffBackup 

    PRINT CONCAT('Diff backup to be restored: ', @lastDiffBackup, '




')


END 




-- 6 - put database in online state 
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY' 

INSERT INTO @commandList (CommandText  )  VALUES  ( @cmd )

-- 7 - execute the script, command by command


DECLARE ct CURSOR FOR SELECT CommandText, CONCAT('Command ', CommandID, ': ', CommandText, '

') FROM @commandList ORDER BY CommandID

OPEN ct

FETCH NEXT FROM ct INTO @cmd, @commandDecored

WHILE @@FETCH_STATUS = 0
BEGIN
    --- printing immidiatelly
	RAISERROR ( @commandDecored , 10, 1) WITH NOWAIT
	

	EXEC (@cmd)

	RAISERROR ( '--------------------------------------
	' , 10, 1) WITH NOWAIT

	FETCH NEXT FROM ct INTO @cmd, @commandDecored
END

CLOSE ct
DEALLOCATE ct