2,560

Two Thousand Five Hundred Sixty. Seems like such a random and unassuming number, however, I can say this was the most disturbing number on the spectrum of disturbing numbers over the past few weeks. The numbers 13 and 666 were child’s play compared to the stress that 2,560 created.

What was this number you say? It was the number of unread messages I had in my personal mailbox the day after I took the Microsoft MCM SQL 2008 Lab exam. Every morning, I would wake up and see a number greater than 2,560 and internally the anticipation would begin. Would any of these new and foreign messages be the one I had been waiting for? Would it begin with “Congratulations” or would it begin with “Unfortunately”. Would this message mark a successful milestone on the already extended five year plan or would this be another speed bump to be planned around and overcome?

On Friday, October 19, 2012 the waiting was over. 2,560 turned to 2,561 one more time. This time was different. Instead of being the latest Amazon Local, SQL Central Newsletter, RedWing special, or a random SPAM, it was a message from boB Taylor (@SQLboBT) letting me know that I had passed one of the most feared exams in the SQL Server community – on the first attempt. The sky parted, the angels sang, and I gave my wife a big hug (I might have cried a little) .

The MCM was the culmination of a 5 year plan that has taken 6 years complete. It was step 5 preceded by working from home (started full time July 2012 – Thanks PTI!), obtaining Bachelor’s degree (complete February, 2012), selling my house (complete September 2010), and becoming a leader in the local SQL Server user group (complete July 2009).

Those close know – smooth has not been the norm while working through this plan. In the last six years, those three kids turned into five. While we did sell our house – we did so in unexpected timing – leaving us to move four times in the last three years. Add on to that - I’m on my 3rd employer since starting this path – but at least these guys are awesome.

Even the process of taking the test was not without issue. The power went out while I was taking my Knowledge exam. I had to wait three hours for the power to come back on and was able to complete the exam without having to make a second trip to Chicago. Little did I know that the process of taking the Lab exam would be even more painful. I drove to Chicago the night before, I got to the testing center early – got into the room – and waited for the test to begin. About five minutes later – it timed out. After four hours, the friendly staff thought they were ready, but the system was not. After sitting another hour – the system timed out again and I left Chicago not having opportunity to take the test. Fortunately, the next time I drove up to Chicago (two weeks later), the test worked and I was able to successfully take the test.

Through all of this – I’ve kept a secret in my back pocket. The secret - my beautiful and magnificent wife – Stephanie – who has been a blessed encouragement, a stable partner, and a hard working mother beside me each step of the way. We’ve taken a lot of hits and she’s still standing right there next to me. Without her I would have melted into a pool of 1s and 0s many months ago. I am thankful to be granted her wisdom, patience, and magnificence on a daily basis.

Looking back at all of the challenges – both personal and professional – I think I’ve stumbled upon what it means to be a Master. Setting a goal, facing adversity with a smile and pushing through until the goal is complete – all with the help of wise mentors, friends, and family. Rinse and Repeat.

While I can now declare that I am a Microsoft Certified Master and value this achievement dearly, this is not the end of the journey. A friend once told me that having an exceptional family means that I have to have an exceptional career as well. My family may not be the fastest, smartest, or the most good looking – but we are exceptional in many ways. Here’s to the continuing journey of building an exceptional career as my exceptional family navigates through life.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server MCM, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Vacation and SQLSat149

When I was considering submitting some sessions to SQL Saturday 149 in Minnesota I thought I would have a difficult time selling it to my family. It’s been very busy around here and I didn’t know how another trip may have been received. Much to my surprise, when I mentioned to my beautiful wife she thought it was a great idea! In fact, we decided that if any of my sessions were chosen that it would be a whole family trip.

Once I found out that the organizing committee was giving me an opportunity to talk about Locks, Blocks, and Deadlocks, the planning began. We’re all heading to the twin cities next week for a welcome break from the ordinary. It’s been an intense couple of years and we are all looking forward to a fun trip.

If you’re in Minneapolis next weekend – stop by the University of Minnesota and hear some awesome presentations. Minneapolis – be prepared. Not only is the #SQLFamily coming to town – so is the entire Neier family. All I can say is Mt. Olympus, Mall of America, and Oracle – you have all been warned!

Look forward to seeing everyone there!

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

T-SQL: Performance of SQL Agent Duration Calculations

In my article published yesterday on using Powershell for SQL Durations (Working with SQL Agent Durations), I proposed using a combination of math functions to extract the durations of SQL Agent jobs in a dependable and accurate manner. While the article sought to provide a better alternative for those using Powershell, the discussion of the article was primarily about T-SQL solutions. The question of both capability and performance were both discussed, so I decided to research the limitations and performance metrics of the various solutions posted.

A combination of string and math based solutions were posted as potential alternatives. A recent post highlighted the use of the msdb function agent_datetime. All of these math functions posted will provide an accurate second value from a SQL Agent duration without regard to sign (positive or negative) or size (greater than 6 digits).

All of the string methods mentioned, including the agent_datetime function, rely on three facts to be true.

1) All values in the table must either be zero or positive integers
2) No value greater than 235959 can exist in the table because of datetime conversion issues.
3) Positions 2-3 and Positions 4-5 must not be greater than 60 due to datetime conversion issues.

While I understand that the existence of any of these values is a sign of a larger issue – if the code generating the report fails, the report is incapable of telling me there is a problem. I generally run reports on new environments to understand what the current state is and what needs to be worked on. It is not uncommon to find jobs that have run more than a day in a new environment. While less common, I have also had to deal with negative duration values stored in the sysjobhistory. I agree – neither is ideal and neither should exist. However, it is much more common for me to be called to fix what is broken, not to watch an already stable and perfect environment.

Testing Performance

I have never actually tested the performance of using the math based solution vs. the string manipulation solutions. So, I devised a process to put all of these suggestions to a performance test. I first created a table with integers –1971000 through 1971000. I understand this is extreme, in both directions, but I had to use something large enough to get some idea of performance.

You can examine the T-SQL at the end of this post. At each test, the T-SQL represents a proposed solution on the forum thread. Each test converts each of the integer values in the table to a number of seconds based upon the HHMMSS rule of how these durations are stored in sysjobhistory. These are populated into a variable so that any transfer to the client or IO issues are eliminated in the performance test. The only thing being tested should be CPU and memory throughput. In order to adjust for the performance of any given environment, I establish a control. This control does no conversion on the integer value, simply selects each one into a variable. This should provide a good baseline for what the maximum performance could be.

Given that each of the string functions proposed fail unless provided very specific numeric values, I eliminated any negative values, any values > 235959, and any values where the HH and SS values were > 59 to avoid datetime conversion errors.

Below is the average of each of these methods (and a control) over 10 executions for both my laptop and an enterprise class server.

	AvgDurationMS
RunType	Laptop	Server
235959 Control	60	27
235959 Mod Math Implicit	87	38
235959 Mod Math	90	44
235959 Mod Math Intrope	123	44
235959 SQL-Tucker	163	79
235959 rmechaber	182	90
Full Control	983	406
235959 agent_datetime	1516	658
Full Mod Math	1755	790
Full Mod Math Implicit	1814	867
Full Mod Math Intrope	2377	1077

Admittedly, the difference in these test scenarios is negligible. However, in all cases, using the mod operator and the implicit truncation within SQL Server was the fastest conversion method. In addition to being the fastest – it’s the most compact visibly and works for just about any values – not just those less than a single day. I encourage you to run these tests in your environment and see which solution comes out on top.

As with anything in SQL Server, when asked which method is “better” I would have to say “It depends.” However, if you ask which appears to be fastest, which works for most conceivable situations, and which will I use? I’m going to stick with math – and leave the strings be.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

T-SQL Used to Test

USE tempdb
GO
/*
Adapted from 
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable&referringTitle=Home
*/
-- Suppress data loading messages
SET NOCOUNT ON

-- Create Sample Data using a Table Varable
/*
    Positive and Negative values
*/
SELECT TOP 3942000 IDENTITY(INT,-1971000,1) AS N
INTO Numbers
FROM sys.all_objects a, sys.all_objects b, sys.all_objects c, sys.all_objects d

-- Create a Primary Key and Clustered Index to control data order
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N) 
GO

CREATE TABLE DurationMetrics(
RunType VARCHAR(100) NOT NULL, StartTime DATETIME NOT NULL, EndTime DATETIME NOT NULL)
GO
DECLARE @StartTime DATETIME,
@EndTime DATETIME,
@DurationSeconds int

DECLARE @Loops INT = 10,
@i INT


/*Control*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds = N
FROM Numbers
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT 'Control' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END

/*Mod Math*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds =  
FLOOR(N/10000) * 3600 +
FLOOR(N/100%100) * 60 +
N%100 
FROM Numbers
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT 'Mod Math' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END

/*Mod Math Implicit*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds =  
N/10000 * 3600 +
N/100%100 * 60 +
N%100 
FROM Numbers
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT 'Mod Math Implicit' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END

/*Mod Math Intrope*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds =  
((N - N % 10000) /10000) * 60 * 60 
+ ((N % 10000 - N % 100) /100) * 60
+ N % 100
FROM Numbers
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT 'Mod Math Intrope' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END


/*235959 Control*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds = N
FROM Numbers WHERE N BETWEEN 0 AND 235959
AND N%100 < 60 AND N/100%100 < 60
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT '235959 Control' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END

/*235959 SQL-Tucker*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds = 
datediff(ss,0,cast(stuff(stuff(left('000000',6-len(N))+cast(N AS VARCHAR),5,0,':'),3,0,':') AS DATETIME))
FROM Numbers WHERE N BETWEEN 0 AND 235959
AND N%100 < 60 AND N/100%100 < 60
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT '235959 SQL-Tucker' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END

/*235959 Mod Math*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds =  
FLOOR(N/10000) * 3600 +
FLOOR(N/100%100) * 60 +
N%100 
FROM Numbers WHERE N BETWEEN 0 AND 235959
AND N%100 < 60 AND N/100%100 < 60
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT '235959 Mod Math' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END

/*235959 Mod Math Implicit*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds =  
N/10000 * 3600 +
N/100%100 * 60 +
N%100 
FROM Numbers  WHERE N BETWEEN 0 AND 235959
AND N%100 < 60 AND N/100%100 < 60
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT '235959 Mod Math Implicit' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END

/*235959 Mod Math Intrope*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds =  
((N - N % 10000) /10000) * 60 * 60 
+ ((N % 10000 - N % 100) /100) * 60
+ N % 100
FROM Numbers WHERE N BETWEEN 0 AND 235959
AND N%100 < 60 AND N/100%100 < 60
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT '235959 Mod Math Intrope' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END

/*235959 rmechaber*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds =  
LEFT(RIGHT('000000' + CAST(N AS VARCHAR(6)), 6), 2) *3600 +
Substring(RIGHT('000000' + CAST(N AS VARCHAR(6)), 6), 3, 2) *60 +
RIGHT(RIGHT('000000' + CAST(N AS VARCHAR(6)), 6), 2)
FROM Numbers WHERE N BETWEEN 0 AND 235959
AND N%100 < 60 AND N/100%100 < 60
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT '235959 rmechaber' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END

/*235959 agent_datetime*/
SELECT @i = 0

WHILE @Loops > @i
BEGIN
SELECT @StartTime = GETDATE()
SELECT @DurationSeconds =  
datediff(s,msdb.dbo.agent_datetime(19000101,0),msdb.dbo.agent_datetime(19000101,N))
FROM Numbers WHERE N BETWEEN 0 AND 235959
AND N%100 < 60 AND N/100%100 < 60
SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)
SELECT '235959 agent_datetime' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime
END



SELECT RunType, 
AVG(DATEDIFF(ms, StartTime, EndTime)) AS AvgDurationMS,
MIN(DATEDIFF(ms, StartTime, EndTime)) AS MaxDurationMS,
MAX(DATEDIFF(ms, StartTime, EndTime)) AS MaxDurationMS
FROM DurationMetrics
GROUP BY RunType
ORDER BY AvgDurationMS

/*Clean Up*/
DROP TABLE DurationMetrics
DROP TABLE Numbers

Nacho Mama’s Training Class

That is what I wanted to name the new training series that we developed at my employer, Perpetual Technologies (PTI.net). Unfortunately, I couldn’t convince anyone to change their name to Nacho Mama, but I digress…

On August 22 and August 23, PTI is offering a two day workshop on SQL Server Performance and Troubleshooting. You can read more about it here as well as download a flier to share with your co-workers and friends.

I will be delivering three of the modules – “SQL Server Performance Analysis”, “Indexing for Performance”, and “Locks, Blocks, and Deadlocks”. Hope Foley (blog | twitter) will be delivering three modules – “SQL Instance Configuration”, “Basics of Database Maintenance” and “System Performance Analysis”. Arie (AJ) Jones (blog | twitter) will be delivering two of the modules – “Index Overview” and “Query Execution Plans”.

We’ve written original content for these sessions – all of it based upon experience from the front lines as we work with SQL Server on a daily basis. These couple of days promise to be exciting and full of SQL Server goodness.

If you are interested, you can get registration info here.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

SQL Saturday 122: T-SQL 2012 presentation

This last weekend, I had the pleasure of being invited down to Louisville, KY to speak at SQL Saturday #122. The volunteers there chose to have me speak on some of the new features of SQL Server 2012 T-SQL.

I barely fit everything in the session – finishing up just as time was over. I was unable to give the windowing functions the time they deserved as one of the coolest features of T-SQL 2012. However, most of the demos worked and it seemed that the audience understood what I was trying to communicate and participated quite well. I had a blast hanging out with Jack Corbett (@unclebiguns), Eddie Wuerch (@EddieW), Craig Purnell (@CraigPurnell) and several other SQL Server experts.

I’ve uploaded the script files to the SQL Saturday website if you want to go through any of the demos for yourself.

Please feel free to hit me up with questions both here and on Twitter.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Speaking at SQL Saturday 122 - Louisville

It has been a great couple of months for both me and for Perpetual Technologies (my employer). PTI has been able to sponsor several SQL Saturday events and we have had several members of the Microsoft team speak at numerous events.

I've been granted the pleasure to speak at SQL Saturday 122 in Louisville - just a couple hours south of home. I'll be speaking on some of the new features in SQL Server 2012 that relate to T-SQL. I'll be touching on some of the new .NET functions that are now built in to the engine, new ways to do error control, and wrapping up with some of the awesome windows functions introduced in T-SQL 2012. You can read the abstract here.

Get signed up here and get the opportunity to share, network, and learn from some local and national experts on SQL Server topics. It's only a couple weeks away - do not pass go, do not collect $200. Sign up today!

Look forward to seeing you there!

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Powershell Add-Member: IsLogBackupAllowed ?

In my previous post, I shared some of the issues I had when attempting to get Add-Member to work with the SMO Database object. In this post, I’ll share some of the fruits of that experience as I show how to quickly determine if a database can have its log backed up through Powershell and SMO.

That’s Easy – just check the recovery model.

I could just say – yup – blog post done. However, it’s not. Consider the following code:

$instancename = ".\SQL2008R2"
$dbname = "TestFullRecovery"

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server ("$InstanceName")
$Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup

$db = $srv.Databases["$dbname"]

if($db.RecoveryModel -eq "Full")
{
    #Take log backup of database only if the database is in full recovery        
    $Backup.Database = $db.name
    $Backup.Action = "Log"
    $Backup.Devices.AddDevice("C:\BAK\TestFullRecovery_log.trn", "File")
    try{$Backup.SqlBackup($srv)}
    catch
    {
        throw $_.Exception.GetBaseException()
    }
}

If you run this on a database that is in Full recovery mode but has not had a full backup, the following error will be thrown:

BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.

This is because the database is in what is known as “pseudo-SIMPLE” mode. Paul Randal describes this issue here as he offers a T-SQL function to determine whether or not a database is really in Full Recovery mode. This can happen if a new database is created and not backed up yet or if the database has recently been switched into Full recovery mode without a subsequent full backup. If you are running in an environment where the application generates new databases like mushrooms, this can become an important issue to resolve.

One option if you are using SMO and Powershell for backups is that you could just catch the error and ignore if the error message is identical to that described above. I do not generally like relying on this type of exception handling – why knowingly execute code that will fail. Let's look at a more proactive solution.

Adding the IsLogBackupAllowed Property

Using Add-Member, we can add a new boolean property to the database object. In this case - instead of adding it as a literal value with the NoteProperty MemberType, the ScriptProperty MemberType allows us to create a property that can be dynamic at run time. Using the special variable "$this" within the construction of the property allows the value to be determined based upon other properties that already exist on the object as well as executing methods of the object to which this new property belongs.

I decided to use the name of “IsLogBackupAllowed” for this new property. This name seemed to fit in well with the other boolean properties of the database object like IsDatabaseSnapshot or IsMirroringEnabled. Leveraging the –PassThru parameter of Add-Member, we can create a simple function that will bring the functionality that Paul Randal created in T-SQL into Powershell. When referencing this function - it will return back the object you put into it with the addition of the IsLogBackupAllowed property.

$instancename = ".\SQL2008R2"

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server ("$InstanceName")

function Add-DBMembers 
{
    param(
        [Microsoft.SqlServer.Management.Smo.Database]$Database
    )
    
    $db | Add-Member -Name IsLogBackupAllowed -MemberType ScriptProperty `
        -Value {      
      if($this.Parent.Version.Major -ge 9)
      {
            if(([Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full,
                [Microsoft.SqlServer.Management.Smo.RecoveryModel]::BulkLogged)`
                  -contains $this.RecoveryModel)
            {
            if($this.ExecuteWithResults(
                  [string]::Format("SELECT last_log_backup_lsn 
                          FROM sys.database_recovery_status
                        WHERE database_id = {0}", $this.ID)
                        ).Tables[0].Rows[0].last_log_backup_lsn.GetType() -ne `
                            [System.DBNull])
                  {
                        $true
                  }else{$false}
            }else{$false}
      }else{$null}
    } -PassThru
    
}
$db = $srv.Databases['TestFullRecovery']

$db = Add-DBMembers -Database $db

$db.IsLogBackupAllowed

The function adds a ScriptProperty of IsLogBackupAllowed to a database object that is provided as a parameter. To determine whether the transaction log backup is allowed, the script first determines if the RecoveryModel property of the database is either Full or BulkLogged. If the database is in Simple recovery mode, there is no chance of getting a log backup. Once it is determined that the database could potentially have a log backup taken, the ExecuteWithResults method is implemented to use the same select from Paul’s post to determine the ability of backing up the log. If anything other than “DBNull” is returned for the column status, a value of True is sent to the caller. If the instance is not SQL 2005 or higher, this property returns $null . SQL 2000 does not make this metadata readily available.

But wait – there’s more!

If you’re asking yourself, That’s fine and dandy, but all the work I’m doing in the Add-Member could be done inline when taking log backups.  What's with all the additional work of making it a property? I'll return your question with a question.

What if the Add-Member step was not necessary? What if you could just reference $db.IsLogBackupAllowed in your code? In the next post, I’ll be showing you how this can be accomplished using the Types.ps1xml files as well as showing how to extend various SMO objects with properties that I have found to be handy in routine maintenance tasks.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter