SQL Server backup versus third party snapshot based backup using VSS

Question

Is it safe to assume that SQL Server instance or database level backup to a NAS share or tape using the SQL Server backup command (internally create .bak file) are transactionally consistent backups?

Your database can be online while a backup to a shared drive is in progress. How does this type of backup guarantee application consistency?

I am comparing this sort of backup against VSS-aware hardware level snapshot, and the backup option using a third party tool, which claims 100% application consistent backup. VSS performs actions like freezing the IO till snapshot is completed.

How does SQL Server native backup maintain its consistency in absence of VSS?

Answer 1

SQL Server uses checkpoint, log sequence numbers, and the transaction log to guarantee that a full backup is restored to a transactionally-consistent state, as of a particular point in time.

The exact point in time for a full database backup is the end of the data-reading portion of the backup process. To recover to an arbitrary point in time requires separate transaction log backups as well.

Online backup means the database remains accessible (and writeable) for other users while the backup is in progress. Enough of the transaction log is saved with the backed up database pages to enable recovery to bring the whole database to a transactionally consistent state when restored.

The basic idea is that pages saved to the backup are already consistent to the target point in time, or can be made so by undoing or redoing transactions from the included portion of the transaction log.

For full details see the TechNet article Understanding SQL Server Backups by Paul S. Randal.