We have got a legacy system. We are migrating to new platform. We took backup of production database on January 6, 2021 and restored in UAT environment.
Below query, we ran to get the backup datetime of the production database from UAT environment. Reference Article
SELECT
[rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] AS [source_database_name],
[bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM
msdb..restorehistory rs
INNER JOIN
msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN
msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY
[rs].[restore_date] DESC
| destination_database_name | restore_date | backup_start_date | backup_finish_date | source_database_name | backup_file_used_for_restore |
|---|---|---|---|---|---|
| our_DB | 2021-01-06 06:02:21.620 | 2021-01-06 00:12:55.000 | 2021-01-06 00:39:06.000 | our_DB | E:\our_DB.bak |
In production database, we keep processing inbound flat files. We process files and commit them, as and when they come.
We want to pull the list of delta flat files, which came after we took the backup and not already processed.
So, should we consider backup_finish_datetime (2021-01-06 00:39:06.000) or backup_start_date (2021-01-06 00:12:55.000) for picking up delta files from production ?
Considering the complexities involved in figuring out the committed transactions and ongoing transactions at the backup finish time, we decided, not to go with the backup finish time as delta time.
We decided to go for all incremental delta files, since start of the day (2021-01-06 00:00:00.000) & we understand that there will be some duplicate entries and we have to handle it accordingly in the new system.
As we don't have audit columns, we decided to take this option. It highlights the important of audit columns.