SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Restore help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

786 Posts

Posted - 05/15/2013 :  10:05:19  Show Profile  Reply with Quote
Hi,

I tried to run a restore database from disk and got this:

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The database is a Mirror in a sql 2008 R2 mirroring setup.
In Object Explorer, it showed as dbname(Mirror, Disconnected/In Recovery).

The mirroring operation has been suspended for couple days. Now I need to restore full/diff/tlog backups from primary to this server so they are insynch for mirroring again.

How do I make it available for restore? I think I need to find a way to get out this recovery mode.

Thanks!

Edited by - Hommer on 05/15/2013 10:16:31

Hommer
Aged Yak Warrior

786 Posts

Posted - 05/15/2013 :  10:52:02  Show Profile  Reply with Quote
I tried to alter it by set offline, or set single_user, but failed because it is in restoring state. (and now, showing (Restoring...) instead of (Mirror, In Recovery).



quote:
Originally posted by Hommer

Hi,

I tried to run a restore database from disk and got this:

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The database is a Mirror in a sql 2008 R2 mirroring setup.
In Object Explorer, it showed as dbname(Mirror, Disconnected/In Recovery).

The mirroring operation has been suspended for couple days. Now I need to restore full/diff/tlog backups from primary to this server so they are insynch for mirroring again.

How do I make it available for restore? I think I need to find a way to get out this recovery mode.

Thanks!



Edited by - Hommer on 05/15/2013 10:56:19
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
797 Posts

Posted - 05/15/2013 :  14:18:49  Show Profile  Reply with Quote
ALTER DATABASE {database} SET PARTNER OFF;
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 05/15/2013 :  14:18:59  Show Profile  Reply with Quote
I have to loop through all SIDs and killed them. Then the restore is working again.
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 05/15/2013 :  17:37:11  Show Profile  Reply with Quote
I got this for that:

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

However, the ssms has (Restoring...) with green arrow on the db.

And when I went back to the primary, and start mirroring (after Configure Security...), I got same message.


quote:
Originally posted by jeffw8713

ALTER DATABASE {database} SET PARTNER OFF;


Go to Top of Page

prett
Posting Yak Master

USA
200 Posts

Posted - 05/16/2013 :  02:55:36  Show Profile  Visit prett's Homepage  Reply with Quote
This post may help to fix this issue: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40841
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
797 Posts

Posted - 05/16/2013 :  13:19:45  Show Profile  Reply with Quote
Once mirroring has been disabled (SET PARTNER OFF) - you have to reconfigure mirroring from the ground up. When you break the mirror, the databases on the mirror will not be recovered automatically. That will leave them in a state of Recovery...

You can recover the databases using: RESTORE DATABASE {database} WITH RECOVERY

After you have restored the database on the mirror - and restored all available log files, you can then rebuild the mirroring.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000