Remove Mirror Question

[BridgeRE]

When I remove mirroring from one of my SQL server, it set the database to (Restoring...) where the mirror was. How can I remove this? When I try to "Take Offline" it I get an error:

TITLE: Microsoft.SqlServer.Smo
------------------------------

Set offline failed for Database 'databasename'.

For help, click:

http://go.microsoft.com/fwlink?Prod...nText&EvtID=Set+offline+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

ALTER DATABASE is not permitted while a database is in the Restoring state.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5052)

For help, click:

http://go.microsoft.com/fwlink?Prod...54&EvtSrc=MSSQLServer&EvtID=5052&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

The links in the error provide no help.

Thanks

 

[ptheriault]

you have to force the database on-line now.

ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[BridgeRE]

Thanks for the reply. My knowledge is pretty limited with SQL. How and where should I be running this? Also is there a different way to remove mirrors or is this "the way"?

Thanks again.

 

[ptheriault]

Run that command through a SSMS query window from the master database.

For the secondary db to become active you must either fail over database or run this command to force the database to become active.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[BridgeRE]

Thanks, this is on a production database, when I run this command on the master, I am not going to take anything down am I? Data will flow to and from the master during and after this command?

 

[ptheriault]

yes, It will only affect the mirrored database. Why did you remove the mirror?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[BridgeRE]

We use a prioritary software that is not capable at this time to switch to the mirror automatically. So it is just a royal pain in the butt to reboot 7 sql servers then fail all the DB's back over to the original server. Thank you for all your help!

 

[BridgeRE]

Hmmm, I get this message:

Msg 1416, Level 16, State 1, Line 1
Database "CallStatOH211" is not configured for database mirroring.


Do I need to re-setup the mirror first?

 

[BridgeRE]

According to the book the ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS command, forces failover. I've already stop the mirroring process and am stuck with <database_name>(Restoring...) on the server where the mirror WAS. I do notice I have a DELETE choice, if I choose this, will it delete the main (Principle DB) from the original SQL Server?

 

[ptheriault]

Did you try
RESTORE DATABASE <db_name> WITH RECOVERY



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[BridgeRE]

No, but I'm not looking to restore anything. I just want to remove <database_name>(Restoring...) on the server where the mirror was.

 

[ptheriault]

>>but I'm not looking to restore anything

That is how you will recover the database. You are not restoring anything! You are telling the database to recover!

- Paul
- If at first you don't succeed, find out if the loser gets anything.