Detach database snapshot

  • Can we detach a database snapshot?

    The interface gives me the option to detach but when I click on detach, it gives an error saying "Cannot detach database snapshot".

  • sunny.tjk (4/5/2012)


    Can we detach a database snapshot?

    The interface gives me the option to detach but when I click on detach, it gives an error saying "Cannot detach database snapshot".

    A database snapshot is a sort of "differential view" of the database it was created from.

    It can't live without the originating database, hence it can't be detached.

    More on database snapshots: http://msdn.microsoft.com/en-us/library/ms175158.aspx

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • No, you cannot detach database snapshots.

    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
  • Gianluca Sartori (4/5/2012)


    sunny.tjk (4/5/2012)


    Can we detach a database snapshot?

    The interface gives me the option to detach but when I click on detach, it gives an error saying "Cannot detach database snapshot".

    A database snapshot is a sort of "differential view" of the database it was created from.

    It can't live without the originating database, hence it can't be detached.

    More on database snapshots: http://msdn.microsoft.com/en-us/library/ms175158.aspx

    Hope this helps

    Gianluca

    Thanks Gianluca.

    I'm also trying to figure out if I could schedule a job to generate snapshot databases at regular intervals of a user database .

  • No backup or restore of snapshots either. A limited feature, IMHO, useful for quick looks at data, or restores of the main db after some upgrade.

    Database snapshots: http://msdn.microsoft.com/en-us/library/ms175158.aspx, good list of limitations in there.

  • You could, just watch the IO impact.

    Lots of database snapshots can have a serious impact on data modifications in the source DB. By lots, I don't mean hundreds, 5 or 10 could have a noticable effect depending how good your IO subsystem is.

    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
  • GilaMonster (4/5/2012)


    You could, just watch the IO impact.

    Lots of database snapshots can have a serious impact on data modifications in the source DB. By lots, I don't mean hundreds, 5 or 10 could have a noticable effect depending how good your IO subsystem is.

    Gail, do you mean that snapshot databases aren't good for OLTP environment where INSERTS/UPDATES/DELETES are quite often? If yes, can snapshots be used for reporting environments?

  • sunny.tjk (4/5/2012)


    GilaMonster (4/5/2012)


    You could, just watch the IO impact.

    Lots of database snapshots can have a serious impact on data modifications in the source DB. By lots, I don't mean hundreds, 5 or 10 could have a noticable effect depending how good your IO subsystem is.

    Gail, do you mean that snapshot databases aren't good for OLTP environment where INSERTS/UPDATES/DELETES are quite often? If yes, can snapshots be used for reporting environments?

    I know of several companies that use snapshot databases on mirrored databases for reporting purposes. They limit the number snapshot databases to one or two depending on reporting requirements.

  • sunny.tjk (4/5/2012)


    GilaMonster (4/5/2012)


    You could, just watch the IO impact.

    Lots of database snapshots can have a serious impact on data modifications in the source DB. By lots, I don't mean hundreds, 5 or 10 could have a noticable effect depending how good your IO subsystem is.

    Gail, do you mean that snapshot databases aren't good for OLTP environment where INSERTS/UPDATES/DELETES are quite often?

    Yes. They can slow down data modifications in the source DB. One or two should be OK, depends on the IO subsystem how many it can handle.

    http://www.sqlservercentral.com/articles/Performance+Tuning/64080/

    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
  • Thank you.

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

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