NTBackup, VSS and SQL Server 2000 question

Question

Post reply

NTBackup, VSS and SQL Server 2000 question

Frank Rosser

SSC Eights!

Points: 970
More actions
September 11, 2007 at 3:44 am

#174636

I understand that NTBackup will detect SQL Server database files on a volume that it is copying to a shadow volume.
How does it detect the SQL Server database files?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Erik Bitemo SSCrazy Points: 2062 More actions · Answer 1

Hi,

NTBackup uses VSS to create consistent file backup of your data files. Enter the following command on your server:

vssadmin list writers

. This will list the writers -and there's the SqlServerWriter. When NTBackup reaches the datafile, SqlServerWriter waits for a moment enabling NTBackup to create a snapshot without torn pages. So you'll have a consistent datafile, which is not clean but SQL Server can recover it without issue. However, if you have multiple data files then obviously you might be in trouble...

So in short: NTBackup doesn't detect SQL data files, it requests a write pause for a moment from the VSS writer - who is the SQL Server.

-- Erik http://blog.rollback.hu

Frank Rosser SSC Eights! Points: 970 More actions · Answer 2

Erik,

thanks for the info.

Another question - does the VSS writer pause all the SQL Server database file writes on the volume being shadowed, or does it pause ALL writes from ALL the SQL Server instances on the machine.

The reason I am curious is I need to guage the impact on all the SQL Server databases hosted on the machine, in this cases there are also multiple instances.

Frank

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 3

I'm not 100% sure, but I believe it works a file at a time, in other words, it has it's own queue of writes to move, so the pausing would move from file to file, and potentially instance to instance.