none
Snapshots as backup strategy

Answers

  • Hi,

    You cannot replace your backup strategy with snapshots. The reason for this, is that if you have a serious failure, such as loosing your instance, or a loosing a data file of the source database, you will loose your snapshot as well!

    You can use snapshots to supplement a backup strategy, so for example, if you take a log backup once every half hour, you could take a snapshot once every 10 mins, in between the log backups, so that if a table was dropped in error, etc, you could revert to the snapshot, rather than all the way back to last log backup, etc, without the pain of rolling the log forward. Even in this scenario, however, you need to quote a maximum of 30 mins potential data loss to the business.

    The only time that I would replace a backup with a snapshot, is if you are deploying a "safe" release to production, where you are 100% confident that nothing serious can go wrong, but you require a backup strategy.

    Hope this helps,

    Pete


    Peter Carter http://sqlserverdownanddirty.blogspot.com/

    Tuesday, March 27, 2012 2:56 AM

All replies

  • Hi,

    You cannot replace your backup strategy with snapshots. The reason for this, is that if you have a serious failure, such as loosing your instance, or a loosing a data file of the source database, you will loose your snapshot as well!

    You can use snapshots to supplement a backup strategy, so for example, if you take a log backup once every half hour, you could take a snapshot once every 10 mins, in between the log backups, so that if a table was dropped in error, etc, you could revert to the snapshot, rather than all the way back to last log backup, etc, without the pain of rolling the log forward. Even in this scenario, however, you need to quote a maximum of 30 mins potential data loss to the business.

    The only time that I would replace a backup with a snapshot, is if you are deploying a "safe" release to production, where you are 100% confident that nothing serious can go wrong, but you require a backup strategy.

    Hope this helps,

    Pete


    Peter Carter http://sqlserverdownanddirty.blogspot.com/

    Tuesday, March 27, 2012 2:56 AM
  • Hi,

    The way snapshot work is it does not make a copy of your database except when the page is modified it then copy this page.

    So if there is a disk failure snapshot can not be there to revert your database because you lose your mdf file.

    Please check the usage of snapshot in this link

    http://msdn.microsoft.com/en-us/library/ms175158.aspx

    I hope this is helpful.

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com

    Tuesday, March 27, 2012 5:39 AM
  • It depends on what typesd of snapshots you mean. The term "snapshot" is a term used in many contexts. You have been given replies for the functionality "Database snapshot", which was introduced in SQL Server 2005. If you mean something different by the word "snapshot" (like SAN snapshot, OS snapshot, VM snapshot etc), then please elaborate.

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, March 27, 2012 10:14 AM
    Moderator
  • Has anyone done this? If so what was it?

    Alan

    Backups kept on the same storage are not backups. Database backups without any ability to roll back up to any pointed transation are not database backups. So...

    -nismo

    Tuesday, March 27, 2012 8:12 PM
  • Snapshots are great to use as a supplement, but not a replacement for full backups.

    We use it in this example:

    We have a major release going to production with several sql scripts to be run against the DB.  We take a DB snapshot before the upgrade starts.  If something goes wrong with the upgrade, then it gives a very quick way to rollback the upgrade without restoring a 600GB database.  We always though have the full backup to use in case we had any physical problems with the environment.

    Thursday, April 25, 2013 6:17 PM