Home‎ > ‎Software‎ > ‎Microsoft‎ > ‎MS SQL Server‎ > ‎

SQL Restore


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

  

ċ
SQL_Restore.pff
(34k)
Kevin Curran,
Apr 16, 2013, 2:25 PM
Comments