########################################################################
# Code Generated By: SAPIEN Technologies, Inc., PrimalForms 2009 v1.1.18.0
# Generated On: 4/16/2013 2:30 PM
# Generated By: kc020616
########################################################################
#----------------------------------------------
#region Application Functions
#----------------------------------------------
function OnApplicationLoad {
#Note: This function runs before the form is created
#Note: To get the script directory in the Packager use: Split-Path $hostinvocation.MyCommand.path
#Note: To get the console output in the Packager (Windows Mode) use: $ConsoleOutput (Type: System.Collections.ArrayList)
#Important: Form controls cannot be accessed in this function
#TODO: Add snapins and custom code to validate the application load
return $true #return true for success or false for failure
}
function OnApplicationExit {
#Note: This function runs after the form is closed
#TODO: Add custom code to clean up and unload snapins when the application exits
$script:ExitCode = 0 #Set the exit code for the Packager
}
#endregion
#----------------------------------------------
# Generated Form Function
#----------------------------------------------
function GenerateForm {
#----------------------------------------------
#region Import Assemblies
#----------------------------------------------
[void][reflection.assembly]::Load("System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
[void][reflection.assembly]::Load("System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
[void][reflection.assembly]::Load("mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
[void][reflection.assembly]::Load("System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
[void][reflection.assembly]::Load("System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
#endregion
#----------------------------------------------
#region Generated Form Objects
#----------------------------------------------
[System.Windows.Forms.Application]::EnableVisualStyles()
$form1 = New-Object System.Windows.Forms.Form
$textboxNewDBName = New-Object System.Windows.Forms.TextBox
$label5 = New-Object System.Windows.Forms.Label
$buttonCopySQL = New-Object System.Windows.Forms.Button
$label4 = New-Object System.Windows.Forms.Label
$richtextboxLog = New-Object System.Windows.Forms.RichTextBox
$richtextboxSQL = New-Object System.Windows.Forms.RichTextBox
$buttonGenSQL = New-Object System.Windows.Forms.Button
$comboboxDatabases = New-Object System.Windows.Forms.ComboBox
$label3 = New-Object System.Windows.Forms.Label
$textboxBackupSource = New-Object System.Windows.Forms.TextBox
$label2 = New-Object System.Windows.Forms.Label
$textboxServerName = New-Object System.Windows.Forms.TextBox
$label1 = New-Object System.Windows.Forms.Label
$InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState
#endregion Generated Form Objects
#----------------------------------------------
# User Generated Script
#----------------------------------------------
$FormEvent_Load={
#TODO: Initialize Form Controls here
$textboxServerName.text=$ENV:Computername
$textboxBackupSource.Text="R:\MSSQL10.FFLY_DATA\MSSQL\Backup"
#$textboxBackupSource.Text="C:\temp"
$databases= gci $textboxBackupSource.Text | where {$_.Attributes -eq 'Directory'} | select name
#$dbtext=foreach ($database in $databases) {$database.name + "`r`n"}
#$textbox1.Text = $dbtext
foreach ($database in $databases)
{
$comboboxDatabases.Items.Add($database.name)
}
}
$handler_comboboxDatabases_SelectedIndexChanged={
#TODO: Place custom script here
$DBName = $comboboxDatabases.SelectedItem
$textboxNewDBName.Enabled = $true
$textboxNewDBName.Text = "$DBName" + "_$(get-date -uformat "%Y%m%d")"
}
$handler_buttonGenSQL_Click={
#TODO: Place custom script here
IF (!(Get-PSSnapin | where {$_.name -eq "SqlServerProviderSnapin100"}))
{
$LogMessage = "Loading SqlServerProviderSnapin100 `r`n"
add-PSSnapin SqlServerProviderSnapin100
}
IF (!(Get-PSSnapin | where {$_.name -eq "SqlServerCmdletSnapin100"}))
{
$LogMessage += "Loading SqlServerCmdletSnapin100 `r`n"
add-PSSnapin SqlServerCmdletSnapin100
}
$ServerName = $textboxServerName.text
$BackupSource = $textboxBackupSource.Text
$BackupDestination = "F:\Backup"
$SQLDataDir = "E:\SQL"
$LogMessage = "Building SQL script for restore of `"$DBName`" `r`n"
If ($comboboxDatabases.SelectedItem -eq $null)
{
$LogMessage = "You must select a database to restore"
}
Else
{
$SQLDataDrive = $SQLDataDir.Substring(0,2)
$SQLDataFreespace = [Math]::Round($(Get-WmiObject Win32_LogicalDisk -Filter "DeviceId='$SQLDataDrive'").freespace / 1GB, 1)
$LogMessage += "The $SQLDataDrive drive has $SQLDataFreespace GB of freespace `r`n"
$BackupDrive = $BackupDestination.Substring(0,2)
$BackupDriveFreespace = [Math]::Round($(Get-WmiObject Win32_LogicalDisk -Filter "DeviceId='$BackupDrive'").freespace / 1GB, 1)
$LogMessage += "The $BackupDrive drive has $BackupDriveFreespace GB of freespace`r`n"
#Validation Check DB Datestamps to be sure replication is working
$RecentFileCount = $(gci $BackupSource\$DBName |
where {$_.lastwritetime -gt $(get-date).addhours(-4)} | Measure-Object).Count
If ($RecentFileCount -gt 0)
{
$LogMessage += "Repplication seems to be working recent files found `r`n"
}
Else
{$LogMessage += "ERROR ---- Repplication or backup may not be working properly, no files in the last 4 hours`r`n"}
#$NewDBName = "$DBName" + "_$(get-date -uformat "%Y%m%d")"
$NewDBName = $textboxNewDBName.Text
If (Test-Path "SQLServer:\SQL\$ServerName\default\Databases\$NewDBName")
{
$LogMessage = "A Database by this name [$NewDBName] already exists `r`n"
}
Else
#If NewDBName does not exist start to build the SQL Query
{
$LogMessage += "Temporary Database will be called [$NewDBName] `r`n"
#Restore DB
#Find files to restore from
#get the Time of the most recent .bak file
$LastFullBackupTime = $(gci $BackupSource\$DBName *.bak | sort LastWriteTime | select -Last 1).LastWriteTime
$RestoreFileList= GCI $BackupSource\$DBName | Where {$_.LastWriteTime -ge $LastFullBackupTime} | sort LastWriteTime
$FirstRestoreFile = $RestoreFileList | where {$_.Extension -eq ".bak"}
#Validate Logical File Names
#RESTORE FILELISTONLY FROM DISK = N`'$($FirstRestoreFile.FullName)`' "
$LogicalNamesQuery = "RESTORE FILELISTONLY FROM DISK = N`'$($FirstRestoreFile.FullName)`' "
$LogicalNames = Invoke-Sqlcmd -ServerInstance $ServerName -Query $LogicalNamesQuery
$LogicalDBName = $($LogicalNames | Where {$_.Type -eq "D"}).LogicalName
$LogicalLogName = $($LogicalNames | Where {$_.Type -eq "L"} | Select -First 1).LogicalName
$LogMessage += "Logical DBName: $LogicalDBName Logical LogName: $LogicalLogName `r`n"
$RestoreParms = "NORECOVERY, NOUNLOAD, STATS = 10"
#If there is only 1 file available do not leave DB open for more restores
If ($RestoreFileList.Count -eq 1) {$RestoreParms = "NOUNLOAD, STATS = 10"}
$SQLQuery += "RESTORE DATABASE [$NewDBName] FROM DISK = N`'$($FirstRestoreFile.FullName)`' "
$SQLQuery += "WITH FILE = 1, MOVE N`'$LogicalDBName`' TO N`'$SQLDataDir\Data\$NewDBName" + ".mdf`', "
$SQLQuery += "MOVE N`'$LogicalLogName`' TO N`'$SQLDataDir\Log\$NewDBName" + ".ldf`', "
$SQLQuery += "$RestoreParms `r`n`r`n"
$SQLQuery += "GO`r`n`r`n"
$TransactionRestoreFiles = $RestoreFileList | where {$_.Extension -eq ".trn"}
If ($TransactionRestoreFiles)
{
$TransLogCount = $TransactionRestoreFiles.Count
$Counter = $Null
foreach ($File in $TransactionRestoreFiles)
{
$Counter ++
If ($TransLogCount -eq $counter) {$RestoreParms = "NOUNLOAD, STATS = 10"}
$SQLQuery += "RESTORE LOG [$NewDBName] FROM DISK = N`'$($File.FullName)`' WITH FILE = 1, "
$SQLQuery += "$RestoreParms `r`n`r`n"
$SQLQuery += "GO`r`n`r`n"
}
}
#Change DB Recovery model
$SQLQuery += "-------- Set Recovery Model for Restored DB -------- `r`n"
$SQLQuery += "USE master ;`r`n"
$SQLQuery += "ALTER DATABASE $NewDBName SET RECOVERY SIMPLE ; `r`n`r`n"
#Shrink Logs
$SQLQuery += "-------- Shrink Logs -------- `r`n"
$SQLQuery += "GO`r`n`r`n"
#Run mask script
$SQLQuery += "-------- Run mask script -------- `r`n"
$SQLQuery += "GO`r`n`r`n"
#Do DB Backup
$SQLQuery += "-------- Create New Backup -------- `r`n"
$SQLQuery += "GO`r`n`r`n"
#Copy file to QA Envirionment
}
}
$richtextboxLog.Text = $LogMessage
$richtextboxSQL.Text = $SQLQuery
}
$handler_buttonCopySQL_Click={
#TODO: Place custom script here
$richtextboxSQL.SelectAll()
$richtextboxSQL.Copy()
}
#----------------------------------------------
# Generated Events
#----------------------------------------------
$Form_StateCorrection_Load=
{
#Correct the initial state of the form to prevent the .Net maximized form issue
$form1.WindowState = $InitialFormWindowState
}
#----------------------------------------------
#region Generated Form Code
#----------------------------------------------
#
# form1
#
$form1.Controls.Add($textboxNewDBName)
$form1.Controls.Add($label5)
$form1.Controls.Add($buttonCopySQL)
$form1.Controls.Add($label4)
$form1.Controls.Add($richtextboxLog)
$form1.Controls.Add($richtextboxSQL)
$form1.Controls.Add($buttonGenSQL)
$form1.Controls.Add($comboboxDatabases)
$form1.Controls.Add($label3)
$form1.Controls.Add($textboxBackupSource)
$form1.Controls.Add($label2)
$form1.Controls.Add($textboxServerName)
$form1.Controls.Add($label1)
$form1.ClientSize = New-Object System.Drawing.Size(443,461)
$form1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$form1.Name = "form1"
$form1.Text = "SQL Restore"
$form1.add_Load($FormEvent_Load)
#
# textboxNewDBName
#
$textboxNewDBName.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$textboxNewDBName.Enabled = $False
$textboxNewDBName.Location = New-Object System.Drawing.Point(131,95)
$textboxNewDBName.Name = "textboxNewDBName"
$textboxNewDBName.Size = New-Object System.Drawing.Size(149,20)
$textboxNewDBName.TabIndex = 15
#
# label5
#
$label5.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$label5.Location = New-Object System.Drawing.Point(13,95)
$label5.Name = "label5"
$label5.Size = New-Object System.Drawing.Size(100,23)
$label5.TabIndex = 14
$label5.Text = "New DB Name"
#
# buttonCopySQL
#
$buttonCopySQL.Anchor = [System.Windows.Forms.AnchorStyles]::Top -bor [System.Windows.Forms.AnchorStyles]::Right
$buttonCopySQL.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$buttonCopySQL.Location = New-Object System.Drawing.Point(312,261)
$buttonCopySQL.Name = "buttonCopySQL"
$buttonCopySQL.Size = New-Object System.Drawing.Size(99,23)
$buttonCopySQL.TabIndex = 13
$buttonCopySQL.Text = "Copy SQL"
$buttonCopySQL.UseVisualStyleBackColor = $True
$buttonCopySQL.add_Click($handler_buttonCopySQL_Click)
#
# label4
#
$label4.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$label4.Location = New-Object System.Drawing.Point(13,261)
$label4.Name = "label4"
$label4.Size = New-Object System.Drawing.Size(140,23)
$label4.TabIndex = 12
$label4.Text = "SQL Script"
#
# richtextboxLog
#
$richtextboxLog.Anchor = [System.Windows.Forms.AnchorStyles]::Top -bor [System.Windows.Forms.AnchorStyles]::Left -bor [System.Windows.Forms.AnchorStyles]::Right
$richtextboxLog.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$richtextboxLog.Location = New-Object System.Drawing.Point(13,124)
$richtextboxLog.Name = "richtextboxLog"
$richtextboxLog.Size = New-Object System.Drawing.Size(418,129)
$richtextboxLog.TabIndex = 11
$richtextboxLog.Text = ""
#
# richtextboxSQL
#
$richtextboxSQL.Anchor = [System.Windows.Forms.AnchorStyles]::Top -bor [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Left -bor [System.Windows.Forms.AnchorStyles]::Right
$richtextboxSQL.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$richtextboxSQL.Location = New-Object System.Drawing.Point(13,290)
$richtextboxSQL.Name = "richtextboxSQL"
$richtextboxSQL.Size = New-Object System.Drawing.Size(418,159)
$richtextboxSQL.TabIndex = 10
$richtextboxSQL.Text = ""
#
# buttonGenSQL
#
$buttonGenSQL.Anchor = [System.Windows.Forms.AnchorStyles]::Top -bor [System.Windows.Forms.AnchorStyles]::Right
$buttonGenSQL.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$buttonGenSQL.Location = New-Object System.Drawing.Point(312,95)
$buttonGenSQL.Name = "buttonGenSQL"
$buttonGenSQL.Size = New-Object System.Drawing.Size(99,23)
$buttonGenSQL.TabIndex = 7
$buttonGenSQL.Text = "Generate SQL"
$buttonGenSQL.UseVisualStyleBackColor = $True
$buttonGenSQL.add_Click($handler_buttonGenSQL_Click)
#
# comboboxDatabases
#
$comboboxDatabases.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$comboboxDatabases.FormattingEnabled = $True
$comboboxDatabases.Location = New-Object System.Drawing.Point(131,67)
$comboboxDatabases.Name = "comboboxDatabases"
$comboboxDatabases.Size = New-Object System.Drawing.Size(149,21)
$comboboxDatabases.TabIndex = 6
$comboboxDatabases.add_SelectedIndexChanged($handler_comboboxDatabases_SelectedIndexChanged)
#
# label3
#
$label3.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$label3.Location = New-Object System.Drawing.Point(13,67)
$label3.Name = "label3"
$label3.Size = New-Object System.Drawing.Size(119,23)
$label3.TabIndex = 4
$label3.Text = "Select DB for Restore"
#
# textboxBackupSource
#
$textboxBackupSource.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$textboxBackupSource.Location = New-Object System.Drawing.Point(131,40)
$textboxBackupSource.Name = "textboxBackupSource"
$textboxBackupSource.ReadOnly = $True
$textboxBackupSource.Size = New-Object System.Drawing.Size(250,20)
$textboxBackupSource.TabIndex = 3
#
# label2
#
$label2.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$label2.Location = New-Object System.Drawing.Point(13,40)
$label2.Name = "label2"
$label2.Size = New-Object System.Drawing.Size(119,23)
$label2.TabIndex = 2
$label2.Text = "Backup Source:"
#
# textboxServerName
#
$textboxServerName.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$textboxServerName.Location = New-Object System.Drawing.Point(131,13)
$textboxServerName.Name = "textboxServerName"
$textboxServerName.ReadOnly = $True
$textboxServerName.Size = New-Object System.Drawing.Size(100,20)
$textboxServerName.TabIndex = 1
#
# label1
#
$label1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$label1.Location = New-Object System.Drawing.Point(13,13)
$label1.Name = "label1"
$label1.Size = New-Object System.Drawing.Size(100,23)
$label1.TabIndex = 0
$label1.Text = "Server Name:"
#endregion Generated Form Code
#----------------------------------------------
#Save the initial state of the form
$InitialFormWindowState = $form1.WindowState
#Init the OnLoad event to correct the initial state of the form
$form1.add_Load($Form_StateCorrection_Load)
#Show the Form
return $form1.ShowDialog()
} #End Function
#Call OnApplicationLoad to initialize
if(OnApplicationLoad -eq $true)
{
#Create the form
GenerateForm | Out-Null
#Perform cleanup
OnApplicationExit
}
