locked
Can't Restore a Mirror Database from Database Snapshot in SQL server 2005 RRS feed

  • Question

  • Hi,

    We have setup Mirroring between two of our databases on different servers in high performance mode, we are taking snapshots of mirror database. When we to broke the mirroring and  tried to recover the mirror database from one of the snapshots we keep getting the below error: Unfortunately the path that is shown in the error is actual path on principal server. I am not sure where I am doing wrong during the recovery process. I have tried this many times but without success. Any help would be really appreciated.

    Msg 5123, Level 16, State 1, Line 1

    CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'H:\SQLData\ESS11Test\klx_log01.ldf'.

    Msg 5024, Level 16, State 2, Line 1

    No entry found for the primary log file in sysfiles1. Could not rebuild the log.

    Msg 5028, Level 16, State 2, Line 1

    The system could not activate enough of the database to rebuild the log.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

     

    Wednesday, August 3, 2011 8:06 PM

Answers

  • Hi Folks,

    Thank you all for your responses and suggestions, we are not looking at this option as high availability. We already have full backups and tlog backups in place for the principal server. We are just trying out our different DR scenarios and this could be worst possible scenario in our DR plan. However Restoring the database snapshot worked after we changed the path of db files to be same on both principal and mirror servers. I appreciate your valuable inputs.

    Thanks,

    Syam

     


    SP
    • Marked as answer by Syam449 Tuesday, August 9, 2011 11:56 AM
    Tuesday, August 9, 2011 11:56 AM

All replies

  • Why do you need to recover a mirroring from the snapshot?  You need to take a backup of the principal (if needed) and re-establish the mirroring....,Snapshot is about reporting.....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by RamJaddu Friday, August 5, 2011 1:40 PM
    Thursday, August 4, 2011 5:06 AM
  • Hi Uri,

     

    We are trying to explore the possibility of restoring snapshot in case of any user error/corrupt data. We are already taking backups on the principal server also. However this seems to be working now when we had similar path forthe database files  on both principal and mirror servers.

     

    Thanks,

    Syam


    SP
    Thursday, August 4, 2011 3:15 PM
  • Hi Uri,

     

    We are trying to explore the possibility of restoring snapshot in case of any user error/corrupt data. We are already taking backups on the principal server also. However this seems to be working now when we had similar path forthe database files  on both principal and mirror servers.

     

    Thanks,

    Syam


    SP

    Hi Syam,

    It is not recommended to restore database from snapshot in case of any user error/corrupt data. As we know, snapshot has small size as it uses 90% less drive space than backing up the system does. But when the original database corrupts, you could not always restore snapshot successfully.
    For example, the database has 4 data pages (1, 2,3,4) and user take a snapshot at 10:00 am. Actually this snapshot did not store any data pages.  At 11:30 am, data page 2 was updated and the original data page 2 has been added into 10:00 am ‘snapshot. After that time, if the new updated page 2 corrupt, the database could restored from the snapshot. But if one the other three data pages corrupt, the database could not be restored from snapshot.

    Hope this helps.

     

     

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by RamJaddu Friday, August 5, 2011 1:40 PM
    Friday, August 5, 2011 8:45 AM
  • >>We are trying to explore the possibility of restoring snapshot in case of any >>>user error/corrupt data.

    Take a snapshot on principal. What do you mean by corrupt data?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, August 7, 2011 5:45 AM
  • It is a bad practice on data recovery that to restore from a SNAPSHOT when you find that there is a corruption or user error of database.

    BOL clearly states that : Taking regular backups and testing your restore plan are essential to protect a database. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.

    So better you look other avenues such as Database Mirroring and regular backup schedule to the databases.


    Satya SKJ, SQL Server MVP [Knowledge Sharing Network - www.sqlserver-qa.net]
    Author of SQL Server 2008 R2 Administration cookbook.
    Follow me @sqlmaster.
    Monday, August 8, 2011 8:47 AM
  • Hi,

    Snapshots can be used in conjunction with database mirroring for reporting purposes. You can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot.

    Database Snapshots should not be seen as high availability option.

    Database Snapshots should not be seen as an alternative to regular backups of your data. Database snapshots can not protect your databases against disk errors or database corruption.

    When mirroring got broken, you should again reconfigure your mirror database by restoring the full backup & Tlog backup from Prinicpal to mirror database.

    In enterprise edition , database snapshot is available so as  you can use that as READ only database, it is not meant to recover mirroring session from snapshot.

    Hope this helps!!

     


    Regards, Vishal Srivastava
    Tuesday, August 9, 2011 10:15 AM
  • Hi Folks,

    Thank you all for your responses and suggestions, we are not looking at this option as high availability. We already have full backups and tlog backups in place for the principal server. We are just trying out our different DR scenarios and this could be worst possible scenario in our DR plan. However Restoring the database snapshot worked after we changed the path of db files to be same on both principal and mirror servers. I appreciate your valuable inputs.

    Thanks,

    Syam

     


    SP
    • Marked as answer by Syam449 Tuesday, August 9, 2011 11:56 AM
    Tuesday, August 9, 2011 11:56 AM