Post reply

DB Snapshots - Schema changes backed up?

  • July 15, 2010 at 6:55 am

    #235319

    Hi,

    I have a situation where we could utilise a DB snapshot as part of our installation routine on an enterpise system that takes a good 1.5 hours to back up. This would save us a lot of time as the backup would be required 3 times during the deployment however, the deployment may make changes to the DB schema. As the snapshot is just for the specific DB that is snapshotted and you can not use snapshots for the master db does this mean that schema changes would not be retained in the snapshot?

    Thanks in advance.

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • July 15, 2010 at 7:53 am

    #1193493

    A snapshot is the database as it existed at the point in time the snapshot was created, including the schema.

  • July 15, 2010 at 12:27 pm

    #1193703

    Hi

    DB snapshot is just point-in-time shell of the DB. They just reflect the state of the DB at the time of snapshot.

    They don't save changes the schema changes.

    Best Regards,

    SQLBuddy

  • July 15, 2010 at 3:27 pm

    #1193802

    You need to understand what a snapshot does to know if it will do waht you want.

    Taking a snapshot actually creates an empty database, with a view over the real database. This is why it can be done so fast.

    From that point on any time you make a change to the original database, SQL does a "before look" and copies this to the snapshot. If you later decide to do a rollback from the snapshot SQL takes all the "before Looks" and re-applies them tothe database, a bit like rolling back a transcation log.

    I would recommend you test the upgrade in your development environment, doing snapshots and all and testing that you can roll back if you need to. I've used snapshots for upgrade backups and they work really well.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • July 16, 2010 at 3:42 am

    #1193968

    Many thanks for the responses guys.

    I have run some tests on adding / removing tables/columns etc and other schema changes and restored from the test db and all seems to work well.

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

Viewing 5 posts - 1 through 4 (of 4 total)

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