Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am testing the AlwaysOn feature of SQL Server 2012. I have 2 servers set up and can successfully create availability groups and fail them over between servers. However, any databases that DO NOT belong to an availability group are stuck in a status of "Restoring" on the secondary server. I have started from scratch several times, and the same thing happens to these databases.

Is this normal behavior?

I was assuming that databases that don't belong to an AG would simply not show up on the secondary server.

share|improve this question

closed as off-topic by Shawn Melton, Phil, Jack Douglas Dec 15 '15 at 15:25

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Shawn Melton, Phil, Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

How did you setup your Availability Group? It sounds like what happened is you blanketed the backups and restores from the primary replica server to the secondary replica server.

In other words, you did a:

backup database <DatabaseName>
to disk .... <so on and so forth>

to all databases on the primary and a

restore database <DatabaseName>
from disk ....
with norecovery

to all the backups aimed at the secondary replica server (proper operations for future AG databases). If you don't join one of those databases to the availability group then it'll stay in that restoring state.

The main question here is, if Database1 (for example) on your primary replica server as not meant to be in the availability group, then why would it be restored/existing on the secondary replica server to begin with?

share|improve this answer
I have not attempted any backup/restore via T-SQL. I have been using the Availability Group Wizard only, and only select a handful of databases out of 30+. When the wizard is complete, the databases I did not select show "Restoring", and the databases I did select show "Synchronized" as expected. – SomeGuy Jun 19 '13 at 15:00
So databases you didn't select during the wizard actually appear on the secondary replica server all of a sudden? – Thomas Stringer Jun 19 '13 at 15:25
If that's the case, then it sounds exactly like what I explained in my answer, whether or not you did that directly or that was your (or the wizard's) intention. My recommendation.... use T-SQL to create all this. You can easily script out your schema and modify accordingly. That will ensure you have complete control over everything that happens. – Thomas Stringer Jun 19 '13 at 15:33
up vote -1 down vote accepted

I have a half-answer at least. My first attempt at creating an Availability Group consisted of every database. I later decided to only try a few databases in a group. So I deleted the databases on the secondary after removing them from the AG. Subsequent attempts to create availability groups with a limited set of databases triggered the stuck "Restoring" state. I am not sure why. I stopped SQL Server on the secondary, deleted the mdf and ldf files, then restarted SQL Server. All databases still showed "Restoring", but I was able to delete them in SSMS. I now have just the databases that I intended to have in the availability group showing on the secondary as "Synchronized"

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.