March 26, 2010 at 12:04 pm
I have a database that I am looking to log ship from SQL 2000 to SQL 2008. Using the norecovery option I can get this working. What I am trying to figure out though is how to see if a transaction log is currently being restored, or if I even have to worry about this.
If I run [RESTORE DATABASE RandomDB WITH RECOVERY;] while a transaction log is being restored I do not know exactly what happens. Does that command fail right away, does it just get queued up and execute after the transaction log restore finishes.
March 26, 2010 at 1:44 pm
you cannot logship to a higher version of SQL. The database needs to go through an upgrade stage and any deprecated commands would fail.
---------------------------------------------------------------------
March 26, 2010 at 4:30 pm
You can in fact log ship to a higher version of SQL using the no recovery option. You just are unable to read any data from the database untill you run the restore database with recovery command. It just stays in the restoring state.
March 26, 2010 at 5:34 pm
why are you trying to do this?
I would be interested to know if this does work, would seem prone to failures on deprecated code to me and I would be concerned about this scenario. Have you actually run recovery yet?
I tried this on SQL 7 to SQL 2000 once and got failures on the log restores (not all of them, after certain commands, reindexes I think)
As for simultaneous restores based on the fact a restore needs exclusive access I would bet the second one to kick off would fail.
activity monitor probably best way to see if restore log actually running, you would be checking to see if latest log is applied anyway before running recovery.
Post back on this I could find nothing else on the subject.
---------------------------------------------------------------------
March 26, 2010 at 9:24 pm
Just to add to George's comments...
According to both the 2005 and 2008 versions of Books Online:
SQL Server 2005 Standard, SQL Server 2005 Workgroup, SQL Server 2005 Enterprise Edition, or a later version, must be installed on all server instances involved in log shipping.
Configuring Log Shipping - 2005
Configuring Log Shipping - 2008
Any other configuration, though it might work under some circumstances, would be unsupported. I would strongly encourage you not to do this.
There are many ways to monitor log shipping, including running sp_help_log_shipping_monitor
RESTORE DATABASE...WITH RECOVERY will be blocked by any concurrent log restore operation.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 27, 2010 at 11:04 am
The reason I need to be doing this is because of a failover solution we have. It was originally built when we were running everything on SQL server 2000. But as we have been migrating to SQL Server 2008 over the past year some of the databases that are on this particular failover system needed to be upgraded. This entire thing is built in a second data center and has no access to the main data center other then the ability to access the backups for log shipping. So I am forced to log ship from SQL 2000 to SQL 2008. I have already tested that this database runs fine under 2008 so I am not really worried about that. Only had a few indexes that needed some minor tweaks for performance.
Since a majority of my environment is using the SQL Server Web license I can't really use any of the built it log shipping functionality, so I have created all the stored proc's and jobs and everything that I need for log shipping to work and it all works quite nicely. But now I am helping build a front end for the fail over step so some one can click a button and everything will switch to the second data center and I was concerned on how things would react if a log restore was running when that happened. From the sounds of it I would probably get a failure so I can most likely just put a retry in so that it waits a minute or two and tries again.
March 27, 2010 at 1:26 pm
why are trying to restore database when log restore is in progress
March 29, 2010 at 1:44 pm
Ever thought of doing transactional replication instead? I've setup 2000 to 2008 with no problems.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply