Passed my #SQLUniversity PowerShell Midterm!

I am always up for learning something new so I jumped in on the free PowerShell training at SQL University. I saw that Aaron Nelson gave me the tools to loop through a Central Management Server and I was inspired. My main motivation came from learning that Policy-Based Management isn’t the ideal solution for evaluating a daily checklist. I started to notice that the time needed to evaluate full backups started to grow as more databases were added to the farm.

I passed the #SQLU exam and got extra credit!

With the following framework you can automate the process of collecting data from the system tables and DMV’s. In this example we are going to focus on automating the process of collecting failed full backups within the last 24 hours.

NOTE: Remember, you could apply this framework to automate the process of collecting any data from the system tables or DMV’s.

Below is the framework steps: (Steps 1 – 3 are need only for the first implementation)

  1. Configure your Central Management Server (We will assume you have this completed)
  2. Configure your server to run PowerShell with SQL. (Go to PowerShell week at SQLUniversity for the guide)
  3. Get and import the out-DataTable PowerShell function
  4. Create and save T-SQL query you want to run against your SQL farm.
  5. Create table to store results
  6. Create table type required with Table Value Parameters
  7. Create stored procedure to insert DataTable
  8. Automate with SQL Agent Job

PowerShell – Find failed backups in the last 24 hours.

In general the following block of PowerShell will allow you to collect results for any query executed against your SQL Farm with the Central Management Server. With that said, there are a few lines that will need to explain so it can be modified for the code to work in your shop.

  1. On line #1, the CMS for this example is PBMDEMO. You will need to switch this to your CMS server.
  2. On line #4, the T-SQL file is named “Missing Backups.sql” and its located at C:\Demos\CMS_PBM\Scripts\T-SQL. You will need to change this to T-SQL file you want to execute against your SQL farm.
  3. On line #7, the server name and database will need to be changed to your daily checklist repository. In this example I use PBMDEMO and PolicyManagement.
  4. On line #10, the stored procedure name will need to be the name for your stored procedure to input your collected date using Table Value Parameters.
  5. On line #17, The table type will need to match your table type used to strongly type the table parameter in your stored procedure (more on this below)

We will save the PowerShell script as FailedBackups.ps1 in the C:\demos\CMS_PBM\Scripts\PowerShell folder so the SQL Agent job will just load this file when we automate the data collection solution.

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Central Management Server Group'\PBMDEMO\ | where {$_.Mode -ne "d"} )

$dt = Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "tempdb" -InputFile "C:\Demos\CMS_PBM\Scripts\T-SQL\Missing backups.sql" | out-DataTable
# Write data table to database using TVP
$conn = new-Object System.Data.SqlClient.SqlConnection("Server=PBMDEMO;DataBase=PolicyManagement;Integrated Security=SSPI")
$conn.Open() | out-null
$cmd = new-Object System.Data.SqlClient.SqlCommand("dbo.usp_InsertFailedBackups", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$spParam = new-Object System.Data.SqlClient.SqlParameter
$spParam.ParameterName = "@TVP"
$spParam.Value = $dt
$spParam.SqlDbType = "Structured" #SqlDbType.Structured
$spParam.TypeName = "FailedBackups"

$cmd.Parameters.Add($spParam) | out-Null
$cmd.ExecuteNonQuery() | out-Null
$conn.Close() | out-Null

T-SQL Query to find failed backups. You will need to save it as “Missing Backup.sql” and place it in the C:\Demos\CMS_PBM\Scripts\T-SQL folder or modify the second line of code in the PowerShell example above to have the correct name and path.

[sourcecode language=”sql”]

DECLARE @BackupDB TABLE (database_name varchar(255), last_db_backup_date datetime, database_id integer)
INSERT INTO @BackupDB (database_name, last_db_backup_date, database_id)
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
FROM   msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  msdb.dbo.backupset.type = ‘D’

SELECT SERVERPROPERTY(‘ServerName’) AS ServerName, name AS DatabaseName, last_db_backup_date
FROM master.dbo.sysdatabases  d LEFT JOIN @BackupDB b ON (DB_ID( = b.database_id)
WHERE (last_db_backup_date < DATEADD(hh, -24, getdate())
OR b.last_db_backup_date IS NULL)
AND dbid > 4

Create Table

The following table will be used to save the results for running the above t-sql query against the SQL Server farm.

[sourcecode language=”sql”]
CREATE TABLE [dbo].[FailedBackups](
[ServerName] [varchar](250) NULL,
[DatabaseName] [varchar](max) NULL,
[DateCaptured] [datetime] NULL,
[last_db_backup_date] [datetime] NULL



Create Table Type

The table type is used as a requirement of using Table Value Parameters. This allows the stored procedure to know what table you are trying to pass to the stored procedure. The last five columns are required by the out-DataTable powershell function.

[sourcecode language=”sql”]
CREATE TYPE [dbo].[FailedBackups] AS TABLE(
[ServerName] [varchar](250) NOT NULL,
[DatabaseName] [varchar](max) NOT NULL,
[last_db_backup_date] [datetime] NULL,
[RowError] [varchar](1000) NULL,
[RowState] [varchar](1000) NULL,
[Table] [varchar](1000) NULL,
[ItemArray] [varchar](1000) NULL,
[HasErrors] [varchar](1000) NULL


Create Insert Stored Procedure

The following stored procedure leverages Table Value Parameters to input a DataTable and do a insert select statement to populate the table.

[sourcecode language=”sql”]
CREATE PROCEDURE [dbo].[usp_InsertFailedBackups] @TVP FailedBackups readonly

INSERT INTO dbo.FailedBackups (ServerName,DatabaseName, DateCaptured, last_db_backup_date)
SELECT  ServerName,


Automate Data Collection with SQL Agent and PowerShell

If you have used the Central Management Server you will notice quickly that you cannot schedule a query to run agains your farm. An easy work around is to use PowerShell to run your t-sql via a SQL Agent Job. Below is an simple scripted example that does that.

[sourcecode language=”sql”]
USE [msdb]

/****** Object:  Job [Daily Checklist – Failed Backups]    Script Date: 05/11/2011 23:43:52 ******/
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/11/2011 23:43:52 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Daily Checklist – Failed Backups’,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [PowerShell – Find Failed Backups]    Script Date: 05/11/2011 23:43:53 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’PowerShell – Find Failed Backups’,
@os_run_priority=0, @subsystem=N’PowerShell’,
@command=N’. C:\Demos\CMS_PBM\Scripts\PowerShell\out-DataTable.ps1
. C:\Demos\CMS_PBM\Scripts\PowerShell\FailedBackups.ps1′,
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave



  • Congratulations!!! Between this and a baby, seems like it’s been a pretty good year for you!

  • Thanks Claire, It has been a very good year for me. I have been blesses with a great family and a great community (SQLPASS). I still cannot believe I am doing a spotlight session at SQLRally tomorrow.
    I also have an exciting announcement to make in the near future that adds the icing to the cake for my year. 

  • Paulus Tandang

    Hi John,

    I ran the code to create table type

    CREATE TYPE [dbo].[FailedBackups] AS TABLE(
    [ServerName] [varchar](250) NOT NULL,
    [DatabaseName] [varchar](max) NOT NULL,
    [last_db_backup_date] [datetime] NULL,
    [RowError] [varchar](1000) NULL,
    [RowState] [varchar](1000) NULL,
    [Table] [varchar](1000) NULL,
    [ItemArray] [varchar](1000) NULL,
    [HasErrors] [varchar](1000) NULL

    and got this error

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘AS’. Do you know what I am missing?


  • Paulus Tandang

    Well, I found my answer, it does not work in SQL 2005, work only in SQL 2008, thanks.

  • Paulus,
    You are correct I am using Table Value Parameters which are new features in SQL Server 2008. You can do a work around by using custom powershell functions. I decided to do TVP instead of Add-SqlTable, Write-DataTable and out-DataTable because they added extra columns.

    You can do a google search on those functions or check out Aaron Nelson’s blog I believe he has an example. If that doesn’t work shoot me another email an I can forward an example Aaron sent me in the past 🙂

  • FYI…this article needs to be updated, this dont work several things changed since it was released

  • Vinny

    Hi There, thanks for the code. I am however running into an error message (even thought the table is being populated with data)

    Exception calling “ExecuteNonQuery” with “0” argument(s): “Column, parameter, or variable #1: Cannot find data type Fa


    At line:18 char:21

    + $cmd.ExecuteNonQuery <<<< () | out-Null

    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : DotNetMethodException

    any ideas?

  • lcerni

    I have followed your instructions and have gotten through most of my errors but I don’t understand this one:
    Executed as user: NT ServiceSQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 2 in a PowerShell script. The corresponding line is ‘foreach ($RegisteredSQLs in dir -recurse SQLSERVER:SQLRegistration’Central Management Server Group’CMSNameHere | where {$_.Mode -ne “d”} ) ‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot find path ‘SQLSERVER:SQLRegistrationCentral Management Server GroupCMSNameHere’ because it does not exist. ‘. Process Exit Code 0. The step succeeded.
    Under Central Management Servers I have created CMSNameHere and under that I have created two folders DevelopmentQA and Production and under those two folders I have added one server.