locked
Prerequirement get failed for Database Mirroring RRS feed

  • Question

  • Hello All,

    When i try to configure Database mirroring.

    First i take the full backup and take log backup successfully on principal database and then i restore it in mirror database using this sql command

    RESTORE DATABASE [smart] FROM  DISK = N'F:\aaa\smart.bak' WITH  FILE = 1,  MOVE N'smart' TO N'D:\Microsoft SQL Server\MSSQL.5\MSSQL\DATA\smart.mdf',  MOVE N'smart_log' TO N'D:\Microsoft SQL Server\MSSQL.5\MSSQL\DATA\smart_log_mir.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 10
    GO

    But it show error like this

    Msg 3154, Level 16, State 4, Line 1
    The backup set holds a backup of a database other than the existing 'smart' database.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    (or) Give some info how to perform the backup and restore for configuring the database mirroring

    Thanks

    Regards,
    RM Thirunavukkarasu
    Wednesday, December 23, 2009 5:58 AM

Answers

  • Ok this is what i did !
    first step :  i have created a database named {smart] on principal server and took  full backup ! 

    second step : i have created database named [smart] on mirror server 

    third step : on mirror server choose smart database -->task--> restore database  --> from device ---> select the backupfile-->select option --> overwrite the exisiting database -->and select the recovery state as no recovery ..

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

    Thanks,Suhas V 
    Wednesday, December 23, 2009 1:18 PM

All replies

  • it states that there is already a database [smart] existing on the server .  !!  you can drop the exisitng database or create a new database and restore with replace 

    -----
    Thanks,Suhas V 
    Wednesday, December 23, 2009 6:15 AM
  • Hello Suhas!

    I want to create mirroring, So that i create a database on mirror server as same name given in principal server.

    Thanks
    RM Thirunavukkarasu
    Wednesday, December 23, 2009 6:26 AM
  • I guess you are trying to setup mirroring between named instance which resides on single server ! if that is the case than the error is mandatory to popup :) ... I have reproved the issue in all the ways and the possible causes are ! 

    --- already a database might exsist on the server  on the mirror server


    ---------------
    Thanks,Suhas V 
    • Edited by Suhas Vallala Wednesday, December 23, 2009 1:27 PM wrong analysis
    Wednesday, December 23, 2009 7:41 AM
  • Hello Suhas

    You correct my 2 instance in same machine only. Wats up the solution for that.

    Thanks,
    RM Thirunavukkarasu
    Wednesday, December 23, 2009 8:26 AM
  • Hello Suhas!

    Thanks for the reply. Just for training purpose i should make two instance in the same machine.

    Thanks

    Regards,
    RM Thirunavukkarasu.
    Wednesday, December 23, 2009 1:07 PM
  • Ok this is what i did !
    first step :  i have created a database named {smart] on principal server and took  full backup ! 

    second step : i have created database named [smart] on mirror server 

    third step : on mirror server choose smart database -->task--> restore database  --> from device ---> select the backupfile-->select option --> overwrite the exisiting database -->and select the recovery state as no recovery ..

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

    Thanks,Suhas V 
    Wednesday, December 23, 2009 1:18 PM
  • Hi thiru,
    Normally this issue is due to not using replace option along with restore command.
    Have you seen this article from Pinal http://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/ .

    Just drop the existing database and restore from scratch with replace option and then the successive transaction logs.

    Thanks, Leks
    • Proposed as answer by Suhas Vallala Wednesday, December 23, 2009 6:38 PM
    Wednesday, December 23, 2009 6:15 PM
    Answerer
  • Yeah thats true for the very first instance i faced the same issue but later on i have done the same ! strange wired error ! 

    -------
    Thanks ,Suhas V 
    Wednesday, December 23, 2009 6:38 PM
  • Hello

    But this is possible if mirroring and principal are in different instance

    Thanks

    Regards,
    RM Thirunavukkarasu
    Thursday, December 24, 2009 3:59 AM
  • Yes it is possible ! For testing purpose that is fine but in production environment we dont choose to have this kind of setup . 

    Thiru , 

    There are many request opened by you kindly please mark as answer for the resolved posts  ! 

    --------------
    Thanks,Suhas V 
    Thursday, December 24, 2009 5:34 AM