restore database

  • I am trying to restore a database thru Management studio

    restored database full backup with no recovery

    Now trying to restore diff backup with recovery, throwing below error

    The backup set holds a backup of a database other than the existing 'test' database.

    Can anybody provide the tsql command to restore

    Restore full backup with no recovery

    restore diff backup with no recovery

    restore diff 2 backup with recovery

  • Are you sure the diff you're trying to restore belongs to that database? The error indicates that it doesn't

    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
  • laddu4700 (1/6/2010)


    Restore full backup with no recovery

    restore diff backup with no recovery

    restore diff 2 backup with recovery

    You only need to restore the most recent diff, then all the transaction logs more recent than the diff.

  • homebrew01 (1/6/2010)


    laddu4700 (1/6/2010)


    Restore full backup with no recovery

    restore diff backup with no recovery

    restore diff 2 backup with recovery

    You only need to restore the most recent diff, then all the transaction logs more recent than the diff.

    Yes - as homebrew said. Eliminate the middle diff backup and then add your tran logs more recent than the last diff.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks to all, i was selecting wrong back up file

    So Now i want to verity the updated data

    I have one full backup file 12/31/2009 12:20AM

    Diff backup file 12/31/2009 06:35 AM

    TLOG BACKUP 180 files between 6:35 am to 12:35pm

    I want to restore each tlog with recovery and check the data..every time do I need to delete database and restore with tlog with recovery? or any other way to do it

  • Restore it with Standby. Otherwise you'd have to drop the database and start from scratch after each log restore.

    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
  • Hi Gail,

    How can we make a database in standby mode if we are specifying the WITH NORECOVERY option?

    Can you please let me know how to restore the database in standby mode?What about the subsequent diff backup and log backup restoration?

    TIA

  • Ratheesh.K.Nair (1/6/2010)


    How can we make a database in standby mode if we are specifying the WITH NORECOVERY option?

    You can't. If you want the database in Standby, you use WITH STANDBY instead of WITH NORECOVERY

    Can you please let me know how to restore the database in standby mode?What about the subsequent diff backup and log backup restoration?

    May I suggest a read of Books Online, specifically the sections on restoring a log.

    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

Viewing 8 posts - 1 through 7 (of 7 total)

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