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
$dt
# 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
"Connected"
$cmd = new-Object System.Data.SqlClient.SqlCommand("dbo.usp_InsertFailedBackups", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
#SQLParameter
$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.

BEGIN

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)
SELECT
 msdb.dbo.backupset.database_name,
 MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
 DB_ID(database_name)
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'
GROUP BY
 msdb.dbo.backupset.database_name
ORDER BY
 msdb.dbo.backupset.database_name

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

Create Table

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

CREATE TABLE [dbo].[FailedBackups](
[ServerName] [varchar](250) NULL,
[DatabaseName] [varchar](max) NULL,
[DateCaptured] [datetime] NULL,
[last_db_backup_date] [datetime] NULL
) ON [PRIMARY]

GO

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.

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
)
GO

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.

CREATE PROCEDURE [dbo].[usp_InsertFailedBackups] @TVP FailedBackups readonly
AS
BEGIN

INSERT INTO dbo.FailedBackups (ServerName,DatabaseName, DateCaptured, last_db_backup_date)
SELECT  ServerName,
DatabaseName,
GETDATE(),
last_db_backup_date
FROM    @TVP
END

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.

USE [msdb]
GO

/****** Object:  Job [Daily Checklist - Failed Backups]    Script Date: 05/11/2011 23:43:52 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
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)
BEGIN
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

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Daily Checklist - Failed Backups',
 @enabled=1,
 @notify_level_eventlog=0,
 @notify_level_email=0,
 @notify_level_netsend=0,
 @notify_level_page=0,
 @delete_level=0,
 @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',
 @step_id=1,
 @cmdexec_success_code=0,
 @on_success_action=1,
 @on_success_step_id=0,
 @on_fail_action=2,
 @on_fail_step_id=0,
 @retry_attempts=0,
 @retry_interval=0,
 @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',
 @database_name=N'master',
 @flags=0
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
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO