SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing Owner or Mirrored DBs


Changing Owner or Mirrored DBs

Author
Message
kapfundestanley
kapfundestanley
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 1317
Always know that there are some commands which will run on the principal database and not the mirror.So remove mirroring change database owner and reconfigure mirroring.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
kapfundestanley
kapfundestanley
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 1317
You cant change anything on the mirrored databases.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
kapfundestanley
kapfundestanley
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 1317
Remove mirroring and reset again,that is how i do it.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7700 Visits: 6045
You could fail over to partner, change owner at mirror, fail back to principal.

Assuming you'd be allowed to do that given the disruption during fail over!
houming1982
houming1982
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 244
If we need to do modifications on mirror database, we must make it writable (set partner off + restore with recovery). But if the mirroring should be reset, we must restore the last backup of the principal database, then the log, then the mirroring, which means all the modifications we did on mirror database is meaningless...

Assume that the dbowner of the principal database is "A", and your SQL account is "B". when you restore the backup of the principal database to the DR instance, the dbowner of the mirror database will be "B". How could we change "B" to "A" on the mirror database if we do not want to do the failover, please?

regards
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7700 Visits: 6045
houming1982 (4/16/2013)
If we need to do modifications on mirror database, we must make it writable (set partner off + restore with recovery). But if the mirroring should be reset, we must restore the last backup of the principal database, then the log, then the mirroring, which means all the modifications we did on mirror database is meaningless...

Assume that the dbowner of the principal database is "A", and your SQL account is "B". when you restore the backup of the principal database to the DR instance, the dbowner of the mirror database will be "B". How could we change "B" to "A" on the mirror database if we do not want to do the failover, please?

regards


Easiest way, connect to the mirror server as "A", rather than yourself, and run the restores.
houming1982
houming1982
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 244
Gazareth (4/16/2013)

Easiest way, connect to the mirror server as "A", rather than yourself, and run the restores.


What if we do not know the password of the login "A"?
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7700 Visits: 6045
houming1982 (4/17/2013)
Gazareth (4/16/2013)

Easiest way, connect to the mirror server as "A", rather than yourself, and run the restores.


What if we do not know the password of the login "A"?


Unfortunately, in that case you'll need to go through the failover process.
Wish you could specify the owner as part of the restore statement but no joy. Not sure if 'execute as' would work but I wouldn't count on it :-)
houming1982
houming1982
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 244

Unfortunately, in that case you'll need to go through the failover process.
Wish you could specify the owner as part of the restore statement but no joy. Not sure if 'execute as' would work but I wouldn't count on it :-)


Thank you very much for your reply, Gazareth. I tried with "execute as login = 'A'", then restore backup and log by commands (the prerequisite is that login "A" has the same permissions on both servers), and it works! :-D
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7700 Visits: 6045
houming1982 (4/19/2013)

Unfortunately, in that case you'll need to go through the failover process.
Wish you could specify the owner as part of the restore statement but no joy. Not sure if 'execute as' would work but I wouldn't count on it :-)


Thank you very much for your reply, Gazareth. I tried with "execute as login = 'A'", then restore backup and log by commands (the prerequisite is that login "A" has the same permissions on both servers), and it works! :-D


Cool, did not expect that to work! Good one to remember! :-)
Cheers
Gaz
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search