January 13, 2011 at 11:39 am
Hi all,
right now I do SQL backups using T-SQL commands (Backup ... To File ...). AFAIK this does also reorganize the database logs, right? Now a discussion arose how to back up a specific application's data consistently, where the application data set does contain several SQL databases (Full recovery) *and* unfortunately some data structures in external files.
The obvious solution is to use NTBackup + VSS, which is also supported by the SQL server as a valid means of backup (at least the documentation says so), hoping VSS will catch all the files and the databases in a consistent way.
My concerns are like follows:
- VSS is working on a per file basis. so I guess that an incremental/differential backup will always backup the whole SQL database, right?
- Ntbackup will very likely not perform any database cleanup tasks, so I'd have to run a pre-or post backup T-SQL script to get the same results like I got if I'd run a backup using the internal SQL server Backup command
Frankly speaking I found almost no detailled documentaion about whether using NTBackup to backup a SQL server is a good idea or not, so I suspect that few are using it to do so. Is anyone here who does use Ntbackup to backup his SQL server, who can comment my concerns?
Thnx,
Armin.
January 13, 2011 at 11:56 am
I'm seeing a lot of confusion on this subject when I look around the web for it.
Personally, I'd set up a testbed and try out some options on it. That'll be more definite than just reading about it, anyway.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2011 at 12:38 pm
If you're doing differential backups, be careful. VSS will invalidate the last full backup, and the differentials will fail. (Found out the hard way when differentials starting failing... investigation found that the SAs started doing VSS backups on servers. I had to disable the SQL VSS service.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 13, 2011 at 2:56 pm
When you say 'consistent' I am assuming you want the databases to be backed up at exactly the same time.
I am not familiar with NTBackup and how it implements VSS - but, to make sure all databases are backed up at exactly the same time so they can be restored, VSS would have to freeze all databases, create the snapshot, unfreeze and then backup from the snapshot.
The most reliable way of doing this is to make sure all data files (mdf/ndf) are all on the same volume and freeze the volume. In other words, you want to create a snapshot of the volume where all databases reside - instead of each file.
Most SAN's now have utilities available that do this (e.g. Snap Manager for Netapp). I would investigate what options you have from your SAN vendor. Normally, I don't recommend using these utilities, but when you have to have the database backups synchronized to a point in time - this seems to be the best option.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2011 at 3:15 pm
Backup does nothing to the transaction logs. Not sure what you mean by "reorganize" here, but a backup doesn't affect t-logs.
The thing is you database file and your external files are not necessarily going to be in the same stable state when NTBackup grabs them. The reason is that it takes time to get all the changes written. I don't know how stable/reliable VSS is, though people do use it.
What I would wonder is why you couldn't run a SQL backup and copy your external files from the same job to a location and then back them up?
January 14, 2011 at 1:49 am
WayneS (1/13/2011)
If you're doing differential backups, be careful. VSS will invalidate the last full backup, and the differentials will fail. (Found out the hard way when differentials starting failing... investigation found that the SAs started doing VSS backups on servers. I had to disable the SQL VSS service.)
Oops, this sounds startling. Xould you be a bit more specific about what I need to do to reproduce this problem? Specifically: do you mean doing the full and differential Backup using NTBackup, or using SQL Server backup, and what exactly does happen?
Armin
January 14, 2011 at 2:05 am
Jeffrey Williams-493691 (1/13/2011)
When you say 'consistent' I am assuming you want the databases to be backed up at exactly the same time.I am not familiar with NTBackup and how it implements VSS - but, to make sure all databases are backed up at exactly the same time so they can be restored, VSS would have to freeze all databases, create the snapshot, unfreeze and then backup from the snapshot.
That's exactly what VSS does. See, for instance, the outcome of it:
"IO freeze" does not mean, that the SQL server stops responding, the term is a bit misleading. While the snapshot operation is in progress, newly written data is redirected to an alternative location till the operation is over.
Armin.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply