June 24, 2011 at 9:41 am
Ok,
Microsoft says
Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.
I'm curious... we have a need to take down a replicated database, restore it to our ETL environment and basically merge another business entity's data into said database, then stick it back on the server and have replication pick up where it left off.
I was thinking that the only way that we might do this would be to.
1. Clone a VM image of the SQL server
2. Bring the VM up here
3. Disable the replication agent without removing replication for the publisher database
4. Perform the ETL/Merge
5. Backup the publisher database, msdb and master from the VM and return them to our customer
6. Customer performs restore of master/msdb and then the replication publisher database and then re-enables the replication agent.
The reason for this rigamarole is that we have triggers setup on the subscriber database which essentially flags data as it comes in so that the customer can call some stored procs we've written to get new data as it comes in. We want the new data from the merge to get caught by these triggers, but not the existing data that would be updated by a reinitialization of the replication.
I hope what I have typed above is clear, the requirements were given to me about 15 minutes ago and I'm still trying to work my way around to a method that might help them. I don't even know at this point if all the steps above can actually be done or not.
That said, any thoughts about my thoughts above?
June 25, 2011 at 7:17 am
in theory, provided you disable the log reader agent and allow the distribution agent(s) to complete its run before disabling them you could get away with this.
This is what I would consider testing.
1. Disable log reader agent
2. Disable Distribution agents
3. Take full back up of database
4. Restore database with "KEEP REPLICATION" option
5. Perform ETL
6. Go to step 3.
.
Enable replication agents again
If you do decide to copy the VM, start at step 5 and make sure the agent jobs are stopped and disabled FIRST! This will retain the transaction log consistancy for when you restore the full backup again so the log reader can pickup where it left off. No transaction log backups either to be safe.
I dont think you'll need to touch the system databases in this case as the publication settings should be retained.
Test it first though 🙂
June 27, 2011 at 6:50 am
MysteryJimbo (6/25/2011)
in theory, provided you disable the log reader agent and allow the distribution agent(s) to complete its run before disabling them you could get away with this.4. Restore database with "KEEP REPLICATION" option
The one problem with this is BOL
Books Online
KEEP_REPLICATIONSupported by: RESTORE
KEEP_REPLICATION should used when setting up replication to work with log shipping. It prevents replication settings from being removed when a database backup or log backup is restored on a warm standby server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted. To ensure replication functions properly after restore:
The msdb and master databases at the warm standby server must be in sync with the msdb and master databases at the primary server.
The warm standby server must be renamed to use the same name as the primary server.
Based on this, the restore can't be done with KEEP_REPLICATION
June 27, 2011 at 7:04 am
mtassin (6/27/2011)
MysteryJimbo (6/25/2011)
in theory, provided you disable the log reader agent and allow the distribution agent(s) to complete its run before disabling them you could get away with this.4. Restore database with "KEEP REPLICATION" option
The one problem with this is BOL
Books Online
KEEP_REPLICATIONSupported by: RESTORE
KEEP_REPLICATION should used when setting up replication to work with log shipping. It prevents replication settings from being removed when a database backup or log backup is restored on a warm standby server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted. To ensure replication functions properly after restore:
The msdb and master databases at the warm standby server must be in sync with the msdb and master databases at the primary server.
The warm standby server must be renamed to use the same name as the primary server.
Based on this, the restore can't be done with KEEP_REPLICATION
Thats true but why would you want no recovery? You want to edit the data?
Also, you can logship a replicated db. You only apply the KEEP REPLICATION option when bringing the standby database online.
June 27, 2011 at 7:11 am
MysteryJimbo (6/27/2011)
Thats true but why would you want no recovery? You want to edit the data?Also, you can logship a replicated db. You only apply the KEEP REPLICATION option when bringing the standby database online.
The plan was to ETL a bunch of data into the database and then send it back, databases not in no recovery don't accept much.
June 27, 2011 at 8:24 am
I think you're mixed up. NO RECOVERY leaves a database in Restoring status and therefore inaccessible.
Specifying this option when restoring a backup with the NORECOVERY option is not permitted.
This states KEEPREPLICATION and NORECOVERY together is not valid. You wouldnt be using them together if you want to add data, therefore its not a problem?!
I've just tested this and proved it works on a couple of local instances.
/* Disabled and stopped all replication jobs on distributor */
/* Backed up replicated db */
/* Restored replicated db to another name */
RESTORE DATABASE [altdbtobemodified] FROM DISK = N'C:\backup\modified.bak' WITH FILE = 1,
MOVE N'altdbtobemodified_Data' TO N'C:\SQLDATA\altdbtobemodified.MDF',
MOVE N'altdbtobemodified_Log' TO N'C:\SQLLOGS\altdbtobemodified.LDF',
KEEP_REPLICATION, NOUNLOAD, STATS = 10
GO
/* Confirmed rows before */
select * from dbo.tablename
insert into dbo.tablename
values (3, 2, 3,1, 120.00)
insert into dbo.tablename
values (4, 2, 3,1, 122.00)
/* Confirmed rows after */
select * from dbo.tablename
/* Backed up modified db */
/* Restore, overwriting existing replicated db */
RESTORE DATABASE [ReplicatedDB]
FROM DISK = N'C:\backup\ReplicatedDB.bak' WITH FILE = 1,
KEEP_REPLICATION, NOUNLOAD, REPLACE, STATS = 10
GO
/* Enabled and started replication agents */
/* Confirmed rows on published db and subscriber */
select * from dbo.tablename
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply