Can you backup a replicated database?



  • I would like to backup a publication SQL Server database that is being replicated with transactional replication.

    Would it cause any issues (with replication or any other issue) if I took a backup of that database?



  • Yes, you can and you should be backing up your replicated databases (inclusive of the system generated database called distribution) just the same as any other databases, to the tolerance of data loss you can afford.

    Please follow the guidelines in https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/back-up-and-restore-replicated-databases?view=sql-server-ver15 . But the procedure for backing them up should be pretty much the same as any of your other databases (Full, Differential, and Transaction Log Backups all being available for use - depending on your database's Recovery Model).

    There's more specific recommendations to pay attention to in the aforementioned documentation when it comes time to restore those backups, such as:

    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 re-create all publications and subscriptions after backups are restored.

    And the specific steps and guidance in https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/strategies-for-backing-up-and-restoring-snapshot-and-transactional-replication?view=sql-server-ver15#publication-database-read-only-transactional-replication which you should carefully read through prior to your first restore.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2