January 23, 2014 at 7:24 am
Hi all
I am trying to set up a script to automatically backup and restore a few instances
One of the instances have a database that is replicated to another instance, so for this to work i have to drop the replication, drop the replicated database, "refresh" the database with a new copy (a restore from production) and then set up the replication again.
my script does all this.
the problem is when the replication is done i want to apply some permissions to the new database on the subscriber side.
usually, i do this after the replication monitor tells me that the "snapshot has been delivered"
but, since i want this to be run on schedule every now and then, i want to script this bit too.... how do i do that?
how do i know when the replication has delivered the snapshot without using a gui? there must be some logfile i can monitor or some system table that keeps track of all this?
any ideas?
January 23, 2014 at 1:19 pm
unfortunately, i think you are pretty much out of luck on this. If you know the time when it was removed, you may be able to go through the Security event log to see who was logged in at the time, but that is not much of a smoking gun.
EDIT: Dang it all. This post was supposed to be in response to a different thread. Sorry about that.
January 23, 2014 at 11:49 pm
You will find what you want somewhere in the distribution database tables.
http://technet.microsoft.com/en-us/library/ms179855(v=sql.100).aspx
January 24, 2014 at 2:58 am
I'm not sure I follow all of this but I'll do my best.
joackim.breien (1/23/2014)
the problem is when the replication is done i want to apply some permissions to the new database on the subscriber side.usually, i do this after the replication monitor tells me that the "snapshot has been delivered"
but, since i want this to be run on schedule every now and then, i want to script this bit too.... how do i do that?
If I wanted to delivery changes to a subscriber after every snapshot is delivered (regardless of what instigated it) I would use a post snapshot script.
http://technet.microsoft.com/en-us/library/ms152525.aspx
joackim.breien (1/23/2014)
how do i know when the replication has delivered the snapshot without using a gui? there must be some logfile i can monitor or some system table that keeps track of all this?any ideas?
This isn't possible. All the gui does is call the same stored procedures you do through TSQL so there is no differentiating between the two.
January 24, 2014 at 5:14 am
MysteryJimbo (1/24/2014)
I'm not sure I follow all of this but I'll do my best.If I wanted to delivery changes to a subscriber after every snapshot is delivered (regardless of what instigated it) I would use a post snapshot script.
This looks like gold! i will see if i can get it to work, thanks alot!
/J
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply