-- 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])
SQL Agent: Use a batch file to run RoboCopy
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