0

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

migrated from stackoverflow.com Jun 21 '13 at 12:00

This question came from our site for professional and enthusiast programmers.

3

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
5

I would recommend using these free backup (and other) scripts from http://ola.hallengren.com/

These have full logging already and have lots of other useful options.

Personally, these are what I use. Thoroughly tested and used by many, many folk.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy