How does backup work in SQL server?

Question

Hi,

Let's say I have a very large database. Its backup starts at midnight and finishes at 02:00 am.

When I recover that backup am I gonna have the state of the database at midnight or 02:00 (or something else)?

Going one step further, let's say that I have multiple databases which contain references to each other's elements. If I backup all databases in parallel starting at the same time, when I recover all of them am I going to have the state of all databases at the same point in time?

Answer 1

The backup process in SQL Server backs up the data files and portions of the transaction log, so it'll be the state of the database at 2am.

You can only guarantee consistency of committed transactions across databases if the backup stops at exactly the same time. This is probably not something you want to rely on, so I'd recommend using frequent transaction log backups in addition to whatever nightly full strategy you're using.