SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A Full Backup Includes Everything (with a caveat)

Full database backups in SQL Server include all of the data, objects, tables, rows, functions, stored procedures, etc. If something is in the database when the data reading portion of the backup concludes, it’s in there.

Note that “in there” means committed in a transaction.

If someone tells you the backup missed a row, or a procedure, or something else, they’re almost always wrong. 99.9999% of the time they are wrong, and you should stand by that.

The issue is that things must be committed in the backup. If they aren’t committed, they aren’t included. And that means that there’s a small chance that something added to the database while the backup is running isn’t in the backup.

If you remember how backups work, there’s a data reading portion of the backup and a log writing portion of the backup. The log writing portion of the backup takes a physical amount of time. If someone were to finish a transaction during this time, the data or objects would not be in a restored database. This is because the transaction didn’t exist or wasn’t committed when the data reading portion of the backup completed.

If the data writing portion of the backup takes a few minutes, and a change was in the last minute or so of the process, someone might think something completed before the timestamp on the backup file is included. It wouldn’t be.

It’s a small chance, and it’s not likely to come down to this point, but it could happen. Can you figure out the transactionally consistent time of the backup? Perhaps, but I don’t know how. You’d need to get the time for the last LSN written in the backup and map that to a time. If you know how to do that, let me know.

Make sure that your backups are transactionally consistent. Don’t export, don’t use open file managers, don’t use anything that doesn’t respect transactions. The native SQL Server backup process does this. If you want a few other features, my employer makes SQL Backup Pro, which also respects transactions.

Filed under: Blog Tagged: administration, Backup/Recovery, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...