Create Normalised Databases with Powershell

# create_databases.ps1
# Creates a new database using specifications
# Scrit owner , Patrick Viennot
# creation Date 2012/06/06

# Modification 08/06/2012
# increase the number of files 
# up to 9 files 


# Then ask some question to get information before creation
# i,e Databasename, Instance Name, filename, size, etc...
clear-host
" "
"Please wait while loading assemblies"
" "
"*"

 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
clear-host
" "
"Please wait while loading assemblies"
" "
"**"
 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
 clear-host
" "
"Please wait while loading assemblies"
" "
"***"
 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
 
# Constants
$dbowner = 'sa'
$compatibility = $null
$number_of_file=9
$logevt = 'SQLSERVER'

#Functions
function Pause ($Message="Press any key to continue...") {
Write-Host -NoNewLine $Message 
$null = $Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
Write-Host ""
}
function IsNullOrEmpty($str) {if ($str) {
"String is not empty"} else {"String is null or empty"}
}

# start with entries and verify
Pause
clear-host
$userid =[System.Security.Principal.WindowsIdentity]::GetCurrent().Name
$dbname = read-host "enter the database name "
if (!$dbname) {exit}
$Servername = read-host "enter the server Name (including instance) "
if (! $servername) {exit}
$createdb = read-host "Create Database (Y/N) "
# Instantiate Server/instance /BDD
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Servername
$db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, $dbname)
#
$evtlog=Get-EventLog -list | Where-Object {$_.logdisplayname -eq $logevt} 
if (! $evtlog) {new-eventlog -logname $logevt -Source 'SQL_EVENT'}
$source = 'SQL_EVENT'
if ([System.Diagnostics.EventLog]::SourceExists($source) -eq $false) {[System.Diagnostics.EventLog]::CreateEventSource($source, $logevt)}
# register username
$msg = 'Creation Process  started by  ' + $userid + ' database ' + $dbname
write-eventlog  -logname $logevt -Source $source  -message $msg  -id 200

if ($createdb -eq 'y' -or $createdb -eq 'Y')
{
$filename = 'standard'
$Vfilename = read-host "enter the file Name (without path, if different from database name) "
$filegroup = read-host "enter filegroup (if not PRIMARY) "
if (! $filegroup) {$filegroup = 'PRIMARY'}
if ($filegroup -eq 'primary' -or $filegroup -eq 'Primary')  {$filegroup = 'PRIMARY'}
$nbrfile = read-host "enter number of files for PRIMARY  1 = (default) - 4 (max) "
if ($filegroup -ne 'PRIMARY') { $nbrother =read-host "enter number of file for " $filegroup  }
$nbrlog = read-host "enter number of log files  1 = (default) - 9 (max) "
$Dsize = read-host "enter Data size in MB (if more than 128) "
$Lsize = read-host "enter Log size in MB (if more than 128) "
$Lgrow = read-host "enter File grow in MB (if more than 128) "
$Recovery = read-host "enter recovery Model (simple = default - Full) " 
$collation = read-host "enter db_collation ( default=SQL_Latin1_General_CP1_CI_AS"
$compatibility = read-host "enter compatibility  actual = (default) - 80/90... "

if (! $collation) {$collation = 'SQL_Latin1_General_CP1_CI_AS'}

$nbr = [int]$nbrfile
$nbro = [int]$nbrother
$nbrl = [int]$nbrlog

if (! $nbrfile) {$nbrfile=1;$nbr=1}
if (! $nbrother) {$nbrother=1;$nbro=1}
if (! $nbrlog) {$nbrlog=1;$nbrl=1}

if ($nbrfile -gt $number_of_file) {$nbr=$number_of_file}
if ($nbrother -gt $number_of_file) {$nbro=$number_of_file}
if ($nbrlog -gt $number_of_file) {$nbrl=$number_of_file}

if (! $Recovery) {$Recovery = 'simple'}
if ($Vfilename)  {$filename = $Vfilename}

$Dsize = [int]$Dsize
$Lsize = [int]$Lsize
$Lgrow = [int]$Lgrow

if (! $Dsize) {$Dsize=128}
if ($Dzize -lt 128) { $Dsize=128}
if (! $Lsize) {$Lsize=128}
if ($Lzize -lt 128) { $Lsize=128}
if (! $Lgrow) {$Lgrow=128}
if ($Lgrow -lt 128) { $Lgrow=128}

# now ask user is the entries are expected
clear-host
write-host 'You are about to install with the following information :'
write-host 'Database Name ' $dbname
write-host 'Server Name '   $Servername
write-host 'Filegroup ''PRIMARY'' with ' $nbr ' file(s) named on '  $filename 
if ($filegroup -ne 'PRIMARY') {
write-host 'Filegroup '''     $filegroup ''' with '  $nbro ' file(s) named on ' $filename
}
write-host  $nbrlog ' Logfile(s) named on '$filename
write-host 'Size Data '     $Dsize
write-host 'Size Log '      $Lsize
write-host 'Size Growth '    $Lgrow
write-host 'Recovery Mode '  $Recovery
write-host 'collation '     $collation
write-host 'compatibility '     $compatibility
write-host ' '
write-host ' '

# get response from user
$response = read-host 'do you agree (Y/N) ? '
if ($response -ne 'y' -or $response -ne 'Y') {exit}
# pause
# if yes => next otherwise stop
# Instantiate the database object and add the filegroups
$sysfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'PRIMARY')
$db.FileGroups.Add($sysfg)
# if user select filegroup <> PRIMARY we assume that there is another filegroup and another file to be created
if ($filegroup -ne 'PRIMARY') {
$appfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $filegroup)
$db.FileGroups.Add($appfg)
}
# Once the filegroups have been created, we can create the files for the database. First we create the file for the database metadata. I've set the size to be 128MB with  growth. To create the database the PRIMARY filegroup has to be set to be the default, so we'll set that here as well.

# Create the file for the system tables
if ( $Filename -eq 'standard') {$syslogname = $dbname + '_Data'} ELSE  {$syslogname = $filename + '_Data' }
# write-host $syslogname
$dbdsysfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($sysfg, $syslogname)
$sysfg.Files.Add($dbdsysfile)
$dbdsysfile.FileName = $s.settings.defaultFile + '\' + $syslogname + '.mdf'
# write-host $dbdsysfile
$dbdsysfile.Size = [double]($Dsize * 1024.0)
$dbdsysfile.GrowthType = 'KB'
$dbdsysfile.Growth = $Lgrow * 1024.0
$dbdsysfile.Set_MaxSize('-1')
$dbdsysfile.IsPrimaryFile = 'True'

# creation of the other file in case of different files in PRIMARY 
# Create the file for Applications Tables in PRIMARY
while ($nbr -gt 1) {
 if ( $Filename -eq 'standard') {$applogname = $dbname + '_0' + $nbr + '_Data'} ELSE  {$applogname =  $filename + '_0' + $nbr + '_Data'}
# "$applogname"
$dbdappfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($sysfg, $applogname)
$sysfg.Files.Add($dbdappfile)
$dbdappfile.FileName = $s.settings.defaultFile + '\' + $applogname + '.ndf'
$dbdappfile.Size = [double]($Dsize * 1024.0)
$dbdappfile.GrowthType = 'KB'
$dbdappfile.Growth = $Lgrow * 1024.0
$dbdappfile.Set_MaxSize('-1')
$nbr--
}  

# creation of the other file in case of different filegroup 
# Create the file for the Application tables in other Partition
if ( $Filegroup -ne 'PRIMARY') {
while ($nbro -gt 0) {
if ( $Filename -eq 'standard') {$applogname = $dbname + '_0' + $nbro + '_AppData'} ELSE  {$applogname =  $filename + '_0' + $nbro  + '_AppData'}
$dbdappfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($appfg, $applogname)
$appfg.Files.Add($dbdappfile)
$dbdappfile.FileName = $s.settings.defaultFile + '\' + $applogname + '.ndf'
$dbdappfile.Size = [double]($Dsize * 1024.0)
$dbdappfile.GrowthType = 'KB'
$dbdappfile.Growth = $Lgrow * 1024.0
$dbdappfile.Set_MaxSize('-1')
$nbro--
}  
}

# Now I can create the file for the transaction log. 

# Create the file for the log
while ($nbrl -gt 0) {
if ( $Filename -eq 'standard') {$loglogname = $dbname + '_0' + $nbrl + '_Log'} ELSE  {$loglogname = $filename + '_0' + $nbrl +  '_Log' }
# write-host $syslogname
$dblfile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($db, $loglogname)
$db.LogFiles.Add($dblfile)
$dblfile.FileName = $s.settings.defaultlog + '\' + $loglogname + '.ldf'
# write-host $dblfile.filename
$dblfile.Size = [double]($Lsize * 1024.0)
$dblfile.GrowthType = 'KB'
$dblfile.Growth = $Lgrow * 1024.0
$dblfile.set_MaxSize('-1')
$nbrl--
}
#
# We can create the database now, and once it's been created we can grab the AppFG filegroup, set it's default property to True, alter the filegroup and alter the database. Now it's ready for loading the tables and other objects necessary for the application to work properly.

# Create the database
$db.Collation = $collation
# Set the recovery model
$db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::$recovery
$db.Create()

$evtlog=Get-EventLog -list | Where-Object {$_.logdisplayname -eq $logevt} 
if (! $evtlog) {new-eventlog -logname $logevt -Source 'SQL_EVENT'}
$source = 'SQL_EVENT'
if ([System.Diagnostics.EventLog]::SourceExists($source) -eq $false) {[System.Diagnostics.EventLog]::CreateEventSource($source, $logevt)}
$msg = 'Creation Process for Database '  + $dbname + ' realised by  ' + $userid  
$msg = $msg + ' on Filegroup '+ $filegroup + ' with ' + $nbro + ' file(s) named on ' + $filename
write-eventlog  -logname $logevt -Source $source  -message $msg  -id 210
write-host ''
Write-host $msg
write-host ''
if ($compatibility)  {$db.CompatibilityLevel = 'Version' + $compatibility}
$db.SetOwner($dbowner)
$db.Alter()
$msg =  'Altering Database' + $dbname + ' => owner : ' + $dbowner
if ($compatibility)  {$msg  = $msg + ' compatibility Database Version ' + $compatibility}
write-eventlog  -logname $logevt -Source $source  -message $msg  -id 220
write-host $msg
write-host ''

}

# end of proc create database