param (

[string] $_instanceName,

[string] $_databaseName,

[string] $_schemaObjectRootPath

)

$currentPath = [System.IO.Directory]::GetCurrentDirectory();

[void][System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($currentPath,"log4net.dll"));

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO");

function LoadSchemaObjects ( $_db, [string]$_schemaObjectPath )

{

foreach ( $f in dir $_schemaObjectPath )

{

$fullPath = $_schemaObjectPath + "\" + $f;

$log.Info(" Applying [$fullPath] to [$_databaseName]");

$sr=new-object System.IO.StreamReader($fullPath);

$script=$sr.ReadToEnd();

$_db.ExecuteNonQuery($script);

trap

{

$log.Error("`t`t`t$error[0].exception");

}

$sr.Close();

}

}

function LoadBulkData ( $_db, [string]$_dataFilePath )

{

foreach ( $f in dir $_dataFilePath )

{

$filePath = "'$_dataFilePath\$f'";

$script = "BULK INSERT $_databaseName.$f FROM $filePath WITH ( DATAFILETYPE='widechar',ROWTERMINATOR ='\n',FIELDTERMINATOR = '\t', KEEPIDENTITY )";

$log.Info("$script");

$_db.ExecuteNonQuery($script);

}

}

$script:log=[log4net.LogManager]::GetLogger("PowerShell");

$private:fileInfo = new-object System.IO.FileInfo("powershell.config");

[log4net.Config.XmlConfigurator]::Configure($fileInfo);

$log.Info("Script started.");

$private:server = new-object ( 'Microsoft.SqlServer.Management.Smo.Server') $_instanceName;

$private:db = new-object ( 'Microsoft.SqlServer.Management.Smo.Database' ) ( $server, $_databaseName );

$db.Create();

$log.Info(" Created database [$_databaseName] on [$_instanceName]");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Options");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Roles");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Schemas");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\UserDefinedDataTypes");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Tables");

LoadBulkData $db ($_schemaObjectRootPath + "\BulkData\Default");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\PrimaryKeys");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\ForeignKeys");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\DmlTriggers");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Views");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\XmlSchemas");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Synonyms");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Functions");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\StoredProcedures");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Defaults");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Indexes");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\CheckConstraints");

LoadSchemaObjects $db ($_schemaObjectRootPath + "\Security");

$log.Info("Script finished.");