SQL Restore

########################################################################

# 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

}

PS Code Here