I make every day at 0300 AM a Full Backup (NOT a COPY-ONLY) of a database (11 GB) and every minute (from 03:05AM till 02:55AM) a TRANSACTION LOG Backup.
When I tried to Restore to a point in Time eg 03:07AM there is an error that the backupsets are not inline.
When I took a look at the backupset table in msdb database I see all the backups (FULL en LOGS), but the first_lsn of the First TRNLOG after a FULL Backup is not the same as the last_lsn of the FULL backup, but it is the same the last_lsn of Prev TRNLOG (which is before the FULL backup).
So it is not possible to restore the FULL backup and eg the First TRNLOG backup.
What is going wrong in my backup process?
Best Answer
To restore to 03:07 AM you would require the following backup files:
SW_SAM_GEMAAL_backup_2017_09_21_030159_6588486
(FULL)SW_SAM_GEMAAL_backup_2017_09_21_030501_0515333
(TLOG)SW_SAM_GEMAAL_backup_2017_09_21_030601_6958001
(TLOG)SW_SAM_GEMAAL_backup_2017_09_21_030701_.......
(TLOG)SW_SAM_GEMAAL_backup_2017_09_21_030801_.......
(TLOG; possibly)In your screenshot you are only listing the files up until the
SW_SAM_GEMAAL_backup_2017_09_21_030601_6958001
TLOG backup. However, this file does not contain any modifications past 03:06:01 AM and this is possibly why you are unable to restore to the point-in-time at 03:07:00 AM.The FULL backup is always consistent as is stated by the official Microsoft (Docs) documentation (emphasis mine):
Reference: BACKUP (Transact-SQL)
Depending on your error message, you might find that because you ommitted one TLOG backup file, that the restore is failing.
Reproducing Issue
Inserting data / creating backups
I reproduced your situation on my local SQL Server with the following steps and was able to restore to a point-in-time with a transaction still open during the FULL and TLOG backups:
Backup History
My backup history looked like this:
Restoring Database
I then restored my database to 11:28:00 AM with the following statements:
Solution
Add your last TLOG backup of your database to the restore statement and you should be able to restore to your point-in-time at 03:07:00 AM.