Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Changing Owner or Mirrored DBs Expand / Collapse
Author
Message
Posted Wednesday, July 13, 2011 1:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:47 AM
Points: 934, Visits: 1,153
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
Post #1140791
Posted Thursday, October 20, 2011 2:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:47 AM
Points: 934, Visits: 1,153
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
Post #1193399
Posted Thursday, October 20, 2011 2:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:47 AM
Points: 934, Visits: 1,153
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
Post #1193400
Posted Tuesday, December 06, 2011 8:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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!
Post #1217140
Posted Tuesday, April 16, 2013 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 7:55 AM
Points: 6, Visits: 201
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
Post #1442781
Posted Tuesday, April 16, 2013 2:13 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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.
Post #1443004
Posted Wednesday, April 17, 2013 2:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 7:55 AM
Points: 6, Visits: 201
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"?
Post #1443138
Posted Thursday, April 18, 2013 3:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Post #1444167
Posted Friday, April 19, 2013 3:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 7:55 AM
Points: 6, Visits: 201

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!
Post #1444287
Posted Tuesday, April 23, 2013 10:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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!


Cool, did not expect that to work! Good one to remember!
Cheers
Gaz
Post #1445580
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse