October 29, 2008 at 10:41 am
I have kind of a strange problem..
I use Maintenance plans to backup my SQL Server 2005 Databases. All user databases in this instance are in FULL recovery mode. I perform a Full backup each night and T-Log backups during business hours every 2 hours.
I thought things were going along GREAT. The transaction log backups are being physically created and I am even getting my email notifications that they completed, but when I look at the Database Properties (for each Database), it shows "last Log backup - NONE"
I discovered this only after trying to use this SQL to get a backup history report for an audit - and it only returns the FULL Backups!!!!!!
SELECT s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name ,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE
(s.database_name = 'OFAC'
OR s.database_name = 'PBSA')
ORDER BY database_name, backup_start_date, backup_finish_date
I really need to get a backup history report. Does anyone know why this might be happening??
I thought maybe a Service Pack issue.. but I only have Two SQL Server 2005 instances and they are both on the same SP level.. Can anyone help me figure this out???
October 30, 2008 at 8:20 am
Hmmm, must be something funky in the maint Plan. You actually see My_DB_20081029_0600.TRN type files on your system ?? Are they 0 bytes ? Is there something odd in your plan that's giving the file the wrong name ... so that the backup of Database_A is getting a name for Database_B somehow ??
(nit-pick ..... divide by 1048576 to get meg, not 1000000)
October 30, 2008 at 8:48 am
OK. The TLog backup files look fine in the backup directory. Files are named correctly, Tlog Backups ARE running every 2 hours and I AM getting an email notification that they ran. They are just not showing up in the MSDB backup history tables. Also, When I open MGT Studio , right-click on any of the databases and choose OPTIONS, it shows last full backup date and time and NONE for Tlog backups.. I tried running a TLOG backlup manually. It runs fine but still no history written OR any indications that the TLOG was backed up when I looK at Database options.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply