use master
go

DECLARE @dataPath VARCHAR(500)
DECLARE @dbSuffix VARCHAR(500)
DECLARE @dbName VARCHAR(500)
DECLARE @mdfPath VARCHAR(500)
DECLARE @ldfPath VARCHAR(500)
DECLARE @backupFile VARCHAR(500)
DECLARE @sql VARCHAR(1000)
DECLARE @cwbtName VARCHAR(500)
DECLARE @cwbtPassword VARCHAR(500)

--****CHANGE THESE******
SELECT @dbSuffix = '_demo'
SELECT @dataPath = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\'
SELECT @backupFile = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\cw_bu_11_8_17_kevin_test_data.cwb'
SELECT @cwbtPassword = '878nwu3D8mRUjspV8n2v'
--**********************

SELECT @dbName = 'CW_Data' + @dbSuffix
SELECT @cwbtName = 'cwbt' + @dbSuffix
SELECT @mdfPath = @dataPath + @dbName + '.mdf'
SELECT @ldfPath = @dataPath + @dbName + '_log.ldf'

SELECT @sql = 'CREATE DATABASE ' + @dbName + ' ON ( NAME = ''' + @dbName + ''', FILENAME = ''' + @mdfPath + ''', SIZE = 100MB, MAXSIZE = UNLIMITED, FILEGROWTH = 100MB ) ' +
	'LOG ON ( NAME = ''' + @dbName + '_log'', FILENAME = ''' + @ldfPath + ''', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 100MB  )'

EXEC ( @sql )

--go

SELECT @sql = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = ''' + @backupFile + ''' WITH MOVE ''' + @dbName + ''' TO ''' + @mdfPath + ''', MOVE ''' +  @dbName + '_log'' TO  ''' + @ldfPath + ''', REPLACE'

EXEC ( @sql )

--use master

--go

EXEC sp_addlogin @cwbtName, @cwbtPassword

SELECT @sql = 'grant view server state to ' + @cwbtName

EXEC (@sql)

SELECT @sql = 'use ' + @dbName + ';EXEC sp_changedbowner ''' + @cwbtName + ''';EXEC sp_helpUser ''dbo'';update cw_common_storage set cmm_st_text = ''' + @dbName + ''' where cmm_st_pk = ''DatabaseName'''

EXEC (@sql)