Sql-server – Restoring a replicated SQL Server database in a non-replicated testing enviroment

replicationrestoresql server

We have a SQL Server 2008R2 database in production. Originally, the database didn't have replication. Every time there was some problem with our software, we'd just create a backup from the production server, restore it on our testing environment, and reproduce the problem to solve it. Simple as that.

A new gov-related requirement pushed us to set up a Transactional replication on the Production database. We modified our software accordingly, and it's now working well. Our software is able to function both with a replicated or non-replicated database.

The problem now, is that we can't simply create a backup and restore in our environment as simple as before, every time there's a problem we need to reproduce. When we restore a backup, we can't modify any of the schema and/or functions of it, because SQL Server says the database is tied to a non-existent replication (because it obviously copies the replication config from the Production environment).

Is there any way to create a backup of a replicated database and restore it cleanly in a non-replicated environment? Just the schema, data, and functions. 'Generate scripts' isn't possible because there's a LOT of data.

I see many questions related to how to restore the database keeping the replication active, but I just need the clean database for testing purposes.

Best Answer

You have to just restore the database using 

restore database <db_name>
from disk = 'Disk path\db_name.bak'
with recovery, replace -- only if there is already database that you need to overwrite
,stats =10,
move 'logical_data' to 'physical_path.mdf',
move 'logical_log' to 'physical_path.ldf'

once the database is restored, you can remove the replication bit 

sp_removedbreplication 'database_name'
Related Question