SQL Server database judgment of the latest backup execution results (latest recommended)
1 2 errorlogtrace3 backupset4 SQL1 SQL Servermsdb.dbo
1
SQL Servermsdb.dbo.backupset
SQL servererrorlog
2 errorlogtrace
sys.traceserrorlogtracefn_trace_gettableerrlogtrace
errorlogtrace
SELECT REVERSE( SUBSTRING( REVERSE([path]), CHARINDEX(CHAR(92), REVERSE([path])), 260 ) ) + N'log.trc' FROM sys.traces WHERE is_default = 1
2. fn_trace_gettableerrologtrace
SELECT *FROM sys.fn_trace_gettable( 'S:MSSQL13.MSSQLSERVERMSSQLLoglog.trc', default )
3. traceEventClass115
SELECT TextData,Databasename,StartTimeFROM sys.fn_trace_gettable( 'S:MSSQL13.MSSQLSERVERMSSQLLoglog.trc', default )WHERE EventClass = 115 AND UPPER(CONVERT(nvarchar(max), TextData)) LIKE 'BACKUP%'
TextDatamsdb.dbo.backupset
3 backupset
1trace flowchart LR A[trace]--> B[StartTime]B[backupset]--> C{StartTime}C---->D[]C---->E[]
traceStartTimebackupsetbackup_start_date
2.
SELECT dt.DatabaseName, dt.StartTime, bs.backup_start_date, bs.backup_finish_date, [Status] = CASE WHEN bs.backup_start_date IS NULL THEN (dt.DatabaseName) + '' ELSE (dt.DatabaseName) + '' ENDFROM sys.fn_trace_gettable( 'S:MSSQL13.MSSQLSERVERMSSQLLoglog.trc', default ) AS dt LEFT OUTER JOIN msdb.dbo.backupset AS bs ON dt.DatabaseName = bs.database_name AND ABS( DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date) ) < 5WHERE dt.EventClass = 115 AND UPPER(CONVERT(nvarchar(max), dt.TextData)) LIKE N'BACKUP%'ORDER BY dt.StartTime DESC;
4 SQL
SQL
1chatgptgroup by2
1
DBCC FREEDECLARE @databaseName1 nvarchar(100)DECLARE @sql nvarchar(4000)DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databasesOPEN db_cursorFETCH NEXT FROM db_cursor INTO @databaseName1WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = ' SELECT TOP 1 dt.DatabaseName, dt.StartTime, bs.backup_start_date, bs.backup_finish_date, [Status] = CASE WHEN bs.backup_start_date IS NULL THEN (dt.DatabaseName) + '''' ELSE (dt.DatabaseName) + '''' ENDFROM sys.fn_trace_gettable( ( SELECT REVERSE( SUBSTRING( REVERSE([path]), CHARINDEX(CHAR(92), REVERSE([path])), 260 ) ) + N''log.trc'' FROM sys.traces WHERE is_default = 1 ), default ) AS dt LEFT OUTER JOIN msdb.dbo.backupset AS bs ON dt.DatabaseName = bs.database_name AND ABS( DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date) ) < 5WHERE dt.EventClass = 115 AND UPPER(CONVERT(nvarchar(max), dt.TextData)) LIKE N''BACKUP%'' AND dt.DatabaseName=''' + @databaseName1 +''' ORDER BY dt.StartTime DESC' EXEC sp_executesql @sql FETCH NEXT FROM db_cursor INTO @databaseName1END CLOSE db_cursorDEALLOCATE db_cursor
324
SQL Server : CPU = 0 = 0 'Worktable' 0 14 0 0 lob 0 lob 0 lob 0 'sysobjvalues' 14 36 0 0 lob 0 lob 0 lob 0 'syspalvalues' 0 98 0 0 lob 0 lob 0 lob 0 'sysguidrefs' 2 30 0 0 lob 0 lob 0 lob 0 'sysclsobjs' 0 0 0 0 lob 0 lob 0 lob 0 'syssingleobjrefs' 7 42 0 0 lob 0 lob 0 lob 0 'sysdbreg' 1 2 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 31 = 27 'Worktable' 0 2 0 0 lob 0 lob 0 lob 0 'sysobjvalues' 2 6 0 0 lob 0 lob 0 lob 0 'syspalvalues' 0 14 0 0 lob 0 lob 0 lob 0 'sysguidrefs' 0 0 0 0 lob 0 lob 0 lob 0 'sysclsobjs' 0 0 0 0 lob 0 lob 0 lob 0 'syssingleobjrefs' 1 6 0 0 lob 0 lob 0 lob 0 'sysdbreg' 0 2 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 16 = 23 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 (1 ) 'backupset' 1 3 0 0 lob 0 lob 0 lob 0 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 468 = 678 SQL Server : CPU = 468 = 678 'Worktable' 0 2 0 0 lob 0 lob 0 lob 0 'sysobjvalues' 2 6 0 0 lob 0 lob 0 lob 0 'syspalvalues' 0 14 0 0 lob 0 lob 0 lob 0 'sysguidrefs' 0 0 0 0 lob 0 lob 0 lob 0 'sysclsobjs' 0 0 0 0 lob 0 lob 0 lob 0 'syssingleobjrefs' 1 6 0 0 lob 0 lob 0 lob 0 'sysdbreg' 0 2 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 16 = 23 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 (0 ) 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 406 = 732 SQL Server : CPU = 406 = 732 'Worktable' 0 2 0 0 lob 0 lob 0 lob 0 'sysobjvalues' 2 6 0 0 lob 0 lob 0 lob 0 'syspalvalues' 0 14 0 0 lob 0 lob 0 lob 0 'sysguidrefs' 0 0 0 0 lob 0 lob 0 lob 0 'sysclsobjs' 0 0 0 0 lob 0 lob 0 lob 0 'syssingleobjrefs' 1 6 0 0 lob 0 lob 0 lob 0 'sysdbreg' 0 2 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 16 = 21 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 (0 ) 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 422 = 659 SQL Server : CPU = 422 = 660 'Worktable' 0 2 0 0 lob 0 lob 0 lob 0 'sysobjvalues' 2 6 0 0 lob 0 lob 0 lob 0 'syspalvalues' 0 14 0 0 lob 0 lob 0 lob 0 'sysguidrefs' 0 0 0 0 lob 0 lob 0 lob 0 'sysclsobjs' 0 0 0 0 lob 0 lob 0 lob 0 'syssingleobjrefs' 1 6 0 0 lob 0 lob 0 lob 0 'sysdbreg' 0 2 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 15 = 616 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 (0 ) 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 375 = 678 SQL Server : CPU = 375 = 678 'Worktable' 0 2 0 0 lob 0 lob 0 lob 0 'sysobjvalues' 2 6 0 0 lob 0 lob 0 lob 0 'syspalvalues' 0 14 0 0 lob 0 lob 0 lob 0 'sysguidrefs' 0 0 0 0 lob 0 lob 0 lob 0 'sysclsobjs' 0 0 0 0 lob 0 lob 0 lob 0 'syssingleobjrefs' 1 6 0 0 lob 0 lob 0 lob 0 'sysdbreg' 0 2 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 16 = 1286 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 (1 ) 'backupset' 1 2 0 0 lob 0 lob 0 lob 0 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 375 = 781 SQL Server : CPU = 375 = 781 'Worktable' 0 2 0 0 lob 0 lob 0 lob 0 'sysobjvalues' 2 6 0 0 lob 0 lob 0 lob 0 'syspalvalues' 0 14 0 0 lob 0 lob 0 lob 0 'sysguidrefs' 0 0 0 0 lob 0 lob 0 lob 0 'sysclsobjs' 0 0 0 0 lob 0 lob 0 lob 0 'syssingleobjrefs' 1 6 0 0 lob 0 lob 0 lob 0 'sysdbreg' 0 2 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 31 = 1608 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 13 = 13 (1 ) 'backupset' 1 3 0 0 lob 0 lob 0 lob 0 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 390 = 737 SQL Server : CPU = 406 = 751 'Worktable' 0 2 0 0 lob 0 lob 0 lob 0 'sysobjvalues' 2 6 0 0 lob 0 lob 0 lob 0 'syspalvalues' 0 14 0 0 lob 0 lob 0 lob 0 'sysguidrefs' 0 0 0 0 lob 0 lob 0 lob 0 'sysclsobjs' 0 0 0 0 lob 0 lob 0 lob 0 'syssingleobjrefs' 1 6 0 0 lob 0 lob 0 lob 0 'sysdbreg' 0 2 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 16 = 20 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 12 = 12 (0 ) 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 468 = 2492 SQL Server : CPU = 484 = 2505 'Worktable' 0 2 0 0 lob 0 lob 0 lob 0 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 (1 ) SQL Server : CPU = 16 = 23 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0
2MAXgroup by
SELECT [Status] = MAX(CASE WHEN bs.backup_start_date IS NULL THEN dt.DatabaseName+'' ELSE dt.DatabaseName+'' END) FROM sys.fn_trace_gettable( ( SELECT REVERSE( SUBSTRING( REVERSE([path]), CHARINDEX(CHAR(92), REVERSE([path])), 260 ) ) + N'log.trc' FROM sys.traces WHERE is_default = 1 ), default ) AS dt LEFT OUTER JOIN msdb.dbo.backupset AS bs ON dt.DatabaseName = bs.database_name AND ABS(DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)) < 5 WHERE dt.EventClass = 115 AND UPPER(CONVERT(nvarchar(max), dt.TextData)) LIKE 'BACKUP%' GROUP BY dt.DatabaseName
1
SQL Server : CPU = 15 = 20 (3 ) 'Worktable' 0 0 0 0 lob 0 lob 0 lob 0 'backupset' 1 48 0 0 lob 0 lob 0 lob 0 SQL Server : CPU = 469 = 935 SQL Server : CPU = 0 = 0 SQL Server : CPU = 0 = 0
xp_readerrorlogerrlog
SQL Server,SQL Server
Tag: latest SQL Server database judgment of the backup execution
Disclaimer: The content of this article is sourced from the internet. The copyright of the text, images, and other materials belongs to the original author. The platform reprints the materials for the purpose of conveying more information. The content of the article is for reference and learning only, and should not be used for commercial purposes. If it infringes on your legitimate rights and interests, please contact us promptly and we will handle it as soon as possible! We respect copyright and are committed to protecting it. Thank you for sharing.
