SQL Tips for a Successful Rollback

Rolling back can be a very painful experience but it doesn’t need to be. Bad things will happen even if you have tested and planned your deployment. People tend to forget until it is too late that the database is a key piece of the business.

What can we do on the SQL Server side to minimize rollback failures?

1. DBCC CheckDB \ Backup \ Restore
How do these three items affect my rollback?

  • Is the database free of errors?
  • When was the last time you ran DBCC CheckDB or reviewed the results?
  • Are you performing good backups?
  • Are you performing regular restores, the ones from tape?
  • When was the last time you restored the master database or any system databases?

2. Adding a Log Table to your deployment scripts.
A log table, this is a great way to keep track of the deploy script progress. For each progress step insert a comment.

Create table ##LogTable
(rownbr int IDENTITY(1,1),
dt datetime,
comment varchar(250))

INSERT INTO ##LogTable
(dt, comment) values (GETDATE(), ‘Add Your Progress Comments’)

3. Create a Rollback Script
A Rollback Script will undo the changes from the deployment script, for example drop the new objects (table, store procedure, index) that was created.

4. Explicit Transactions
By adding Explicit Transactions (Begin Transaction, Commit and Rollback) your deployment scripts will rollback on any error in your code. I am working on an Explicit Transaction demo for an upcoming blog post.

A few thoughts
No matter how much testing you do, Bad Things Happen.
What takes seconds to run, will take hours to unwind.

Advertisements

Create your own SQL Tools with PowerShell and Windows Forms

By combining PowerShell and Windows Forms you can create your own SQL Tools. From the Windows Form you can execute your SQL and PowerShell scripts with just a couple clicks of a mouse. The added bonus is now your scripts are in a central location that is tailored to your needs.   I have created a base form below so you can get started adding you own scripts. If you’re new to PowerShell save the code below as a ps1 file and then run it from the PowerShell.

Untitled


####################### PowerShell Code ###############################


function CreateForm {
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.drawing


$form1 = New-Object System.Windows.Forms.Form
$button1 = New-Object System.Windows.Forms.Button
$button2 = New-Object System.Windows.Forms.Button
$checkBox1 = New-Object System.Windows.Forms.CheckBox
$checkBox2 = New-Object System.Windows.Forms.CheckBox
$InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState

#Form Parameter
$form1.Text = “My PowerShell Form”
$form1.Name = “form1”
$form1.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 550
$System_Drawing_Size.Height = 150
$form1.ClientSize = $System_Drawing_Size

#Add Label and TextBox
$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,25)
$objLabel.Size = New-Object System.Drawing.Size(110,50)
$objLabel.Text = “Enter Server Name”
$form1.Controls.Add($objLabel)
$objTextBox = New-Object System.Windows.Forms.TextBox
$objTextBox.Location = New-Object System.Drawing.Size(120,25)
$objTextBox.Size = New-Object System.Drawing.Size(200,20)
$form1.Controls.Add($objTextBox)

#Button 1 Action
$button1_RunOnClick=
{
if ($checkBox1.Checked) { SQLVersion }
if ($checkBox2.Checked) { LastReboot }
}

#Button 2 Action
$button2_RunOnClick=
{
if ($checkBox1.Checked) {$checkBox1.CheckState = 0}
if ($checkBox2.Checked) {$checkBox2.CheckState = 0}
}

$OnLoadForm_StateCorrection=
{
$form1.WindowState = $InitialFormWindowState
}

#Button 1
$button1.TabIndex = 4
$button1.Name = “button1”
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 75
$System_Drawing_Size.Height = 23
$button1.Size = $System_Drawing_Size
$button1.UseVisualStyleBackColor = $True
$button1.Text = “Run”
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 350
$System_Drawing_Point.Y = 25
$button1.Location = $System_Drawing_Point
$button1.DataBindings.DefaultDataSourceUpdateMode = 0
$button1.add_Click($button1_RunOnClick)
$form1.Controls.Add($button1)


#Button 2
$button2.TabIndex = 4
$button2.Name = “button2”
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 150
$System_Drawing_Size.Height = 23
$button2.Size = $System_Drawing_Size
$button2.UseVisualStyleBackColor = $True
$button2.Text = “Clear CheckBox”
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 350
$System_Drawing_Point.Y = 60
$button2.Location = $System_Drawing_Point
$button2.DataBindings.DefaultDataSourceUpdateMode = 0
$button2.add_Click($button2_RunOnClick)
$form1.Controls.Add($button2)
#SQLVersion
$checkBox1.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox1.Size = $System_Drawing_Size
$checkBox1.TabIndex = 0
$checkBox1.Text = “SQL Version”
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 80
$checkBox1.Location = $System_Drawing_Point
$checkBox1.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox1.Name = “checkBox1”
$form1.Controls.Add($checkBox1)

#LastReBoot
$checkBox2.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox2.Size = $System_Drawing_Size
$checkBox2.TabIndex = 1
$checkBox2.Text = “Last Reboot”
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 175
$System_Drawing_Point.Y = 80
$checkBox2.Location = $System_Drawing_Point
$checkBox2.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox2.Name = “checkBox2”
$form1.Controls.Add($checkBox2)

#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($OnLoadForm_StateCorrection)
#Show the Form
$form1.ShowDialog()| Out-Null
} #End function CreateForm

function Invoke-Sqlcmd3
{
param(
[string]$Query,
[string]$Database=”tempdb”,
[Int32]$QueryTimeout=30
)
$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString=”Server={0};Database={1};Integrated Security=True” -f $Server,$Database
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables[0]
}

Function SQLVersion
{
[string]$SQLVersion = @”
SELECT @@Version
“@
$Server = $objTextBox.text
Invoke-Sqlcmd3 -ServerInstance $Server -Database Master -Query $SQLVersion | Out-GridView -Title “$server SQL Server Version”
}

Function LastReboot
{
$Server = $objTextBox.text
$wmi = Get-WmiObject -Class Win32_OperatingSystem -Computer $server
$wmi.ConvertToDatetime($wmi.LastBootUpTime) | Select DateTime | Out-GridView -Title “$Server Last Reboot”
}
#Call the Function
CreateForm


####################### PowerShell Code ###############################

Welcome

Welcome to my blog. I like to keep asking myself how does that work and why and then go find the answer. At the same time learning should be fun. I hope that you enjoy my posts. My coding motto is keep it SIMPLE! My primary focus will be on SQL Server, PowerShell, Visual C#, Python and anything related to data and technology.