locked
recovery RRS feed

  • Question

  • hi all,

    how can i recovery the data when user delete table?

    Thursday, July 4, 2013 3:57 AM

Answers

  • Hi Bro

    Its a most common interview schenerio of now days interview.

    Schenerio: i have full backup on sunday 10: AM , differntial every day 10:00 AM except sunday and t-log every 15 mins.

    User deleted some table on wednesday 02:05 PM , now i have to recover till 02:04

    Steps :

    1. Use latest full backup (here last sunday backup)

    2. use latest differential backup (here wednesday diff backup)

    3. All t-log backups after wednesday differential backup til 2:00 PM backup

    4. run 2: 15 t-log backup with stop at 2:04 syntax.

    syntax :

    RESTORE LOG AdventureWorks2012
       FROM AdventureWorksBackups
       WITH FILE=4, RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

    Make sure in step 1,2,3 u should use no recovery and 4 use recovery.

    Refrence: http://msdn.microsoft.com/en-us/library/ms186858.aspx#restoring_to_pit_using_STOPAT


    Thanks Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    • Edited by Saurabh Sinha DBA Thursday, July 4, 2013 11:23 AM signature
    • Marked as answer by Fanny Liu Thursday, July 11, 2013 10:27 AM
    Thursday, July 4, 2013 11:22 AM
  • Do you have a valid backup? Restore the backup.


    Srinivasan

    • Marked as answer by Fanny Liu Thursday, July 11, 2013 10:27 AM
    Thursday, July 4, 2013 4:05 AM
  • I think to do that you will need a log file backup as well...

    Take a look a third party tool 

    http://www.lumigent.com/ ---exploer log for sql


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Fanny Liu Thursday, July 11, 2013 10:27 AM
    Thursday, July 4, 2013 5:29 AM
    Answerer

All replies

  • Do you have a valid backup? Restore the backup.


    Srinivasan

    • Marked as answer by Fanny Liu Thursday, July 11, 2013 10:27 AM
    Thursday, July 4, 2013 4:05 AM
  • I think to do that you will need a log file backup as well...

    Take a look a third party tool 

    http://www.lumigent.com/ ---exploer log for sql


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Fanny Liu Thursday, July 11, 2013 10:27 AM
    Thursday, July 4, 2013 5:29 AM
    Answerer
  • Hi Bro

    Its a most common interview schenerio of now days interview.

    Schenerio: i have full backup on sunday 10: AM , differntial every day 10:00 AM except sunday and t-log every 15 mins.

    User deleted some table on wednesday 02:05 PM , now i have to recover till 02:04

    Steps :

    1. Use latest full backup (here last sunday backup)

    2. use latest differential backup (here wednesday diff backup)

    3. All t-log backups after wednesday differential backup til 2:00 PM backup

    4. run 2: 15 t-log backup with stop at 2:04 syntax.

    syntax :

    RESTORE LOG AdventureWorks2012
       FROM AdventureWorksBackups
       WITH FILE=4, RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

    Make sure in step 1,2,3 u should use no recovery and 4 use recovery.

    Refrence: http://msdn.microsoft.com/en-us/library/ms186858.aspx#restoring_to_pit_using_STOPAT


    Thanks Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    • Edited by Saurabh Sinha DBA Thursday, July 4, 2013 11:23 AM signature
    • Marked as answer by Fanny Liu Thursday, July 11, 2013 10:27 AM
    Thursday, July 4, 2013 11:22 AM
  • The Backup usually what everyone looks incase if they have good backup stratgey in place becuase there is no such easy way to perform it but most of the case it's not(proper backup startegy plan) since the activity which you deleted the table the sql server performed for you the only way to recover with your backup , how ever if you good pratcise like

    using the T-SQL you can use Begin tran then do what you want and verify if the criteria meets for you if not make it rollback.

    >Incase if you have replica then you can do an import from other database incase if its sync in place properly.

    other wise plenty of 3rd party tools exists might be those are chargable so.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Thursday, July 4, 2013 6:09 PM
  • I have elaborated on that topic here: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp

    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, July 5, 2013 10:11 AM