Backstory: I'm trying to restore copies of several SQL 2012 databases to a different environment, but the application needs them to be (roughly) "synchronized" in time.

(Of course the ideal way to do this is by using FULL recovery mode using full and log backups, and then do point-in time restores using the same timestamp. But in my case, I don't need them perfectly synchronized, just within ~10 minutes or so, and I'd rather deal with only full backups and restores. I do have control over when the full backups run, but the problem is that the databases are of very different sizes, so the full backups run for very different lengths of time.)

So my question: should I synchronize the start time of the full backups? Or the finish time?

In other words, when I restore a full backup, will I end up with how it looked when the backup started, or when it finished?

  • 2
    you can use transaction marks when taking backups and to recover them to a logically consistent point. I have used that for sharepoint databases and it works pretty well.
    – Kin Shah
    Commented Jun 1, 2018 at 14:55

3 Answers 3


Check out this link

A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Even though it may take many hours for the backup process to run, you can still only restore the backup to a single point (effectively at the end of the backup, but I'll discuss exactly what that point is later in this article). A full backup does not allow recovery to any point in time while the backup was running.

  • 3
    Thanks, that's a very on-point quote. For anyone who doesn't want to read the whole thing, Randal clarifies later on that the "exact point" is when the backup process is finished reading the data files, but before it reads through the tran log to capture transactions that ran during the full backup. So, pretty much the finish time.
    – BradC
    Commented Jun 1, 2018 at 16:12
  • The full backup also utilizes the records in the tail log. Does that mean the backup contains data uptill the last completed transaction? If so, then in your comment above, why do you say "before it reads through the tran log to capture transactions that ran during the full backup"?
    – variable
    Commented May 28, 2020 at 15:03
  • 2
    @variable Say a backup started at 10pm, finished reading the data files at 11pm, and took an additional 2 minutes to capture the tail log before the backup completed. Restoring that backup would bring you to how it looked at 11:00pm not 11:02pm (or 10pm). So it does use the tail log in the backup, but it uses it to bring you back to the moment just before it starts reading the tail log, if that makes any sense.
    – BradC
    Commented May 28, 2020 at 20:14
  • @BradC - please can you advise why does it use the tail log during backup? Is this related to undo/redo transactions?
    – variable
    Commented May 29, 2020 at 4:17
  • @variable It uses it to capture all the activity that was going on during the time it was reading the data files for backup. So in my example above, the completed transactions between 10pm and 11pm.
    – BradC
    Commented May 29, 2020 at 13:01

A Sql Server backup contains all allocated data pages and enough of the transaction log so a restore operation can recover the database so it is transactionally consistent as of the point in time when the read data operation completed.

So, to answer your question

What is the “moment” of a full backup? The START time or the FINISH time?

It's the FINISH time.

It should be very easy to prove this to yourself by invoking a backup operation, making some changes to some data while the backup is running (making sure to commit them) and then restore that backup somewhere and check for your changes.

Referencing More on how much transaction log a full backup includes

  • Thanks for the link. I would consider Paul Randal an authoritative reference on this subject.
    – BradC
    Commented Jun 1, 2018 at 16:53

Any SQL backup is done when it writes the backup end time to the backupset table in the msdb database. When you search in there full backups are type D and log backups are type L. And there is a copy-only column to mark copy-only backups

I run the following query periodically against a group I created on our central management server and you can modify it for your needs.

select  a.server_name as hostname,   a.Database_Name,a.name, 
--over (partition by database_name) 
as Backup_End_Time
,Backup_Type = 
case a.type
when 'D' then 'Full backup'
when 'I' then 'Differential Backup'
when 'L' then 'Transaction Log Backup'
when 'F' then 'filegroup Backup'
when 'G' then 'Differential File backup'
else 'See Books Online'
end, cast((a.backup_size/1048576)as bigint) as Size_of_Backup_MB , 
a.software_major_version, a.compatibility_level, a.has_backup_checksums, 
from msdb..backupset a
inner join sys.dm_hadr_availability_replica_cluster_states b on 
a.server_name = b.replica_server_name
--inner join sys.dm_hadr_availability_replica_cluster_states e on 
d.server_name = e.replica_server_name
inner join sys.dm_hadr_availability_replica_states c on c.replica_id = 
inner join sys.databases e on a.database_name = e.name
where exists 
(select 1 from msdb..backupset d
            where a.server_name = d.server_name 
                and a.database_name = d.database_name
                and d.is_copy_only = 0
                having a.backup_finish_date = max(d.backup_finish_date))
 and a.Type = 'd'
 --and a.name in ('CommVault Galaxy Backup')
 --and a.has_backup_checksums = 0
 --and c.role_desc = 'primary'
 group by a.backup_finish_date, a.database_name, a.server_name, a.Type, 
 a.backup_size, a.compatibility_level, a.database_version, 
 , a.has_backup_checksums, a.is_copy_only, a.name, a.description
 having max(a.backup_finish_date) < getdate() -2
 order by a.server_name, a.database_name  
  • What OP is asking is how to make backup synchronized between different databases ... Also, based on your edit .. you need to remove and a.name in ('CommVault Galaxy Backup')
    – Kin Shah
    Commented Jun 1, 2018 at 15:00
  • 1
    I know how to find the start and finish times, but my question is about whether a restore will include all the transactions that occurred between the start time and finish time, or whether full backups do some sort of snapshot thing and therefore contain the database as it was when the backup began.
    – BradC
    Commented Jun 1, 2018 at 15:03
  • I have a bunch of AlwaysOn clusters in a CMS group I run this against. I usually run this to make sure Commvault isn't skipping anything but sometimes I comment out the next to last line to get all the backups. OP can comment it out and run this against the servers he needs to compare and it will let him/her know when the backups complete on each server.
    – Alen
    Commented Jun 1, 2018 at 15:03
  • Sorry, that's a nice script, but it doesn't answer my question.
    – BradC
    Commented Jun 1, 2018 at 15:06
  • As I understand it the full backup will read the transaction log and backup the transactions that take place during the full backup. Any transaction that doesn't complete will be rolled back during the restore. Backupset also has the LSN's so you can track which ones are backed up.
    – Alen
    Commented Jun 1, 2018 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.