Monday, October 16, 2006

Change Service Broker identifier After Database Restore

When u want to create a copy of a Database using Restore/sp_Detach &sp_Attach command if the database is Service Broker Enabled, then you should change the Database UniqueIdentifier after restoration. Ie. Each database has a identifier used for routing Service Broker messages to that database. In restore and sp_Attach/Detach method both database will have same identifier, so erratic routing may occur. In such scenario after restoration of the database, you must change the Identifier by the following command

ALTER DATABASE [DatabaseName] SET NEW_BROKER
 
Locations of visitors to this page