August 20, 2015 at 7:19 am
Hi
I am learning about mirroring and trying to develop a set of scripts I can use to remove mirroring. I have followed the steps from the link below. The part I have trouble with is when
I go to the mirror server and enter RESTORE DATABASE xyz WITH RECOVERY. I get the following error "The database "xyz" does not exist. RESTORE can only create a database when restoring either a full backup or file backup of the primary file."
This documentation is from sql server 2008 r2 but I looked in 2012 and it gave this same command. So it seems that the database is not known to the system databases--is this correct? How would I restore with recovery?
https://technet.microsoft.com/en-US/library/ms189112(v=SQL.105).aspx
Thanks
Kathy
August 20, 2015 at 7:24 am
Does the database xyz exist?
What does this return?
select name from sys.databases
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2015 at 7:31 am
Hi Gail
No the databases that are on the mirror server are not listed in the results of the query select name from sys.databases. I wonder if instead of first removing the mirroring from the principal, I should failover first and then the system dbs would acknowledge the existence of 'xyz'. Then maybe the restore with recovery would work?
Thanks
Kathy
August 20, 2015 at 7:37 am
Huh?
No, once you fail over the mirror is active and the DB is online.
You have mirroring implemented at the moment, the principle on one instance, the mirror on another?
Mirroring is working, there are no errors?
Can you show a screenshot of the databases folder on the mirror please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2015 at 8:01 am
The attached pdf is the database layout on the mirror server.
Thanks
Kathy
August 20, 2015 at 8:03 am
Which is missing any useful information. 🙁
Check that the query window where you ran the SELECT from sys.databases is pointing to the correct server. There's no reason I can think of (other than perhaps permissions) why databases should not show up in sys.databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2015 at 8:05 am
I don't know about 2012, but in 2008 something would be wrong if the mirrored dbs aren't showing in sys.databases. I have 4 mirrored DBs in 2008 that do show up in that view as RESTORING.
Are you sure you have the proper permissions to query that view? If you do, then I think you need to 1) verify the datafiles physically exist on the server and 2) put in a ticket with Microsoft. Because something is probably wrong.
I've got some newly installed 2012 servers. I will play with them this morning and see if I can replicate your problem.
EDIT: When was the last time you refreshed your SSMS view? If the databases have been removed and you haven't refreshed Object Explorer or Object Explorer Details, that would explain a few things too.
August 20, 2015 at 8:19 am
I'm sorry Gail--new to this.
I do have a separate server for the principal, mirror, and witness.
I get no errors when I look at the Database Mirroring Monitor
When I setup this test environment I was trying to mimic out LYNC mirrored prod environment because I have been granted the privilege of installing windows updates and service packs to SQL Server 2012.
I was given the responsibilities for these servers when one of the log files exceeded its maximum limit of space and the production system stopped working. At that time I discovered that backups were not being done so logs weren't being managed.
Anyway -- long story.
I'm thinking that my permissions are not set up exactly right so I think I have to research more about that. I will look into this and then report back.
Thanks for the input
Kathy
August 21, 2015 at 5:58 am
Brandie Tarvin (8/20/2015)
I don't know about 2012, but in 2008 something would be wrong if the mirrored dbs aren't showing in sys.databases. I have 4 mirrored DBs in 2008 that do show up in that view as RESTORING.Are you sure you have the proper permissions to query that view? If you do, then I think you need to 1) verify the datafiles physically exist on the server and 2) put in a ticket with Microsoft. Because something is probably wrong.
I've got some newly installed 2012 servers. I will play with them this morning and see if I can replicate your problem.
I got sidetracked yesterday by work stuff, but was able to do the db mirroring experiment this morning. I can see the secondary in master.sys.databases. Let us know if you discover what your issue is or are continuing to have questions.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply