Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [DBAUtility]
- GO
- /****** Object: StoredProcedure [dbo].[RestoreYourDB_BE] Script Date: 1/13/2017 2:46:54 PM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE PROCEDURE [dbo].[RestoreYourDB_BE]
- /*
- EXECUTE [DBAUtility].[dbo].[RestoreYourDB_BE] 'DEV1_BE', '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\BE_KW_PROD_SID_PRUNED.bak'' WITH FILE = 1, ' +
- 'MOVE N''KW-PROD-SID_data'' TO N''E:\sqldata\' + @DatabaseNameToBeRestored + '.mdf'', ' +
- 'MOVE N''Index'' TO N''E:\sqldata\' + @DatabaseNameToBeRestored + '_1.ndf'', ' +
- 'MOVE N''CDR_Main_Line'' TO N''E:\sqldata\' + @DatabaseNameToBeRestored + '_2.ndf'', ' +
- 'MOVE N''Gen3File'' TO N''E:\sqldata\' + @DatabaseNameToBeRestored + '_3.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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement