Change Collation of a Mirrored Databse


If you have already tried this directly you have probably seen messages like the action is not available on databases involved in mirroring or database unable to be locked and so on.

So first you have to the database from the mirror session, then do the necessary change actions and restore the mirror session. Ex,

ALTER DATABASE SET PARTNER OFF
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS

On the mirrored database:
RESTORE DATABASE WITH RECOVERY


Error Messages:

  • The database could not be exclusively locked to perform the operation. 
  • The operation cannot be performed on database it is involved in a database mirroring session or an availability group