Clone a replicated db by using detach amd attach

kK-2020 46 Reputation points
2022-03-27T14:52:12.447+00:00

Aim is to clone a db from prod, the db has replication setup on it, the way we do it is by copy data and log files into the new server and attach the db to the files. The db had replication setup on it , do i still need to recreate the replication after the attach

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP
    2022-03-27T15:55:46.047+00:00

    The normal way to copy a database is to use BACKUP/RESTORE. Then you don't have to take the source database offline. It's also simpler since it is only one file you have to copy. And if you write the backup to a network share, you may not have to copy anything at all.

    I don't have any experience of copying databases where replication is set up, but I think you would need to do something. For one thing, you want both the source database and the database copy replicate data to the subscriptions. That would be chaos.

    Exactly what you need to do depends on what you want to happen in the test environment. If you don't want any replication to happen, you would have to remove it. If you want replicate to some other test database, you would have to setup the replication from scratch, I guess.

    0 comments No comments

  2. Seeya Xi-MSFT 16,456 Reputation points
    2022-03-28T02:52:20.503+00:00

    Hi @kK-2020 ,

    Welcome to Microsoft Q&A!
    Please see this article about Difference Between Attach vs Restore Database in SQL Server.
    In your situation, you probably want the backup / restore option as that will leave the current database online.
    See this part of the offcial document: Detaching a Database.
    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.
    For more information, please read Back Up and Restore Replicated Databases.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments