db restoration issue

  • received below error after db restoration.

    Database 'xxxx' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

    RESTORE DATABASE is terminating abnormally.

    Brought the db online and set it to Multiuser, but receiving same errors while using database.

    how to solve this ?

  • charipg (4/11/2014)


    See the topic MSSQL_ENG003165 in SQL Server Books Online.

    What did Books online say on the referenced page?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • it didn't worked out.

  • What didn't work out? What did the referenced Books Online page say?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • in books online, below is the solution.

    User Action

    To resolve this issue:

    Execute ALTER DATABASE to bring the database online; for example: ALTER DATABASE AdventureWorks SET ONLINE. For more information, see ALTER DATABASE (Transact-SQL). If you want to preserve replication settings, go to step 2. If not, go to step 3.

    Execute sp_restoredbreplication (Transact-SQL). If this stored procedure executes successfully, the restore is complete. If it does not execute successfully, go to step 3.

    Execute sp_removedbreplication (Transact-SQL) to remove all replication settings.

    Reconfigure replication if necessary. If you have scripted the replication topology as recommended, use scripts to reconfigure the topology.

    actually we need to resolve the issue without reconfigure the replication .

    is there anyway ?

  • charipg (4/11/2014)


    actually we need to resolve the issue without reconfigure the replication .

    Did you try the steps that the documentation stated?

    Step 2: Execute sp_restoredbreplication (Transact-SQL). If this stored procedure executes successfully, the restore is complete. If it does not execute successfully, go to step 3.

    Step 3: Execute sp_removedbreplication (Transact-SQL) to remove all replication settings.

    Reconfigure replication if necessary. If you have scripted the replication topology as recommended, use scripts to reconfigure the topology.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • yes i tried .

    Executed the sp_restoredbreplication and received below error so Executed sp_removedbreplication successfully, but its not resolved my original issue.

    Msg 208, Level 16, State 1, Procedure sp_vupgrade_publisherdb, Line 524

    Invalid object name 'sysarticles'.

    Msg 266, Level 16, State 2, Procedure sp_vupgrade_publisherdb, Line 524

    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 2, current count = 3.

    Msg 266, Level 16, State 2, Procedure sp_MSrestoredbreplication, Line 0

    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 2, current count = 3.

    Msg 266, Level 16, State 2, Procedure sp_restoredbreplication, Line 0

    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 2, current count = 3.

  • any suggestions pls ...

  • Try the restore again?

  • yes , i restored the db with keep_replication also again.

    its showing below error after restoration.

    Processed 448 pages for database 'abc', file 'abc_Data' on file 1.

    Processed 2 pages for database'abc', file 'abc_Log' on file 1.

    Msg 208, Level 16, State 1, Procedure sp_vupgrade_publisherdb, Line 524

    Invalid object name 'sysarticles'.

    Msg 3165, Level 16, State 1, Line 1

    Database 'abc' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

  • What about if you restore it without keeping replication? Restore it under another name if you don't want to overwrite the other one.

  • yes, i tried to restore without keep replication also...

    received same error.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply