Post reply

RESTORE DATABASE is terminating abnormally

  • May 29, 2009 at 3:34 am

    #134601

    Dear All,

    I am getting error if I restore the database from snapshot...

    Msg 3137, Level 16, State 4, Line 1

    Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Any solution....

    Thanks

  • May 29, 2009 at 3:49 am

    #1001951

    I did the repro :

    CREATE DATABASE TEST;

    CREATE TABLE test1

    (t INT)

    CREATE DATABASE test1 ON

    ( NAME = test1, FILENAME = 'E:\TEST_1.SS' )

    AS SNAPSHOT OF TEST;

    GO

    CREATE TABLE test2

    (t INT)

    CREATE DATABASE TEST2 ON

    ( NAME = test2, FILENAME = 'E:\TEST_2.SS' )

    AS SNAPSHOT OF TEST2;

    GO

    RESTORE DATABASE snapshottEST FROM DATABASE_SNAPSHOT = 'TEST_1';

    Msg 3137, Level 16, State 4, Line 1

    Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    So you have perhaps more than 1 snapshots of that database and if thats true , you wont be able to restore until you drop other snapshots

    Restrictions on Reverting :

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

    Reverting is unsupported under the following conditions:

    The source database contains any read-only or compressed filegroups.

    Any files are offline that were online when the snapshot was created.

    More than one snapshot of the database currently exists. Only the snapshot that you are reverting can exist..

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • May 29, 2009 at 3:50 am

    #1001952

    The error itsself gives you the hint 🙂

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • May 29, 2009 at 4:15 am

    #1001958

    Thanx

    Thanks

  • June 1, 2009 at 4:18 am

    #1002633

    Dear All,

    Can I restore the database snapshot on another server..if yes then ?...

    Thanks

  • June 1, 2009 at 4:38 am

    #1002640

    No.

    The only 'restore' you can do with a snapshot is to restore the source database from the snapshot. A snapshot isn't a complete database, it's just the original version of pages that have changed since its creation.

    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

  • June 1, 2009 at 4:50 am

    #1002644

    If My database is fine yet I am restoring the snapshot for the database then what happen?what data will get by my original database.

    1. Snapshot contains sparse file for updated data,then will original database has not the updated data?

    Thanks

  • June 1, 2009 at 5:03 am

    #1002649

    From Books Online

    Reverting a Database to a Database Snapshot

    A revert database operation (specified using the DATABASE_SNAPSHOT option) takes a full source database back in time by reverting it to the time of a database snapshot, that is, overwriting the source database with data from the point in time maintained in the specified database snapshot. Only the snapshot to which you are reverting can currently exist. The revert operation then rebuilds the log (therefore, you cannot later roll forward a reverted database to the point of user error).

    Data loss is confined to updates to the database since the snapshot's creation. The metadata of a reverted database is the same as the metadata at the time of snapshot creation. However, reverting to a snapshot drops all the full-text catalogs.

    Reverting from a database snapshot is not intended for media recovery. Unlike a regular backup set, the database snapshot is an incomplete copy of the database files. If either the database or the database snapshot is corrupted, reverting from a snapshot is likely to be impossible. Furthermore, even when possible, reverting in the event of corruption is unlikely to correct the problem.

    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

  • June 1, 2009 at 5:11 am

    #1002651

    The snapshot will contain the initial data ie the data when you took the snapshot...Using snapshot you can restore that point

Viewing 9 posts - 1 through 8 (of 8 total)

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