MSSQL_ENG003165: Database was restored, however an error was encountered while replication was being restored/removed. The database has been left offline.

Error: “Database  was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. 

Scenario:

I have  backup taken from database which is publisher and restored on other server not part of replication at all. After restore database went offline with below error.

Issue:

• If the backup is being restored to the same database and server on which it was taken, the error indicates that replication settings could not be restored properly.

• If the backup is being restored to a different database or server, the error indicates that replication settings could not be removed properly (by default, replication settings are removed if the database or server is different).
Solution:

1. Execute ALTER DATABASE to bring the database online; for example: ALTER DATABASE AdventureWorks SET ONLINE. If you want to preserve replication settings, go to step 2. If not, go to step 3.

2. Execute sp_restoredbreplication If this stored procedure executes successfully, the restore is complete. If it does not execute successfully, go to step 3.

3. Execute sp_removedbreplication to remove all replication settings.
Reconfigure replication if necessary. If you have scripted the replication topology as recommended, use scripts to reconfigure the topology.


Solution is available in topic MSSQL_ENG003165 in SQL Server Books Online also.