3

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?

  • 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 Jun 1 '18 at 14:55
8

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 Jun 1 '18 at 16:12
4

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 Jun 1 '18 at 16:53
0

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, 
a.backup_finish_date
--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, 
a.is_copy_only
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 = 
b.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.software_major_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 Jun 1 '18 at 15:00
  • 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 Jun 1 '18 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 Jun 1 '18 at 15:03
  • Sorry, that's a nice script, but it doesn't answer my question. – BradC Jun 1 '18 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 Jun 1 '18 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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