Post reply

Restore of the Database

  • January 20, 2009 at 9:16 pm

    #383769

    Comments posted to this topic are about the item Restore of the Database

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • January 21, 2009 at 12:52 am

    #930145

    Obvious!

  • January 21, 2009 at 1:12 am

    #930152

    Not that obvious. 😉

    "Keep Trying"

  • January 21, 2009 at 3:13 am

    #930189

    So why does the documentation for the restorehistory table show a setting of 'V' = Verifyonly for column restore_type?

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

  • January 21, 2009 at 4:28 am

    #930210

    I agree with Warren. I read the same BOL article and that's why I answered YES.

    By the way, the MSDN article quoted to justify NO being the correct answer doesn't even mention the msdb.dbo.restorehistory table.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • January 21, 2009 at 6:15 am

    #930251

    This answer is wrong. Just because SQL does not actually perform the backup, doesn't mean it won't store information about the RESTORE command. In that table, there's a restore_type that could be verifyonly:

    restore_type

    char(1)

    Type of restore operation:

    D = Database

    F = File

    G = Filegroup

    I = Differential

    L = Log

    V = Verifyonly

    R = Revert

    Can be NULL.

  • January 21, 2009 at 6:40 am

    #930280

    I must be pretty bored today. I just tried it on both SQL 2005 and 2008, and niether wrote a record to the restorehistory table.

    One interesting thing I did observe...

    The backup file I used to play with was a backup of a database with several filegroups that live on different drives in production. On the dev box I was using that only has one, it actually spit out the same error message a regular restore does when you don't use the WITH MOVE clause to tell where you want those files to go. I found that slightly cool.

    The Redneck DBA

  • January 21, 2009 at 6:41 am

    #930281

    I agree with Warren as well. I originally thought the answer was NO until I read the restorehistory docs which have a verifyonly entry, so I answered YES which came back as incorrect to my suprise.

    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/9140ecc1-d912-4d76-ae70-e2a857da6d44.htm

    Could someone explain when the V = Verifyonly entry would be used for if there is NOT an entry made in this table with a RESTORE VERIFYONLY?

    Thanks!

  • January 21, 2009 at 7:11 am

    #930309

    I got the question wrong, but after searching some more found out I WAS wrong. It would put the information into the restorehistory table if you used the LOADHISTORY option. which wasn't used in the question.

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

  • January 21, 2009 at 7:16 am

    #930314

    Steven Cameron (1/21/2009)

    I got the question wrong, but after searching some more found out I WAS wrong. It would put the information into the restorehistory table if you used the LOADHISTORY option. which wasn't used in the question.

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

    Interesting. I missed that option when I read it the first time.

    It's sometimes handy to see restores in that table just to see who did what and when. But I wonder what use there would be for keeping a record of restore verifyonly executions?

    The only think I can think of is a senior DBA checking up on a jr. dba making sure they run restore verifyonly before running the real restore? But even that seems like a stretch.

    The Redneck DBA

  • January 21, 2009 at 7:23 am

    #930324

    Why is verify history needed? I've never used this on the same server since backups are usually known quantities. It's on a DR server I might check since I don't have backup history.

  • January 21, 2009 at 7:48 am

    #930346

    The question probably should have said that RESTORE VERIFYONLY doesn't write to the msdb.dbo.restorehistory by default. But there is the LOADHISTORY parameter that will make it so.

  • January 21, 2009 at 1:14 pm

    #930664

    I agree with Warren. In MSDN its clearly written that msdb.restorehistory contains one row for each restore operation.

    For VERIFYONLY restore option column [restore_type] will contain 'V'

    V = Verifyonly

  • January 21, 2009 at 3:23 pm

    #930750

    igorg (1/21/2009)

    I agree with Warren. In MSDN its clearly written that msdb.restorehistory contains one row for each restore operation.

    For VERIFYONLY restore option column [restore_type] will contain 'V'

    V = Verifyonly

    RESTORE VERIFYONLY can write to history system table if you specify LOADHISTORY option. The question doesn't mention this at all, so the answer is No.

    Urbis, an urban transformation company

  • June 23, 2009 at 8:26 am

    #1013977

    Iggy (1/21/2009)

    igorg (1/21/2009)

    I agree with Warren. In MSDN its clearly written that msdb.restorehistory contains one row for each restore operation.

    For VERIFYONLY restore option column [restore_type] will contain 'V'

    V = Verifyonly

    RESTORE VERIFYONLY can write to history system table if you specify LOADHISTORY option. The question doesn't mention this at all, so the answer is No.

    No. it's a stupid question and the correct answer "Only if LOADHISTORY" is specified isn't available.

    Tom

Viewing 15 posts - 1 through 15 (of 17 total)

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