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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1151 Visits: 1227
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1151 Visits: 1227
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1151 Visits: 1227
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
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3997 Visits: 5807
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
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3997 Visits: 5807
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
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3997 Visits: 5807
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
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3997 Visits: 5807
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