USE [DBAUtility]
GO

/****** Object:  StoredProcedure [dbo].[RestoreYourDB_FE]    Script Date: 1/13/2017 2:47:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[RestoreYourDB_FE]

/*
EXECUTE [DBAUtility].[dbo].[RestoreYourDB_FE] 'DEV1_FE', 'DEV1'
*/

	@DatabaseNameToBeRestored varchar(100),
	@UserName varchar(50)

AS
BEGIN

	IF (ISNULL(@DatabaseNameToBeRestored, '') = '')
	BEGIN
		RAISERROR('Missing target database name: @DatabaseNameToBeRestored cannot be NULL or empty', 18, 0)
		RETURN
	END

	IF (ISNULL(@DatabaseNameToBeRestored, '') = '')
	BEGIN
		RAISERROR('Missing Login Name: @UserName cannot be NULL or empty', 18, 0)
		RETURN
	END

	--DECLARE @DBName_UserLogin_and_Password NVARCHAR(100); --These should be the same: Database name, Login name and password.
	DECLARE @SINGLE_USER NVARCHAR(100);
	DECLARE @MULTI_USER NVARCHAR(100);
	DECLARE @RESTORE_DATEABASE NVARCHAR(1000);

	--SET @DBName_UserLogin_and_Password = @DatabaseNameToBeRestored

	IF db_id(@DatabaseNameToBeRestored) IS NOT NULL
		SET @SINGLE_USER = 'ALTER DATABASE [' + @DatabaseNameToBeRestored + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'



	SET @RESTORE_DATEABASE = 'RESTORE DATABASE [' + @DatabaseNameToBeRestored + '] FROM  DISK = N''E:\Backups\FE_KW_PROD_SID_PRUNED.bak'' WITH  FILE = 1, ' +
							'MOVE N''KW-PROD-SID'' TO N''E:\sqldata\' + @DatabaseNameToBeRestored + '.mdf'', ' +
							'MOVE N''KW-PROD-INDEX'' TO N''E:\sqldata\' + @DatabaseNameToBeRestored + '_1.ndf'', ' +
							'MOVE N''Gen3File'' TO N''E:\sqldata\' + @DatabaseNameToBeRestored + '_2.ndf'', ' +
							'MOVE N''KW-PROD-SID_log'' TO N''E:\sqllog\' + @DatabaseNameToBeRestored + '_log.ldf'', ' +
							'NOUNLOAD, ' +
							'REPLACE, ' +
							'STATS = 5;'


	SET @MULTI_USER = 'ALTER DATABASE [' + @DatabaseNameToBeRestored + '] SET MULTI_USER;'

	PRINT 'Set ' + @DatabaseNameToBeRestored + ' to single user mode'
	EXEC (@SINGLE_USER);

	PRINT 'Restore ' + @DatabaseNameToBeRestored
	EXEC (@RESTORE_DATEABASE);

	PRINT 'Set ' + @DatabaseNameToBeRestored + ' to multi user mode'
	EXEC (@MULTI_USER);

	--Recreate accounts if needed
	PRINT 'If needed recreate accounts for octopus and ' + @UserName

	--Octopus

	DECLARE @SQL nvarchar(500);

	--SQL Server Login: Check if it exists, if not create it.
	IF NOT EXISTS(SELECT principal_id FROM sys.server_principals WHERE name = 'octopus')
	BEGIN
		SET @SQL = 'CREATE LOGIN octopus WITH PASSWORD = ''octopus'''
		EXECUTE SP_EXECUTESQL @SQL
	END

	----Database User: Check if it exists, if not create it.
	IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = 'octopus')
	BEGIN
			SET @SQL = 'CREATE USER octopus FOR LOGIN octopus'
			EXECUTE (@SQL)
	END

	----Turn off expiration and policy.
	SET @SQL = 'ALTER LOGIN [octopus] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
	EXECUTE SP_EXECUTESQL @SQL

	----Let access objects in db
	SET @SQL = 'ALTER ROLE [db_owner] ADD MEMBER [octopus]'
	EXECUTE SP_EXECUTESQL @SQL

	--Database that was restored

	--SQL Server Login: Check if it exists, if not create it.
	IF NOT EXISTS(SELECT principal_id FROM sys.server_principals WHERE name = @UserName)
	BEGIN
		--SET @SQL = 'CREATE LOGIN ' + @DBName_UserLogin_and_Password + ' WITH PASSWORD = ''' + @DBName_UserLogin_and_Password + ''''
		EXEC ('USE MASTER')
		SET @SQL = 'CREATE LOGIN ' + quotename(@UserName) + ' WITH PASSWORD = ' + quotename(@UserName, '''')
		EXECUTE SP_EXECUTESQL @SQL

	END

	EXEC master..sp_addsrvrolemember @loginame = @UserName, @rolename = N'sysadmin'

	--Turn off expiration and policy.
	EXEC ('USE MASTER')
	SET @SQL = 'ALTER LOGIN ' + @UserName + ' WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
	EXECUTE SP_EXECUTESQL @SQL

	PRINT 'Finished Restoring ' + @DatabaseNameToBeRestored

END
GO