2

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.

Improve this question

1 Answer 1

Reset to default
1

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'
Improve this answer
7
  • After processing the mdf and ldf, some errors pop up. I'm translating them from spanish, so they may not be exact: 'Can't remove procedure 'proc_name' because it's being used for replication' 'The database has been restored, but an error has ocurred while restoring or deleting the replication.' Be advised that this happens while running the first script. If I nonetheless run the second script ignoring the errors, it executes correctly, but if I try to delete some function X, I get: "Can't remove procedure 'dbo.sp.MSdel_dbosome table name' because it is being used for replication"
    – Lucas Sch
    Commented Nov 20, 2015 at 17:54
  • Sorry for the mix up. The error showing when I try to delete some function X, is 'Can't remove function X because it is being used for replication'. The error I noted before is the one I got when restoring the database. Could that mean we're doing something wrong CREATING the backup?
    – Lucas Sch
    Commented Nov 20, 2015 at 18:05
  • Can you post how you are doing backup ? Exact command and screenshot ? that will help.
    – Kin Shah
    Commented Nov 20, 2015 at 18:12
  • We simply use the Management Studio. Right-click on DB, Tasks, Backup Database. We use the predetermined options.
    – Lucas Sch
    Commented Nov 20, 2015 at 18:15
  • 1
    This might be applicable to your problem. social.msdn.microsoft.com/Forums/sqlserver/en-US/…
    – Henrico Bekker
    Commented May 22, 2017 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.