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?