September 28, 2011 at 4:58 am
Hello,
Recently i found that one of the windows server jobs is backing up SQL 2008 databases unexpectedly.
This job is used to backup application data and not SQL usually. This is the standard Microsoft Windows Backup and uses block-level backup linked through VSS.
My question is: if we use standard SQL backups for full, diff and log backups and this unexpectedly backs up the databases writing the ususal information messages, would this affect our restore chain is recovery is needed. I have been unable to find an answer yet.
My first instinct is no as this is a block-level backup....but as this writes individual entries for each database in SQL log and SQL is VSS aware....im not so sure anymore!!
The backup log looks like this:
Database backed up. Database: eGActiveDB, creation date(time): 2011/03/07(14:25:37), pages dumped: 99998, first LSN: 178:187120:196, last LSN: 178:187202:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{39ED1B57-130A-4E44-A4E5-3D976F3CD654}4'}). This is an informational message only. No user action is required.
I would need to look in the Windows Backup Console to see if the databases can be distinguished from the other data.
Thanks in advance
LilyWhites
September 28, 2011 at 6:34 am
The backup log looks like this:
Database backed up. Database: eGActiveDB, creation date(time): 2011/03/07(14:25:37), pages dumped: 99998, first LSN: 178:187120:196, last LSN: 178:187202:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{39ED1B57-130A-4E44-A4E5-3D976F3CD654}4'}). This is an informational message only. No user action is required.
When does the backup log say this? When you're performing SQL Server backups, or the "file system" backup. A file system backup cannot produce this message.
If it genuinely is a file system backup through windows backup, then no, it can't affect the chain for TLOG backups or the differential base of DIFF's, but you'd never want to recover from the file system backup anyway, so I would get the Windows Backup to exclude the files.
September 28, 2011 at 6:42 am
Hello,
The SQL Server Error Log has an entry for the database backup for all databases, system and user....all starting at 01:00:41.
Date27/09/2011 01:00:41
LogSQL Server (Current - 18/09/2011 06:06:00)
SourceBackup
Message
Database backed up. Database: eGMasterDB, creation date(time): 2011/03/07(14:25:08), pages dumped: 970, first LSN: 1070:11115:56, last LSN: 1070:11139:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{39ED1B57-130A-4E44-A4E5-3D976F3CD654}3'}). This is an informational message only. No user action is required.
I would not normally expect the file-system level backup to affect the log chain....but if SQL is recognising the file-system backup and recording individual backup entries for all databases with LSN numbers i would assume it is able to affect the chain.
I will be working with the server team to see how they have configured these backups. I know they are using a new powershell method so will ask to see the scripts or if there are any exclusions available.
September 28, 2011 at 6:52 am
Hmm, actually, looks like my information may be a little out of date.
It has a one line statement in the Windows Server Backup document that mentions an awareness of VSS in SQL Server and there's a 3rd party arcitle that says the following:
Because the system is based on VSS (unlike some other drive imaging products), it means that it's possible to backup VSS aware applications, like Exchange Server 2007, SQL Server 2005 and SharePoint Services. At the time of the backup, WSB will issue a notification to VSS that a backup is about to start, and in turn, VSS will notify the VSS aware applications to commit their data to disk. This means that the backup will be consistent, and any open databases will have their data committed and in a consistent state.
http://www.wbadmin.info/articles/how-does-windows-server-2008-backup-work.html
I've no idea how accurate this is, but the message shown looks like a full backup of some kind - this does not upset log chains (only log backups do this) but depending on how it's taken could affect differential bases, but I'd suspect not as we'd hear about it more often.
What does msdb..backupset show for the backups that match these times? Specifically the type and flags columns would be useful
September 28, 2011 at 6:53 am
Sorry....our backup schedule runs differentials during the week at 05:00 and fulls on Sunday at 04:00.
This is definately part of the file-system backup as that job starts at 1am every tuesday which is when these backups happen....and are also surrounded by VSS service and disk informational messages.
Our standard backup logs look like:
Date27/09/2011 05:00:04
LogSQL Server (Current - 28/09/2011 10:30:00)
SourceBackup
Message
Database differential changes were backed up. Database: eGActiveDB, creation date(time): 2011/03/07(14:25:37), pages dumped: 7947, first LSN: 178:242578:257, last LSN: 178:242685:1, full backup LSN: 177:4678:246, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\lonvault2\Backups\SQL_Backups\UK_Servers\EMS00940\Differential\eGActiveDB_Differential_Tuesday.BAK'}). This is an informational message. No user action is required.
I will arrange for these to be excluded and would not want to restore from the file-system backup....but my real question was:
will this break the chain??
this is more to answer the question so i know, so others know....so i know to re-run the full backups tonight to ensure that all servers affected are recoverable.
Thanks
September 28, 2011 at 6:59 am
please see attachment for msdb..backupset entries
thanks
September 28, 2011 at 7:05 am
Hello,
I have found the answer i believe. Checking the full LSN number from the differential posted above, the full backup taken at 1am the other day and the full backup taken over last weekend....the "full backup LSN" matches the scheduled differential and the full from the weekend....not the full from the other night.
I was worried as it was showing LSN numbers and individual entries per database from the VSS backup but it looks the theory that it is unable to affect the differential chain is correct.
Thanks for your help
LilyWhites
September 28, 2011 at 7:07 am
Ok, so these are COPY_ONLY snapshot backups.
COPY_ONLY means that it does not reset the differential base, so will not affect other backup sequences.
I've learnt something new here - I've always told people that Windows Server Backups of SQL Server data files are to be avoided as they're 'hot' backups, but it appears they would be consistent if this is how they're backed up
September 28, 2011 at 7:12 am
very true....they really dont serve much purpose though....apart from point-in-time snapshots but for the space used....ill arrange to get them removed sharpish!!
thanks for the help 🙂
i hope this is useful to others also....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply