Phantom Backup Job

GYeohAMAL

Greetings All,

I have a strange issue that began after a disaster recovery test of several log shipped databases.

We've been using Redgate SQL backup 6.4.0.56 log shipping successfully on a SQL 2008 server. After the DR test was completed I deleted the exiting log shipping job and re-set them up without any issues, even manually ran each job to make sure the log was produced, copied to the target server and then played successfully.

What is strange is that the when the log shipping schedule starts at 7am I get the following error

22/06/2011 7:05:01 AM: SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.
22/06/2011 7:05:01 AM: SQL error 4305: SQL error 4305: The log in this backup set begins at LSN 20453000000071900001, which is too recent to apply to the database. An earlier log backup that includes LSN 20452000000484900001 can be restored.

Now I understand that the SQL error 4305 essentially means that I'm missing a transaction log, but the strange thing is that between the full backup that is scheduled for 9pm and the transaction log shipping starting at 7am the following morning and stopping at 7pm at night, there are no other jobs scheduled to run. Also the full back finishes before 11pm.

On closer inspection of the MSDB..BackupSet and MSDB..BackupMediaFamily tables, I found 2 entries each night since the DR test that show a full backup and a log shipping backup happen randomly between 2 and 3 am. The issue is that the physical_device_name show 'NUL'. (FYI NUL is not a mistype of NULL, it actually states 'NUL' in the field.) Also strange is that nothing is scheduled to run between 2 and 3 (Native SQL jobs included).

I'm hoping that I have just missed a clean up task and it's just a case of RTFM more carefully.

Any help will be most appreciated.

Thanks
Gavin.

petey

Backing up to a NUL device is usually only used for testing and benchmarking purposes (e.g. here). You would never use it on a production system as it'll render differential backups made after the nul-type full backup useless, and also break your trx log chain for log backups.

SQL Backup never schedules backups to NUL devices. I would suggest running Profiler to trace who/what is running the backups from 2 am to 3 am. You could filter on the BACKUP keyword.

GYeohAMAL

Thanks Peter I'll give the Profiler a go and report back.

GYeohAMAL

Well the Profiler provided no clues, so I broaden my investigation to any changes to other systems that were made on the day before the issue consistently started happening.

What I found was that a Veeam base backup was happening at that time and it has a VSS setting that causes SQL server to do a full backup of all non system databases and then a truncate of the logs to NUL. Details can be found at http://www.veeam.com/forums/viewtopic.php?f=2&t=2533

I've now made the necessary changes to the Veeam backup and will keep fingers crossed tonight that log shipping doesn't break again.

petey

Thanks for the update.