Log shipping from SQL server 2008 to 2008 R2

Last Post 19 Nov 2010 06:32 AM by samuelh. 14 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
samuelh
New Member
New Member

--
15 Nov 2010 12:26 PM
I'm setting up log shipping between two databases. The active database sits on SQL server 2008 and the destination Standby server will be on SQL server 2008 R2. When I try to restore the database to the destination server (2008 R2) I get the following message:

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

I thought that log shipping works from a previous version to newer version SQL but not vice versa. Do I have to upgrade my SQL server 2008 to version R2 in order to get the log shipping going?

Many thanks in advance.




gunneyk
New Member
New Member

--
15 Nov 2010 01:52 PM
Do the first restore with RECOVERY not STANDBY so it can do the upgrade and then you should be able to use standby for subsaquent restores.
samuelh
New Member
New Member

--
16 Nov 2010 06:49 AM
Once you restore WITH RECOVERY, the database will be ready for use, and therefore additional transactional logs cannot be restored. The main purpose of log shipping is to be able to restore log files every so often to have a standby server/database.

Unless what you're suggesting is, restore the database WITH RECOVERY, then back it up from the 2008 R2 server and restore it back with WITH STANDBY option and maybe try and load the trn files from the 2008 server. I doubt that this will work either.

Thanks
gunneyk
New Member
New Member

--
16 Nov 2010 08:52 AM
Sorry I meant with NORECOVERY. You have two options for log shipping. Standby so you can read it or NORECOVERY that only allows more restores.
samuelh
New Member
New Member

--
16 Nov 2010 01:28 PM
gunneyk...........again thanks for the help on this.
I tried restoring it WITH NORECOVERY. It restores the database and stays in Restoring Mode. Cannot read from it. I want to be able to restore WITH STANDBY, so that I can also read from the database.

I guess the only option here is to upgrade from 2008 to 2008R2.


gunneyk
New Member
New Member

--
16 Nov 2010 03:15 PM
Try restoring the database with NORECOVERY and then restore the next tran log with STANDBY. When you restore the db it should do the upgrade and then from then onward you should be able to restore the log backups with STANDBY and be able to read from it.
samuelh
New Member
New Member

--
17 Nov 2010 05:47 AM
Have already tried that. Doesn't work!
gunneyk
New Member
New Member

--
17 Nov 2010 06:41 AM
When you say it doesn't work what is the error message?
samuelh
New Member
New Member

--
17 Nov 2010 09:12 AM
This backup cannot be restored using WITH STANDBY, because a database upgrade is needed. Re-issue the restore without WITH STANDBY.
gunneyk
New Member
New Member

--
18 Nov 2010 04:23 AM
I am upgrading my current edition from 2008 to 2008R2 today and I will test but that still sounds like you are using STANDBY and not NORECOVERY. Others agree with me that NORECOVERY should work.
russellb
New Member
New Member

--
18 Nov 2010 06:54 AM
Don't think this is correct. Upgrade doesn't occur until the recovery phase. At least that's what I see when testing.
gunneyk
New Member
New Member

--
18 Nov 2010 07:43 AM
I just loaded R2 and tested this and Russell is correct. The upgrade happens when you actually do the full recovery. The NORECOVERY will work as a log shipping method but you cannot put it into StandBy until the db has been upgraded. So if you want to do read operations you will need to upgrade the source.
samuelh
New Member
New Member

--
18 Nov 2010 11:52 AM
Gunneyk and Russell.............thanks again for all your input. The conclusion here is that I must UPGRADE my source server, so that I can have my log shipping going, as long as I'm going to be reading from the Standby database. thanks again!
gunneyk
New Member
New Member

--
19 Nov 2010 06:26 AM
Yes if all you want to do is have a standby server then no change is necessary but if you want the ability to read you must upgrade the source.
samuelh
New Member
New Member

--
19 Nov 2010 06:32 AM
thanks again guys for all your input on this.



Acceptable Use Policy
---