January 12, 2011 at 12:38 pm
The setup:
An application has "environment A" and a stand-by/failover "environment B". Each environment has one database. The db in A has environmental data specific to A, plus reporting data. The db in B has environmental data specific to B, plus the reporting data.
Say it's necessary to "failover" to environment B. We must leave the environment-specific data unchanged in B's database BUT we must bring over the current reporting data from A to B.
So the challenge is to restore just the reporting data from a backup taken at A but not overlay the environment-specific data at B.
Does anyone know a way to do this with the SQL backup/restore options? I thought of putting the reporting tables in their own filegroup and only restoring that filegroup at B but I don't think filegroup restores will allow that.
January 12, 2011 at 12:57 pm
Are you wanting to restore just specific tables from A into B ?
January 12, 2011 at 1:06 pm
Yes, that's really it. The solution might be to restore the full backup from A onto B with a different temp name. Then truncate the reporting tables at B and then load those tables from the restored temp database. But that could be slow and cause a lot of transaction log growth...
January 12, 2011 at 1:10 pm
If you are using a 3rd party backup tool that has "Object Level Recovery", then perhaps you could select just those specific tables from your "A" backup to restore to your "B" database.
January 12, 2011 at 2:00 pm
Thanks. We use Red Gate SQL Backup but I don't think it has an object level recovery option.
January 12, 2011 at 2:18 pm
How much environment specific data is there? Could you script it out as updates and run it after a full restore over database B, or truncate and load the environmental tables only.
Or perhaps transactional replication would be the best option here as you need a 'logical' failover, perhaps even snapshot replication depending on latency you can afford.
---------------------------------------------------------------------
January 12, 2011 at 5:02 pm
Those are good ideas. Replication might be the answer.
January 13, 2011 at 7:36 am
I agree. It sounds like you're in a situation where replication would work better.
As far as Red Gate's SQL Backup goes, yes, you can restore objects and rows if you're using 6.0 and the Pro version. It's in the faq, just scroll down a bit. Here's the sales pitch on it. Here's the documentation. But there are some limits that are likely to cause issues for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply