SQL Server Backup – Restore to Start or Finish Time?

backupsql serversql-server-2012

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?

Best Answer

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.

Related Question