• Steve Jones - SSC Editor - Tuesday, February 13, 2018 9:25 AM

    Eric,

    Do you track some logins, Linked servers, with this as well? Those server level objects are often just maintained by DBAs, but easy to forget that audits, XE sessions, DDL triggers, etc. need to be captured for DR purposes is for no other reason.

    I'm referencing objects collected under the .NET namespace: Microsoft.SqlServer.Management.Smo.Server

    https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx

    Currently: databases, backup devices, server level triggers, end points, SQL Agent properties, jobs, linked servers, logins, alerts, and operators. There is so much more it could be scripting to be a complete solution, and I need to go back and flesh this out even more. The Git repository contains folders based on server name and then beneath each server are folders for each object type, having one separate .sql script for each object.

    In addition to being a comprehensive versioned archive of what's currently in (or has been) in production, from a monitoring and auditing perspective it can also be used to keep tabs on changes (planned or unplanned), because each commit represents some type of modification to an object since the last time the PowerShell job ran, which is scheduled nightly.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho