Who restored those databases?!

Working an upgrade with a customers right now, from SQL Server v2005 to v2012. The bulk of the work is done, but I am still rebuilding an ETL project for them. The new VS solution is in place, I've upgraded 50+ packages successfully, and now I just need to build the Agent job that will kick off the ETL each day. Everything is progressing very well, but... today I was surprised!  

I get into SSMS, SQL Server Agent\Jobs, and find a TON of jobs there that I know nothing about! The only thing I can tell for sure is that they are SSRS subscriptions. You know, those crazy job names like this:

That tells me ReportServer, but remember, this is a new box.  Not production-ready yet. I didn't even know there was a ReportServer database until now. Hence, my surprise. This post is just a quick collect on the last database restore details from msdb..restorehistory, if exists.

    /* get the most recent restore details from your databases */

    WITH LastRestore AS

    (

    SELECT

       sd.[name] [Database],

       rh.restore_date [RestoreDate],

       rh.destination_database_name [RestoredTo],

       rh.user_name [RestoredBy],

     RowNum = ROW_NUMBER() OVER (PARTITION BY sd.Name ORDER BY rh.[restore_date] DESC)

    FROM

       master.sys.databases sd INNER JOIN msdb.dbo.[restorehistory] rh

         ON sd.[name] = rh.[destination_database_name]    )

    -- bring back the details

    SELECT

       [Database],

       RestoreDate,

       RestoredTo,

       RestoredBy

    FROM

       LastRestore

    WHERE 

       RowNum = 1

       AND RestoreDate IS NOT NULL

This pic is from my customer's instance..though I've blacked out RestoredBy.  :-)

And this one is from my own instance, just so you can see the full output:

      

I've actually posted this before here, but it was for another purpose, and I didn't include in the output who actually did the deed. Pretty helpful to know who's doing what.  We all know, it happens.  

See this for more information on msdb..restorehistory:

  https://msdn.microsoft.com/en-us/library/ms187408.aspx

Disable (or enable) all SQL Server Agent jobs

I was doing some heavy lifting for a customer recently... it was after hours, and I needed to disable a few Agent jobs beforehand, to be sure I didn't run into anything. This post is the cursor I used to first disable the jobs, and then re-enable them afterward. Very simple, you can see it's just based on an IN list:

/* Use this to disable multiple SQL Server Agent jobs in one action. */

USE msdb;

DECLARE

     @job VARCHAR(128),

     @success INT,

     @error INT,

     @sql NVARCHAR(255)

     /* declare cursor to disable sysjobs based on the IN list */

     DECLARE DisableJobCursor CURSOR FOR

     SELECT sj.name FROM msdb..sysjobs sj

     WHERE sj.name NOT IN('AFewJobs','YouMayWant','ToIgnore') -- change as needed

     AND enabled = 1

     SELECT @success = 0

     SELECT @error = 0

     OPEN DisableJobCursor

     FETCH NEXT FROM DisableJobCursor

     INTO @job

     WHILE (@@FETCH_STATUS <> -1)

     BEGIN

       IF (@@FETCH_STATUS <> -2)

       BEGIN

        SELECT @sql='EXEC dbo.sp_update_job @job_name = N'''+@job+''',@enabled = 0;'

        EXECUTE sp_executesql @sql

        SELECT @success = @success + 1

       END

       NextRecord:

       FETCH NEXT FROM DisableJobCursor

       INTO @Job

     END

     CLOSE DisableJobCursor

     DEALLOCATE DisableJobCursor

     SELECT 'Disabled Jobs = ' + CONVERT(VARCHAR(10),@success)

     SELECT 'Failures = ' + CONVERT(VARCHAR(10),@error)

If you're working with a lot of jobs, that IN list can become a pain. In that case we could go in more collectively by using the job category. For example, let's say we want to disable all jobs in the 'Database Maintenance' category.  Just use @category in your cursor declaration, instead of the IN list, like this:

    DECLARE

       @job VARCHAR(128),

       @jobcategory  VARCHAR(25) = 'Database Maintenance',

       @sql VARCHAR(255)

       DECLARE DisableJobCursor CURSOR FOR

       SELECT sj.name FROM msdb..sysjobs sj INNER JOIN msdb..syscategories sc

         ON sj.category_id = sc.category_id

       WHERE sc.name = @jobcategory

       AND enabled = 1  

Another option, though a bit reckless, is just to go in for all enabled jobs.  This approach let's you disable/enable en masse:

       DECLARE DisableJobCursor CURSOR FOR

       SELECT sj.name FROM msdb..sysjobs sj

       WHERE sj.enabled = 1

You want to be careful with that last approach... who knows how many jobs you have?!

I would be remiss if I don't caution you about cursor usage. In most cases, I am pretty anti-cursor, but... that's not what this post is about.  I will give you an excellent reference for cursor examples, performance considerations, and alternatives. Don't ignore.  This is an excellent read.

    http://www.brentozar.com/sql-syntax-examples/cursor-example/ 

Query SQL Server Maintenance Plan details

I recently inherited a couple of servers, and am in the process of assessing them, and cleaning things up.  Today I was viewing job logging, and found this in the output of a failed job:

     Could not load package "\Maintenance Plans\RumbaKillListCSV" because of error 
     0xC0014062."

RumbaKillList?  Maintenance plans?  I didn't know about either of these things!  As I said, I just inherited these servers... it looks like I've got a lot more digging to do.  For starters, I wrote this to quickly list details regarding any maintenance plans that may exist.

     /* quick details regarding any maintenance plans you've inherited */

     SELECT

            mp.name [MaintenancePlan],

            mp.[description] [Description],

            mp.[owner] [Owner],

            sp.subplan_name [Subplan],

            sp.subplan_description [SubplanDescription],

            sj.name [Job],

            sj.[description] [JobDescription],

            sj.[enabled] [IsEnabled]

     FROM

            msdb..sysmaintplan_plans mp INNER JOIN msdb..sysmaintplan_subplans sp

              ON mp.id = sp.plan_id INNER JOIN msdb..sysjobs sj

                ON sp.job_id = sj.job_id

Not much, just a quick look to see what plans are there.  See that 'IsEnabled' -- this is just something to let you know whether or not the maintenance plan's corresponding Agent job is enabled.

Here's the output for just this plan:

You can expand upon the output, if you'd like.  Take a look at this from MSFT, for more detail  on these and the other Maintenance Plan tables:

     https://msdn.microsoft.com/en-us/library/ms190275.aspx

Update job owner for all SQL Server Agent jobs

I am helping a customer cleanup their SQL Server inventory, and remove a few risks that exist. In this case, they have more than 40 SQL Server Agent jobs that are owned by the domain login of their old DBA, who no longer works at the firm. They can't remove that login from the DC yet because the jobs that it owns will fail to run.

What login should be used as the Agent job owner?  There are many opinions here, but my rule is simple;  do not define SQL Server Agent job ownership with a domain login. What happens if when they remove the credentials, or even if DNS becomes inaccessible, and the login cannot be resolved? 

Use SA, or another SQL login that is created explicitly for Agent job ownership. In this tip I will show you how to identify all non-SA owned Agent jobs, and then update ownership to SA. 

FIRST, you run this SELECT to get a list of the jobs that you are going to effect. It's just a safety check, and will help you change back, if necessary. 

     /*  output a list of the jobs you'll update */ 

     SELECT a.name

     FROM msdb..sysjobs a JOIN sys.server_principals b

       ON a.owner_sid = b.sid

     WHERE a.enabled = 1  -- just looking for enabled jobs

     AND b.name <> 'sa'

Now we can alter the ownership for each to SA using a cursor, like this:

     /*  update job owner for all SQL Server Agent jobs */

     DECLARE @jobname VARCHAR(1000)

     DECLARE JobCursor CURSOR

     FOR

     SELECT a.name

     FROM msdb..sysjobs a JOIN sys.server_principals b

       ON a.owner_sid = b.sid

     WHERE a.enabled = 1  -- just looking for enabled jobs

     AND b.name <> 'sa'

     OPEN JobCursor

     FETCH NEXT FROM JobCursor INTO @jobname

     WHILE (@@FETCH_STATUS <> -1)

     BEGIN

          EXEC msdb..sp_update_job @job_name = @jobname, @owner_login_name = 'sa'

          FETCH NEXT FROM JobCursor INTO @jobname

     END

     CLOSE JobCursor

     DEALLOCATE JobCursor

Very simple, but we do have to ask another question. Should we use the SA account for job ownership?  I'll come back with another post tomorrow regarding SQL Server Agent's fixed database roles. Specifically, SQLAgentOperatorRole. We will use it to establish SQL Server Agent job ownership, using a non-SA account.

Back soon.  

SERVERPROPERTY, part II

I decided to expand upon my SERVERPROPERTY script this morning... there are a lot of other properties beyond just the name of the instance.  :-)  I put this one together quickly, should work well if you're looking at configuration or other settings, and you'd like to see all server properties at once. 

     /* Use SERVERPROPERTY to collect details regarding the server instance. */

     DECLARE @serverproperty TABLE (property sysname)

     INSERT INTO @serverproperty(property)

     VALUES

            ('BuildClrVersion'),

            ('Collation'),

            ('CollationID'),

            ('ComparisonStyle'),

            ('ComputerNamePhysicalNetBIOS'),

            ('Edition'),

            ('EditionID'),

            ('EngineEdition'),

            ('InstanceName'),

            ('IsClustered'),

            ('IsFullTextInstalled'),

            ('IsIntegratedSecurityOnly'),

            ('IsSingleUser'),

            ('LCID'),

            ('LicenseType'),

            ('MachineName'),

            ('NumLicenses'),

            ('ProcessID'),

            ('ProductVersion'),

            ('ProductLevel'),

            ('ResourceLastUpdateDateTime'),

            ('ResourceVersion'),

            ('ServerName'),

            ('SqlCharSet'),

            ('SqlCharSetName'),

            ('SqlSortOrder'),

            ('SqlSortOrderName'),

            ('FilestreamShareName'),

            ('FilestreamConfiguredLevel'),

            ('FilestreamEffectiveLevel');      

     -- pull them back out

     SELECT

            Property,

            SERVERPROPERTY(Property) Value

     FROM

            @serverproperty

Here's a piece of the output from one of my own instances:

Take a look at this for more SERVERPROPERTY details from MSFT:  

   https://msdn.microsoft.com/en-us/library/ms174396(v=sql.110).aspx