Sql-server – SQL Server Maintenance Plan TSQL query to log the backup databases

sql server

Does anyone know how to use the TSQL task in SQL Server 2008 Maintenance Plan to log (insert) the databases that where backed-up?

Imagine a Maintenance Plan that does the backup for DB1,DB2 and DB3.

I wanted a way to do 

INSERT INTO LOG VALUES (BD1,...)
INSERT INTO LOG VALUES (BD2,...)
INSERT INTO LOG VALUES (BD3,...)

But I did not want to statically type this, but have a generic SQL that knows which DBs where backup and insert those.

Thanks

Best Answer

You can get the backup date for each database backed up after the plan started from

--INSERT INTO YourBackupLogTable
--     (DatabaseName, LastBackupStartDate, LastBakupFinishDate, LastBackupDurationSecs)
SELECT db.name AS DatabaseName
    , MAX(lb.backup_start_date) AS LastBackupStartDate
    , MAX(lb.backup_finish_date) AS LastBakupFinishDate
    , DATEDIFF(S, MAX(lb.backup_start_date), MAX(lb.backup_finish_date) ) AS LastBackupDurationSecs
FROM sys.databases AS db
JOIN msdb.dbo.backupset lb ON lb.database_name = db.name
WHERE lb.backup_start_date >= (SELECT MAX(l.start_time) AS Start 
                                 FROM msdb.dbo.sysmaintplan_plans p
                           INNER JOIN msdb.dbo.sysmaintplan_log l ON p.id = l.plan_id
                                WHERE name = 'Your backup plan')
GROUP BY db.name
Related Question