Sql-server – log ship from older version to newer one

log-shippingsql server

As per below thread from SQL Server: Can I log ship from SQL Server 2014 –> 2016?

Always log ship UP as it's a basic supported upgrade process. you can't log ship DOWN. The question you might be concerned with is how do you roll back and not lose data if you need to go back to 2014 as you can't restore 2016 DBs to 2014

We need to set this up for our environment to log ship from older version to newer one, i.e. from 2008R2 to 2016 and getting following error:

Error: This backup cannot be restored using WITH STANDBY because a database
upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Would you please advise whether this can be done at all? If so, what we are supposed to do to get it working?

Best Answer

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server. The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only. This is documented in RESTORE Statements - Arguments (Transact-SQL):

STANDBY is also not allowed when a database upgrade is necessary.

You can use NORECOVERY rather than STANDBY for the restores instead. You won't be able to use the new server as a readable copy of the database, which might be a dealbreaker. But it supports the scenario of migrating to the newer server (if that's what your end goal is).

I would double check your configuration. Specifically, check that you selected "No recovery mode" for the "Database state when restoring backups" option on the Restore tab for the target (secondary) database. It's step 17 in Configure Log Shipping (SQL Server) - Using SQL Server Management Studio

On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

You can review the settings by right-clicking the database, choosing properties, and then looking at the "transaction log shipping" tab.

Clarifying a few questions and statements from the comments:

It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm!"

No, that's not how it works. The transaction logs are applied during the RESTORE LOG operation. So the deleted source transaction logs aren't a problem. If you're getting that error about missing transaction logs, then it sounds like some of the restores failed, or they were restored out of order, etc.

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

You can't use Windows/NTFS timestamps as a valid indication of the SQL Server data files being used, and thus it can't be used as evidence that the transaction logs are not being applied when you restore them.

Related Solutions

SQL Server – How to Restore Backup Without WITH STANDBY

WITH STANDBY is only supported when both SQL Servers are the same version.

You can only use WITH NORECOVERY

An alternative if you need read access to the destination databases, is to use replication.

SQL Server 2008 R2 – Unexplained Log Ship Slow Down

This is bigger than a comment but something to test first and then implement :

You are logshipping 800+ databases. Thats a large amount of databases that you logship every 15 mins.

You should offload some databases to another server. IMHO 800 databases on a single server is a lot!

We had similar problem with logshipping when we logshipped 200+ databases from NY to LD region.

What we did is as below :

There was blocking writing to msdb.dbo.sysjobhistory with (TABLOCKX). The TABLOCK hint means that access to the sysjobhistory is always serialized. And since you have lot of jobs running every 15mins, there will be contention (blocking).
We implemented trace flag TF – 1236. It will introduce Database lock partitioning. Partitioning the DATABASE lock keeps the depth of the lock list manageable in each local partition. This significantly optimizes the access path that is used to obtain a DATABASE lock.

create indexes on sysjobhistory and log_shipping_monitor_history_detail tables as below :

use msdb
    go

    create nonclustered index [nc_DBA_sysjobhistory] on dbo.sysjobhistory (
            ,[job_id] 
            ,[step_id]     
            ,[run_date]
            ) 
    include (
            [instance_id]
            ,[step_name]
            ,[sql_message_id]
            ,[sql_severity]
            ,[message]
            ,[run_status]
            ,[run_time]
            ,[run_duration]
            ,[retries_attempted]
            ,[server]
            )
    go

    use [msdb]
    go
    ----- this will help sys.sp_MSprocesslogshippingretentioncleanup proc (delete from msdb.dbo.log_shipping_monitor_history_detail).. that does the cleanup of logshipping.

    create nonclustered index [nc_DBA_LogShipping_monitor_history_detail] on [dbo].[log_shipping_monitor_history_detail] (
            [agent_id] asc
            ,[agent_type] asc
            ,[log_time_utc] asc
            )
    go

Best Answer

Related Solutions

SQL Server – How to Restore Backup Without WITH STANDBY

SQL Server 2008 R2 – Unexplained Log Ship Slow Down

Related Question