DECLARE
	-- configurable variables
	@sourceDatabase SYSNAME = '' -- source database
,	@dataPath VARCHAR(255) = '' -- path to data file
,	@logPath VARCHAR(255) = '' -- path to log file
,	@tableListFile VARCHAR(255) = '' -- text file containing the list of tables to copy
,	@destinationDataSize INT = 500 -- destination database size in MB
,	@destinationLogSize INT = 50 -- destination log file size in MB

	-- non-configurable variables
,	@destinationDatabase SYSNAME
,	@TSQL VARCHAR(MAX)

	-- table specific variables
,	@tableName SYSNAME
,	@columnList VARCHAR(MAX)

-- initializations
SELECT
	@destinationDatabase = 'Copy_' + @sourceDatabase
,	@dataPath = CASE WHEN RIGHT(@dataPath, 1) <> '\' THEN @dataPath + '\' ELSE @dataPath END
,	@logPath = CASE WHEN RIGHT(@logPath, 1) <> '\' THEN @logPath + '\' ELSE @logPath END

IF OBJECT_ID('tempdb..##t_TableList') IS NOT NULL
	DROP TABLE ##t_TableList
CREATE TABLE ##t_TableList (
	TableName	SYSNAME NOT NULL
)

IF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL
	DROP TABLE ##t_ColumnList
CREATE TABLE ##t_ColumnList (
	ColumnList	VARCHAR(MAX) NOT NULL
)

SET NOCOUNT ON

BEGIN TRY
	-- Cleanup
	SELECT @TSQL = '' +
	'IF EXISTS ( SELECT name FROM master.sys.databases WHERE name = ''' + @destinationDatabase + ''' ) ' +
	'BEGIN ' +
	'ALTER DATABASE [' + @destinationDatabase + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' +
	'USE [master] DROP DATABASE [' + @destinationDatabase + '] ' +
	'END'
	PRINT(@TSQL)
	EXEC(@TSQL)

	-- Create Database
	SELECT @TSQL = '' +
	'CREATE DATABASE [' + @destinationDatabase + '] ' +
	'ON PRIMARY ' +
	'( NAME = N''' + @destinationDatabase + '_data'', FILENAME = N''' + @dataPath + @destinationDatabase + '_data.mdf'' , SIZE = ' + CONVERT(VARCHAR, @destinationDataSize) + 'MB , FILEGROWTH = 512000KB ) ' +
	'LOG ON ' +
	'( NAME = N''' + @destinationDatabase + '_log'', FILENAME = N''' + @logPath + @destinationDatabase + '_log.ldf'' , SIZE = ' + CONVERT(VARCHAR, @destinationLogSize) + 'MB , FILEGROWTH = 10%)'
	PRINT(@TSQL)
	EXEC(@TSQL)

	-- Get Table List from File
	SELECT @TSQL = '' +
	'BULK INSERT ##t_TableList FROM ''' + @tableListFile + ''' with (FirstRow = 1)'
	EXEC(@TSQL)

-- DEBUG	SELECT * FROM ##t_TableList

	-- Process Each Table
	WHILE EXISTS ( SELECT 1 FROM ##t_TableList )
	BEGIN
		SELECT TOP 1 @tableName = tableName, @columnList = '' FROM ##t_TableList
		DELETE FROM ##t_TableList WHERE tableName = @tableName
		PRINT(@tableName)

		-- Fetch Column List
		BEGIN
			SELECT @TSQL = 'USE [' + @sourceDatabase + ']
			DECLARE @columnList VARCHAR(MAX) = ''''
			SELECT @columnList = @columnList + CASE WHEN @columnList = '''' THEN ''['' + [name] + '']'' ELSE '',['' + [name] + '']'' END FROM sys.columns c WHERE c.is_computed = 0 AND OBJECT_SCHEMA_NAME([object_id]) = ''dbo'' AND OBJECT_NAME([object_id]) = ''' + @tableName + ''' ORDER BY column_id
			SELECT @columnList'

			INSERT INTO ##t_ColumnList (ColumnList)
				EXEC(@TSQL)

			SELECT @columnList = ColumnList FROM ##t_ColumnList
			DELETE FROM ##t_ColumnList
--			PRINT(@columnList)
		END

		-- Generate Destination Schema
		-- **note** Must create dummy join so identity columns are not propagated to destination database
		SELECT @TSQL = 'SELECT TOP 0 ' + @columnList + ' INTO [' + @destinationDatabase + '].[dbo].[' + @tableName + '] FROM [' + @sourceDatabase + '].[dbo].[' + @tableName + '] LEFT JOIN ( SELECT TOP 1 ''Dummy'' AS DummyColumn FROM master.sys.databases ) b ON b.[DummyColumn] = ''Dummy'''
		PRINT(@TSQL)
		EXEC(@TSQL)

		-- Insert Data
		SELECT @TSQL = 'INSERT INTO [' + @destinationDatabase + '].[dbo].[' + @tableName + '] WITH (TABLOCK) (' + @columnList + ') SELECT ' + @columnList + ' FROM [' + @sourceDatabase + '].[dbo].[' + @tableName + '] WITH (NOLOCK)'
		PRINT(@TSQL)
		EXEC(@TSQL)

		-- Enable Table Compression
		SELECT @TSQL = 'USE [' + @destinationDatabase + '] ALTER TABLE [dbo].[' + @tableName + '] REBUILD WITH (DATA_COMPRESSION=PAGE)'
		PRINT(@TSQL)
		EXEC(@TSQL)
	END

END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();

	IF @@TRANCOUNT > 0
		ROLLBACK TRAN
	ELSE

	-- Use RAISERROR inside the CATCH block to return error
	-- information about the original error that caused
	-- execution to jump to the CATCH block.
	RAISERROR (@ErrorMessage, -- Message text.
				@ErrorSeverity, -- Severity.
				@ErrorState -- State.
				);

END CATCH

-- Cleanup
SELECT @TSQL = 'IF EXISTS ( SELECT name FROM master.sys.databases WHERE name = ''' + @destinationDatabase + ''' ) USE [' + @destinationDatabase + '] ALTER DATABASE [' + @destinationDatabase + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE USE [master] DROP DATABASE [' + @destinationDatabase + ']'
PRINT(@TSQL)
EXEC(@TSQL)

IF OBJECT_ID('tempdb..##t_TableList') IS NOT NULL
	DROP TABLE ##t_TableList
IF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL
	DROP TABLE ##t_ColumnList