locked
Database mirroring cannot be enabled RRS feed

  • Question

  •  

    I am trying to mirror a database and I keep getting the error Msg 1469 saying that "Database mirroring cannot be enabled because the database is an auto close database on one of the partners.    Well.. the database properties is saying "false" under auto-close.   What else could be wrong?
    Thursday, March 13, 2008 3:18 PM

All replies

  • I am not sure about the error message but you may wish to review the below points.

    1. What is the Sql Server build and edition ?
    2. If it is Sql Server 2005 RTM version you need to start Sql server with trace flag 1400
    3. You need to have the partner databases in Full recovery model
    4. You need to restore the full backup using Norecovery option and 1 t-log backup using Norecovery option

    - Deepak

    Thursday, March 13, 2008 11:17 PM
  • Hi,

        Try the below command
        
    Code Snippet

        SELECT DATABASEPROPERTYEX('DBNAME','IsAutoClose')




        If it returns 0 then Autoclose option is disabled, else its in enabled state. If its enabled use the  below query to

    disable the same.

    Code Snippet

        ALTER DATABASE DBNAME SET AUTO_CLOSE OFF




        Once the above completed, take a full backup of the db from principal and restore with norecovery and then take a log

    backup then restore with norecovery and try configuring mirroring. Try this link also

    Note: Change dbname before you use the script
    Monday, March 24, 2008 2:36 AM
  • Are you able to mirror any other user database. Do you get same message for all database(s). Which version of SQL Server build you are on ? Could you try upgrading to SP2.

     

    Vishal

    Monday, March 24, 2008 7:25 AM
  • I had the same problem.

    Turns out the principal DB had autoclose true, so the restore also has autoclose true.  But you can't get at properties in a DB that has been restored with norecovery to turn this off in the mirror.  So the solution is to turn autoclose off in the principal, then start over with backup and restore.  The mirror DB will then have autoclose false and the error goes away.

    Thursday, August 21, 2008 9:40 PM
  • Still having problems.

     

    The original database WAS autoclose-true, so I set it to false.

     

    I deleted the database on the mirror server, re-saved .bak and .trn files from the primary and reloaded them onto the mirror server (this time using recovery, so that I could check the status)

     

    The loaded database KEEPS coming back as autoclose=true even though the original (where I took the .bak) is now autoclose=false (double checked it after this process).

     

     

    Friday, November 7, 2008 4:32 PM
  •  

    Ahh this was a 'duh' moment. When I backed up, I forgot to include the 'FORMAT' specification, so I was appending the good copies but loading the bad one.

     

     

    Friday, November 7, 2008 7:48 PM
  • one server has 9.0sp3 and other has 9.0sp2 still I am getting error.

    when I have applied the trace flag on (9.0 sp2 server) after that I am getting error:

    TITLE: Database Properties
    ------------------------------

    An error occurred while starting mirroring.

    ------------------------------
    ADDITIONAL INFORMATION:

    Alter failed for Database 'abcv_Mirror'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Database 'abcv_Mirror' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3077&EvtSrc=MSSQLServer&EvtID=927&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    Thanks
    Wednesday, July 8, 2009 6:52 AM
  • When I try to start mirroring getting error  ...

    "Database 'MY DATABASE NAME' cannot be opened.  It is in the middle of a restore.  (Microsoft SQL Server, Error: 927)"


    Anybody looked at this yet?  I am hitting the same brick wall as guptaajay1985.

    1.    Running SQL Server 2008 Enterprise edition (tried it with and without SP1)

    2.    The primary and mirror SQL Server versions are both 10.1.2531.0.

    3.    The witness is version 10.0.1600.22 (pre- SP1)

    4.    My two Win 2008 servers are VMs running on the same host PC.  Does that make a difference?

    5.    They are in the same domain. 
    (A virtual domain since all three are in VMs on the same host.)

    6.    All 3 server services are using the same domain account.

    7.    The primary is running in Full Recovery mode.

    8.    I did a full backup (with overwrite) and a trans log backup (with append) of the primary DB.

    9.    Copied the file to the mirror server,

    10.    Restored the DB and trans log on the mirror using NORECOVERY,

    11.    Using the wizard in the SSMS I configured the two endpoints which resulted in the full TCP addresses for all three servers being entered on the screen (primary, mirror and witness)

    12. When I click the start mirroring button on the pop up I get error 927

    "Database 'DATABASE NAME' cannot be opened.  It is in the middle of a restore.  (Microsoft SQL Server, Error: 927)

    The mirror DB is supposed to be in NORECOVERY "in the middle of a backup" so it can receive  the updates.  I am in the process of building an actual server and will try it with that.

    This is my test environment.  Mirroring to a second instance on the same PC has little business value so perhaps I am just hitting a wall that would never exist in my production environment, but I would like to know just the same. 

    Wednesday, December 23, 2009 1:12 AM
  • Wednesday, December 23, 2009 1:25 AM
    Answerer
    1. Yes, it was on. 
    2. So I turned it off,
    3. verified that it was off 
    4. repeated steps 8 through 12 above.


      use MYDATABASE;
      go

      ALTER DATABASE MYDATABASE
      SET ENCRYPTION off
      GO

    select DB_NAME(database_id), encryption_state
    from sys.dm_database_encryption_keys

    Returns:

    (No column name)   encryption_state
    tempdb                    3
    MYDATABASE       1

    Thursday, December 24, 2009 12:01 AM
  • Instead of trying to disable the encryption keys , try moving the encryption keys to the second instance and then try to configure mirroring.


    Thanks, Leks
    Thursday, December 24, 2009 12:34 AM
    Answerer
  • Instead of trying to disable the encryption keys , try moving the encryption keys to the second instance and then try to configure mirroring.


    Thanks, Leks
    That doesn't work, because the instance still has the encryption keys
    Friday, February 4, 2011 8:15 AM
  • https://subhrosaha.wordpress.com/tag/error-927/

    Please follow above url


    Thanks, /\J/\Y GUPT/\ SQL DBA ..India..

    Thursday, February 8, 2018 2:02 AM