I'm running MSSQL Server 11.0.2100 (SQL Server 2012) and every time I restart the MSSQLSERVER service on the principal or on the mirror, the database mirror breaks and the database is set into In Recovery mode on the SQL instance that was restarted.
I have no problem setting up a mirrored database and I can fail-over with no problem between the principal or the mirror but any time I restart the MSSQLSERVER service everything breaks.
After the database is set to In Recovery mode it is impossible to get it out of it. The only option is to delete (drop) the database.
Running Alter Database TestDB SET PARTNER OFF generates the following error:
Msg 945, Level 14, State 2, Line 1
Database 'TestDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Running Restore Database TestDB WITH RECOVERY generates the following error:
Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'TestDB' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.*
Is database mirroring broken on 11.0.2100 or do I have some kind of permission problem?
Best Answer
After installing 10.50.4000 (SQL Server 2008 R2 + Service Pack 2) on the principal and on the mirror and configuring it exactly as on 11.0.2100, I can now confirm that there are some serious issues with database mirroring in 11.0.2100. Database mirroring just doesn't work in 11.0.2100.
Running 10.50.4000 everything work perfectly okay and I'm able to restart the MSSQLSERVER service without problem.
Lesson learned: Do never use a SQL Server version where there are no service pack released.