July 13, 2007 at 3:06 am
Hello
We're running an SQL2K server that uses the SQL Server Agent to a nightly full backup of our main DB (about 3 AM) with frequent transaction log backups in between.
Separately, our operations people have a nightly disk backup on the same server that's done using NTBACKUP and which happens at 9PM.
Every time the NTBACKUP process kicks off, SQL Server does a snapshot backup of all databases to a virtual device, which as I understand it is part of the volume shadow copying that NTBACKUP does.
I have 2 questions.
1. Does the SQL snapshot backup that happens as part of the NTBACKUP process break our transaction log history chain (i.e. does it truncate the transaction log like a normal full backup)?
2. Given that we're using SQL server for our DB backup, is there a way to get the server to ignore NTBACKUP's activity and not bother with the snapshot backup? We've tried adding *.ldf and *.mdf to the list of excluded file types for backup but that doesn't appear to have made an impact.
Thanks,
Ciarán O'Rourke
July 13, 2007 at 3:40 am
Your full database backup will not affect the log backups anyways that you do it as part of logshipping. You can do a full backup and then back that to disk if you want to avoid tlog backs to tape exclude *.trn files so that the don't move to tape.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 13, 2007 at 5:05 am
You may find this KB article of interest: http://support.microsoft.com/kb/903643
To summarise it states that the NTBACKUP process may break the 'differential backup' chain; however, since you do not perform diff backups, this is not an issue for you.
Paul
July 13, 2007 at 9:55 am
Interesting question. I did find a reference in mskb that the data files and logs are backed up with volume shadow copy service, and another reference that these types of cached copying "by 3rd party tools" can invalidate these log backups. Whether or not VSS is considered part of these "3rd party" cached copying tools is not clear. But to be safe, I would expect that SQL Server backups in NTBackup may fail when restored, and that the transaction log is no longer in sync with your last full SQL Server backup when NTBackup runs.
If you were running SQL Server 2005, you can get NTBackup to not backup SQL Server databases (and logs) by stopping SQL Writer service. But I don't think you have that in SQL Server 2000. I did find a reference (http://support.microsoft.com/kb/830575/en-us) to an error messages when NTBackup tries to backup SQL Server 2000 and SQLVDI.dll is unregistered. Perhaps you can do that and put up with the error messages.
Otherwise, I would suggest your best option is to get operations to not run NTBackup on database server. 2nd best option is to schedule your full backup as soon as possible after the NTBackup runs, so you have consistent full and tlog backups after that point.
Hope this helps. Good luck. Perhaps Steve can bounce this off one of his SQL Ranger buddies. I will post this to the one of the MS partner managed newsgroup, to see if anything comes back from this. Again, an interesting question.
Mark
July 13, 2007 at 10:11 am
Thanks everyone for the feedback.
I've done some more digging, and think I've clarified the situation. Basically, it looks like my assumption that a full database backup truncates the transaction log was wrong. When the DB is in full recovery mode (as ours is), the only thing that clears inactive entries from the transaction log is a transaction log backup.
The first hint I saw of this was in another forums discussion thread about "Full recovery model confusion" at http://www.dbforums.com/archive/index.php/t-319210.html.
The following MS KB article supports this view. It talks about scenarios where transaction logs can grow out of control. It mentions that with a full recovery model, one method to controlling transaction log size is regular transaction log backups. http://support.microsoft.com/kb/873235
Because a full backup doesn't truncate the logs, the snapshot backup triggered by NTBackup is not a problem. I'd still like to get SLQ Server to ignore NTBackup altogether, but it looks like we'll need to move to 2005 for that.
Thanks again,
Ciarán
July 14, 2007 at 12:57 pm
Your first question should have been answered by your restore testing. You are restore-testing your backups, right?
Restore a snapshot, and start applying logs through an NTBACKUP time.
The quality of different vendors' snapshot backup implementation varies, and the restore procedures can be rather non-intuitive. You need to constantly validate:
- That the snapshot job has been configured correctly
- That the software works as advertised
- That everyone on staff is comfortable with pulling a snapshot and logs from tape and restoring to a point in time with minimal digging through documentation. Emergency restores are not planned around 'the guy who knows how the backups work' being available, and the additional stress of an emergency restore will practically guarantee mistakes at the hands of a first-timer.
The only way to acheive these results is frequent restore-testing. This can be automated, while also requiring everyone who may be called upon to restore data to occasionally do so manually.
The most basic task of any DBA is to protect the data: backups and security. Everything else we do is at the luxury of having solid backups and security.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply