February 9, 2016 at 11:30 am
Hi All,
I got an alert from my Red Gate SQL Monitor of blocking on one of our production databases. When I looked at it, there was my normal backup job blocking another backup that I've never seen before. After going a few rounds with the Networking department, which started out with "We're not doing anything" it turned out they are doing VM snapshots that are also doing a backup. Here is the command from their tool (taken from SQL Monitor alert):
BACKUP DATABASE [MyDatabase] TO VIRTUAL_DEVICE='{3778E1BC-41C9-47D0-AEDE-170658B2DFA2}5' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024
I can't find much information on WITH SNAPSHOT. My question is this.
If I do a FULL database backup, then this backup executes and I start my transaction log backups, is this going to break the chain? Will I need to start with their backup and then apply the log backups or will I be able to use the FULL backup I took prior to this backup?
Thanks,
-Tom
February 9, 2016 at 11:45 am
The way things work is that log chains are separate from full backup chains.
For example, if this happens:
Full backup Monday, 11:00pm
Full backup Tuesday, 2:00pm
Full backup Tuesday, 11:00pm
And I have log backups here (short for simplicity).
Log backup, Monday, 10:00pm, let's say LSN 12
Full backup Monday, 11:00pm
Log backup, Tuesday 3:00am, LSN 15
Log backup, Tuesday, 6:00am, LSN 40
Log backup, Tuesday, 1:00pm, LSN 80
Full backup Tuesday, 2:00pm
Log backup, Tuesday, 10:00pm, LSN 100
Full backup Tuesday, 11:00pm
These are really interleaved like this:
Log backup, Monday, 10:00pm, let's say LSN 12
Full backup Monday, 11:00pm
Log backup, Tuesday 3:00am, LSN 15
Log backup, Tuesday, 6:00am, LSN 40
Log backup, Tuesday, 1:00pm, LSN 80
Full backup Tuesday, 2:00pm
Log backup, Monday, 10:00pm, LSN 100
Full backup Tuesday, 11:00pm
At any point, I can restore a full and then all logs following it. So if I need to restore Tuesday at 3:00pm, I can restore this:
- Full backup Tuesday, 2:00pm
- Tail log, taken from existing log
Or I can restore this:
- Full backup Monday, 11:00pm
- Log backup, Tuesday 3:00am, LSN 15
- Log backup, Tuesday, 6:00am, LSN 40
- Log backup, Tuesday, 1:00pm, LSN 80
- Tail log backup
Logs are independent of fulls, which is why you need log backups since these allow the log to clear and be re-used. Full backups do not allow this.
Does that help?
I don't like two sets of backups being run, but this isn't necessarily a problem. However, if the network group does log backups, then you have an issue because you need all those to perform a restore to a point in time.
February 9, 2016 at 11:52 am
Thanks Steve. This is exactly what I needed to know. As long as my full backup is still useful for a restore, I'm happy.
I did not find any evidence of them taking transaction log backups so I'm good.
Thanks,
-Tom
February 9, 2016 at 12:00 pm
You are welcome. One thing I'd watch is the msdb backup tables. If they're issuing commands through there, you should see things logged. Make sure no log backups in there are being made without your knowledge.
February 9, 2016 at 12:15 pm
When I first found that I started looking at what backups SQL Server had in msdb using this script (I don't remember where I got the base script from so unfortunately I can't attribute it to the author)
SELECT
s.database_name,
CASE s.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'Log'
END AS BackupType,
s.backup_start_date,
s.backup_finish_date,
DATEDIFF(SECOND, s.backup_start_date, s.backup_finish_date) AS DurationSeconds,
m.device_type,
m.physical_device_name
FROM msdb.dbo.backupset s
JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.backup_start_date > DATEADD(DAY, -7, GETDATE())
AND s.database_name = DB_NAME()
-- Remove m.device_type to see all backups
AND m.device_type = 7
ORDER BY s.backup_start_date DESC;
Looking at device_type = 7 for all virtual devices since I'm not doing any backups this way.
This was very helpful in tracking down the mystery backups.
-Tom
February 9, 2016 at 12:26 pm
Thanks for sharing. That's a good way to track these errant processes down.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply