DBA first day in a new job - check backups and security - How? what else should be checked?

Generally when I start in a new environment, I tend to check where are the backups, when the last full was taken, when was the last restore applied and I check the security too.

The way I do this is via T-SQL.


Check the backups

;with Radhe as (
            SELECT  @@Servername as [Server_Name],
            B.name as Database_Name, 
            ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
            ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
            ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
            ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
            ,media_set_id = MAX(A.media_set_id)
            ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
            ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
            ,A.type
            FROM sys.databases B 

            LEFT OUTER JOIN msdb.dbo.backupset A 
                         ON A.database_name = B.name 
                        AND A.is_copy_only = 0
                        AND (A.type = 'D')  --'D' full, 'L' log 

            GROUP BY B.Name, A.type

        )

         SELECT r.[Server_Name]
               ,r.Database_Name
               ,[Backup Type] = r.type 
               ,r.DaysSinceLastBackup
               ,r.LastBackupDate
               ,r.BackupSize_GB
               ,r.BackupSize_MB
               ,F.physical_device_name
               ,r.[AVG Backup Duration]
               ,r.[Longest Backup Duration]

           FROM Radhe r

            LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                         ON R.media_set_id = F.media_set_id

            ORDER BY r.Server_Name, r.Database_Name

For checking the security:

Server

I wrote the free (and open source) sp_Blitz for this exact reason.

People kept handing me SQL Servers and going, "You're the DBA, you manage this thing." I needed something that could quickly analyze stuff like:

• Databases that hadn't been backed up or checked for corruption

• Unsupported builds of SQL Server

• Dangerous trace flags and database settings

• Bad sp_configure options

Just run it, no parameters required, and you'll get a prioritized health check. Priorities 1-50 are things you want to jump on right away, and priority 51+ are things to take a note of for later.

For support or to contribute code, head to the Github repo at FirstResponderKit.org.

StackExchange Database Administrators Q#164406, answer score: 73