February 15, 2016 at 10:07 am
Trying to avoid replication so looking at homegrown solution to moving data between a 2008r2 database and 2014 DWH source database.
My plan was take a full backup of 2008r2 db restore to 2014 server. Then during the day restore the log backups from 2008r2 server to the 2014 server. Full backup and restore worked, but it did not like the log restore giving error on breaking log chain.
? is this a workable solution. This is an oltp database about 120 gig so doing full backup and restore during the day on the OLTP is not really workable
February 15, 2016 at 10:17 am
Restoring up-version and applying logs requires that the DB be left in the RESTORING state, meaning it can't be accessed at all. As soon as you RESTORE ... WITH RECOVERY, bringing the DB online, you can't restore more log backups and will have to start over by restoring the full backup to apply more logs.
STANDBY on log restores leaves the DB read-only, but is not an option when restoring to a higher version.
Consider transactional replication.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2016 at 12:30 pm
really need to avoid the replication, this is a 3rd party app/db which is OLTP where the vendor will scream about anything outside the box impacting anything including replication. With backups I can play in the sandbox and not get in trouble. i was wrong it is going from 2012 to 2014
February 15, 2016 at 1:48 pm
2008 R2 to 2014 or 2012 to 2014 doesn't make the slightest difference. You can't RESTORE ... WITH STANDBY, hence the only way to make a restored DB readable is to RESTORE ... WITH RECOVERY and once you've done that you can't restore more log backups.
As a once-a-day snapshot of the data, what you propose is doable. Drop the DB each night, restore full and logs, bring DB online. Repeat each night.
As an up-to-the-minute view of the data, it's not going to work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply