SQL: Last backups for all databases

\ by 
-- Last backups for all databases 

SELECT 
    CAST(DATABASEPROPERTYEX([d].[name], 'Recovery') AS VARCHAR(10)) [RecoveryModel], 
    LEFT([d].[name], 50) [DatabaseName], 
    MAX([s].[backup_start_date]) [StartDate], 
    DATEDIFF(HOUR, MAX([s].[backup_start_date]), GETDATE()) [HoursAgo], 
    CASE [s].[type] 
    WHEN 'D' THEN 
        'Full' 
    WHEN 'I' THEN 
        'Differential' 
    WHEN 'L' THEN 
        'Log' 
    WHEN 'F' THEN 
        'File or Filegroup' 
    END [LastBackupType] 
FROM [master]..[sysdatabases] [d] 
LEFT OUTER JOIN [msdb]..[backupset] [s] 
    ON [d].[name] = [s].[database_name] 
JOIN ( 
    SELECT 
        [d].[name], 
        MAX([a].[backup_start_date]) [StartDate] 
    FROM [master]..[sysdatabases] [d] 
    LEFT OUTER JOIN [msdb]..[backupset] [a] 
        ON [d].[name] = [a].[database_name] 
    WHERE [d].[name] NOT IN ( 
              'tempdb', 'Northwind', 'pubs', 'model' 
          ) 
          AND [a].[type] = 'D' 
    GROUP BY [d].[name] 
) [x] 
    ON [x].[name] = [d].[name] 
WHERE [d].[name] NOT IN ( 
          'tempdb', 'Northwind', 'pubs', 'model' 
      ) 
GROUP BY [d].[name], 
         [s].[database_name], 
         DATABASEPROPERTYEX([d].[name], 'Recovery'), 
         [s].[type], 
         [x].[StartDate] 
HAVING MAX([s].[backup_start_date]) >= [StartDate] 
ORDER BY [d].[name], 
         MAX([s].[backup_start_date])
Posted in: SQL

SQL Agent: Use a batch file to run RoboCopy

\ by

It’s really hard to run RoboCopy in a SQL Agent command step. We want to use CmdExec so we can execute using a DOS proxy. But RoboCopy has a lot of weird exit values.

Easily handle the exit codes by calling a batch file. I’ve hard coded our options to make it easy and standard. 

@ECHO ON

IF {%3}=={} (
  @ECHO USAGE
  @ECHO RoboCopyFiles SourcePath DestinationPath Files
  @ECHO.
  GOTO FINISHED
) ELSE (

IF NOT {%4}=={} (
  (robocopy %1 %2 %3 /COPY:DAT /NP /R:0 /A+:A) 
) ELSE (
  (robocopy %1 %2 %3 /COPY:DAT /NP /R:0 /A+:A  /L) 
)

)
:FINISHED
IF %ERRORLEVEL% GEQ 8 exit /B %ERRORLEVEL%
exit /B 0
Posted in: Coding, DOS