Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Restore help

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-15 : 10:05:19
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!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-15 : 10:52:02
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!


Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-05-15 : 14:18:49
ALTER DATABASE {database} SET PARTNER OFF;
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-15 : 14:18:59
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

808 Posts

Posted - 2013-05-15 : 17:37:11
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

212 Posts

Posted - 2013-05-16 : 02:55:36
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

819 Posts

Posted - 2013-05-16 : 13:19:45
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
   

- Advertisement -