Restore job run successfully without error but actually not applied during log ship from older version to newer one

Question

Our requirement is for SQL Server 2008 R2 databases to be upgraded/migrated to SQL Server 2016. We want to configure log shipping between SQL Server 2008 R2 (source) and SQL Server 2016 (target). We have also need to open these standby database after a long time (For example 1 month), while transaction logs will be deleted after two days. Is this possible?

Currently we have configured 2 test instances using NORECOVERY with Log Shopping jobs (Backup, Copy and Restore) running every hour, while Log history of these three jobs shows no error. i.e. Backup executes successfully on 2008R2 instance, Copy and Restore jobs execute fine on 2016.

However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured. It seems transaction logs are not being applied. Also, when we try to open the database on 2016 using Restore -> Database, it reports some transaction log missing (because we configured to delete older than 2 days transaction logs and hence these were deleted after 2 days).

It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm! Would you please show whether there is any way it can apply transaction logs while in this UPGRADE setup as explained above and let older than 2 days transaction log files deleted as per scheduled till we actually upgrade to 2016?

Answer 1

You cannot restore using STANDBY from a lower version. If that is what you mean nu "open these standby databases after a long time).

STANDBY implies a recovery, which would need to physically upgrade the database. An database cannot be downgraded again, which in turn would be necessary when restoring a subsequent log backup.

Timestamp of database files are irrelevant. Don't bother with those. They aren't updated when information inside the file is modified.

You can keep the database in a restoring state for a long time, i.e., log shipping doing restore using NORECOVERY. But you need to have a schedule between the three agent jobs so that the restore job actually have the backup file available. So, for instance backup every hour, copy file every hour, restore (NORECOVERY, of course) every hour. And then delete the backup files after, say 1 day. That way the files neeeded for the restore will be on disk.