SQL Server Performance Forum – Threads Archive

sp_restoredbreplication

What would fire sp_restoredbreplication if you do not have replication implemented?
I have an issue with my â€œrestore with moveâ€ operation. This task is executed via an application. Currently only users in the SA role can execute this task because it is calling sp_restoredbreplication. I don#%92t understand why it is calling this proc. Replication is not implemented, although it is installed. Any thoughts?

BOL refers:
Removes replication settings if restoring a database to the non-originating server, database, or system that is otherwise not capable of running replication processes. When restoring a replicated database to a server or database other than the one where the backup was taken, replication settings cannot be preserved. On the restore, the server calls sp_restoredbreplication directly to automatically remove replication meta data from the restored database. So you must check whether this database was used in replication previously, if so then remove the replication and perform the another restore. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you so much for replying! I’ve read the BOL and that is where I am stuck. Replication is not set up on this server or databases. It could have been before my time but how would I tell? I checked sp_dboptions and there is nothing in there and I checked the properties for the server under the replication tab. The disable button is disabled and the configure button is enabled which tells me replication is not running. So let’s say that before my time replication was set up and then removed is there another table that may have this database flagged as replicated and that is why the proc fires? I need to fix this because my users who do this process currently are members of sa and I would like to limit the role to at least dbcreator however I can’t because of that proc. Thanks again!

This amuses me and for RESTORE statement also ..If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database. If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. I presume you found this activity using PROFILER. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes, I did use profiler to track this down. I also researched RESTORE permissions and even though it is high level it is still not required to be a member of sys admin in order to perform. I have implemented a procedure previously at a different company doing a similiar task and I only needed to assign dbcreator to the users. I have to prove that the proc sp_restoredbreplication is not a normal call with RESTORE and it must either A. Is fired via the application making the call or B. Something is telling SQL Server this is a replicated database and SQL Server is automatically making the call. Thanks!

If it is the replicated then only way is to remove replication to avoid this issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

And if it is not replication, what would be calling this proc to fire? That is where I am really stuck. I used the sp_removedbreplication proc, ran my process and sp_restoredbreplication is still fired during the restore. Thanks!

]]>