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.
Yes, you can restore the logs by changing the settings for the Restore Job to be set to STANDBY, instead of NORECOVERY. This would provide a degree of Read Only access until the next restore is run.
If you are using the log shipping functions that come with SQL Server you use the settings shown by Ali Razeghi for using sp_change_log_shipping_secondary_database.
If you have rolled your own log shipping process (which many people have done) then you are already controlling the restore process yourself.
EDIT: Since you restore the secondary one time a day, the READ ONLY window will be most of a day. So that eases the scheduling issue.
Since the procedure sp_change_log_shipping_secondary_database is not working, either (1) you ran this on the primary instead of the secondary server, or (2) your organization made their own custom log shipping process.
EDIT: If you are in the (2) state, then please include the details of the secondary server's Log Shipping Restore Job.
However, a database in NORECOVERY can be updated to STANDBY like this:
RESTORE DATABASE [MyDatabase]
WITH STANDBY = N'D:\MSSQL\Backup\MyDatabase_RollbackUndoFile.bak'
Any time the Restore Job recovers a series of LOG files, it will disconnect any STANDBY users that are still connected.
Also see Kendra Little's post: Reporting From a Log Shipping Secondary in STANDBY mode Contained in here post is a mention of the problem that you have run into with restoring a 2008 database to a 2008 R2 Secondary.
You can’t use “STANDBY” Mode if your Secondary is a Higher Version
I was a little sad when I learned this one years ago. I had a SQL
Server 2005 instance that was the log shipping primary. The log
shipping secondary was made readable so that developers could check
out production data if needed without having access to production.
This is because the database pages need to be upgraded to the 2008 R2 level in order to be readable by STANDBY, but that is a violation of keeping the Secondary exactly like the Primary.
Really the only route to getting STANDBY mode on your secondary is to upgrade your SQL Server 2008 to SQL Server 2008 R2 so as to match the secondary. (Or
(if you want to modernize) to upgrade to a higher version for both primary and secondary.)
Best Answer
The user has to be granted permissions in the source copy of the database.
The restored copy is just that -- a copy -- of the source, so there's no way to change one and not the other.
Since this is a SQL Server login, you'll need to create the logins on both servers with identical SIDs so the mapping from the database user to the login matches in all cases. You can do this by recreating the login on the secondary, specifying the SID manually in the
CREATE LOGINstatement.If you went with a solution such as replication (not saying you should, just giving an example), this works using two read/write databases, where you could grant permissions completely independently.