Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

sql

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

deleting a replicated restore

I am running SQL 2K & Win 2K. I restored a backup, to a
different name, that was being replicated when it was
backed up. After the restore, I cannot detach the
backup, because SQL thinks it is being replicated. How
can I fix/detach this dB?
Larry,
have a look at sp_removedbreplication 'dbname' and sp_replicationdboption in
BOL.
Regards,
Paul Ibison
|||If sp_removedbreplication doesnt work you can update the category column in
sysdatabases to 0
Thanks
Gopal
|||If working inside the database, you may also have to manually set the
replinfo column in sysobjects to 0.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Deleting a replicated database..

I am new to SQL server...In case you couldn't tell.
Under Enterprise Manager, SQLGROUP, Server, Databases, I
show three databases that are "suspect" that I need to
delete. The databases were manually removed
(Permanentley) from the SQL directory in explorer. When I
try to delete the database from enterprise manager, I get
the error message, "Error 3724: Cannot drop the
database "db" because it is being used for replication. I
can find no place to cancel or remove the replication.
Please help.
Thanks,
Scott
can you issue a sp_resetstatus 'ProblemDatabaseName' to mark these databases
as 'normal' and then try to delete them?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Scott" <scott@.jabrousa.com> wrote in message
news:240001c47014$a9f83790$a301280a@.phx.gbl...
> I am new to SQL server...In case you couldn't tell.
> Under Enterprise Manager, SQLGROUP, Server, Databases, I
> show three databases that are "suspect" that I need to
> delete. The databases were manually removed
> (Permanentley) from the SQL directory in explorer. When I
> try to delete the database from enterprise manager, I get
> the error message, "Error 3724: Cannot drop the
> database "db" because it is being used for replication. I
> can find no place to cancel or remove the replication.
> Please help.
> Thanks,
> Scott
>

Deleteing a RO Warm Standby

I restored a a dB and for what ever resaon, it is a warm
standby that is replicated. I am attempting to delete
the dB via EP, but...
I cannot delete it because it is being used for
replication.
I cannot remove replication because it is read-only.
I cannot remove the read-only flag because it is a warm
stand by.
HELP!!!
How do I go about getting rid of this dB?
Larry...
Larry,
before altering the database in any way you'll need to recover it. Please
try this script:
restore database xxx with recovery
go
exec sp_removedbreplication xxx
go
use master
go
drop database xxx
go
HTH,
Paul Ibison
|||Thanks, this did the trick!!!