SQL Server - user needs to edit one table at transactional replication subcriber DB, to not interrupt replication backup of subcriber DB and restore is done daily. Size being a problem trying to find different solutions.

Question

SQL Server - the current setup is transactional replication that runs continuously. At the subscriber one of the departments need to edit values in one table. So they take a backup of subscriber DB and restore daily at night. Size being a problem trying to find different solutions. They need the entire DB + Edited table for reporting.

Answer 1

Hi

Use Begin Tran & commit tran

BEGIN TRAN

UPDATE [dbo].[Bank]
   SET [BankName] = 'BBC'
 WHERE userId IN (FROM Users where UserId LIKE '%Tom%')
 COMMIT TRAN
GO

--please don't forget to Accept as answer if the reply is helpful--

Answer 2

Hi @SushmaPeddi-1361,

Transactional replication supports updates at Subscribers through updatable subscriptions and peer-to-peer replication. Which type did you use? When data is updated at a Subscriber, it is first propagated to the Publisher and then propagated to other Subscribers. If you back up the subscribe DB after edit the table, the backup will contained the change. This mean it contain the entire DB + Edited table. Please refer to MS document Updatable Subscriptions - For Transactional Replication to get more information.

If I misunderstood your issue, please let me know.

---

If the response is helpful, please click "Accept Answer", thank you.

Answer 3

Thank you @CathyJi-MSFT , the data needs to match subscriber and publication for other departments. The restore happens with the different name so the business user who needs to update the table is isolated.