Restore SQL Database

-- Restores the DBs
SET NOCOUNT ON

DECLARE @DB_NAMELIST VARCHAR(200)
DECLARE @DB_NAME VARCHAR(50)
DECLARE @STG_NAME VARCHAR(50)
DECLARE @SQL VARCHAR(MAX)
DECLARE @BackupFolderLocation VARCHAR(50)
DECLARE @BackupExtension VARCHAR(10)
DECLARE @Backupfilepathname VARCHAR(150)
DECLARE @$$errno INT
DECLARE @$$errmsg VARCHAR(1000)
DECLARE @$$severitylevel INT
DECLARE @pos INT
DECLARE @exist INT

SET @DB_NAMELIST = 'Prod_Sitecore_Core,Prod_Sitecore_Master,Prod_Sitecore_Web'
SET @BackupFolderLocation = 'D:\SQLBackup\'
SET @BackupExtension = '.bak'
SET @DB_NAMELIST = LTRIM(RTRIM(@DB_NAMELIST)) + ','
SET @Pos = CHARINDEX(',', @DB_NAMELIST, 1)
SET @$$errmsg = ''

IF REPLACE(@DB_NAMELIST, ',', '') <> ''
BEGIN
	WHILE @Pos > 0
	BEGIN
		SET @DB_NAME = LTRIM(RTRIM(LEFT(@DB_NAMELIST, @Pos - 1)))

		IF @DB_NAME <> ''
		BEGIN
			SET @Backupfilepathname = @BackupFolderLocation + @DB_NAME + @BackupExtension
			--SELECT @exist = dbo.udf_CheckFileStatus (@Backupfilepathname) 
			SET @exist = 0

			IF (@exist = 0)
			BEGIN
				--PRINT 'backup file exist'
				SELECT @STG_NAME = REPLACE(@DB_NAME, 'PROD_', 'DEV_')

				SELECT @SQL = 'alter database ' + @STG_NAME + ' set SINGLE_USER WITH ROLLBACK IMMEDIATE ;'

				--print (@SQL)
				EXEC (@SQL)

				SELECT @$$errno = @@error

				IF (@$$errno <> 0)
				BEGIN
					SELECT @$$errmsg = 'Error : while setting the target database to SINGLE user mode, SQL: ' + @SQL

					GOTO ERROR
				END

				SELECT @SQL = 'restore database ' + @STG_NAME + ' from DISK = N''' + @Backupfilepathname + ''' WITH REPLACE ;'

				--print (@SQL)
				EXEC (@SQL)

				SELECT @$$errno = @@error

				IF (@$$errno <> 0)
				BEGIN
					SELECT @$$errmsg = 'Error : while restoring the target database using the source database backup, SQL: ' + @SQL

					GOTO ERROR
				END

				SELECT @SQL = 'alter database ' + @STG_NAME + ' set multi_user ;'

				--print (@SQL)
				EXEC (@SQL)

				SELECT @$$errno = @@error

				IF (@$$errno <> 0)
				BEGIN
					SELECT @$$errmsg = 'Error : While setting the target database to MULTI USER mode, SQL : ' + @SQL

					GOTO ERROR
				END
			END
			ELSE
			BEGIN
				PRINT 'Backup ' + @Backupfilepathname + ' does not exist !'
			END

			SET @DB_NAMELIST = RIGHT(@DB_NAMELIST, LEN(@DB_NAMELIST) - @Pos)
			SET @Pos = CHARINDEX(',', @DB_NAMELIST, 1)
		END --end @DB_NAME
	END --end while loop

	GOTO END_PROC
END

ERROR:

SELECT @$$errmsg = 'Error dbo.sp_restore_db : ' + isnull(@$$errmsg, '')

RAISERROR (
		@$$errmsg
		,@$$severitylevel
		,1
		)

END_PROC:

SET NOCOUNT OFF
GO

USE DIADevSitecore_Core

EXEC sp_change_users_login 'Update_One'
	,'SITECOREUSR'
	,'SITECOREUSR';

USE DIADevSitecore_Master

EXEC sp_change_users_login 'Update_One'
	,'SITECOREUSR'
	,'SITECOREUSR';

USE DIADevSitecore_Web

EXEC sp_change_users_login 'Update_One'
	,'SITECOREUSR'
	,'SITECOREUSR';

USE master;

ALTER DATABASE DIADevSitecore_Core

SET RECOVERY SIMPLE;

ALTER DATABASE DIADevSitecore_Master

SET RECOVERY SIMPLE;

ALTER DATABASE DIADevSitecore_Web

SET RECOVERY SIMPLE;
GO