SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore of the Database


Restore of the Database

Author
Message
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7565 Visits: 2250
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."
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7555 Visits: 3399
Obvious!

I run on tuttopodismo
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6761 Visits: 1865
Not that obvious. Wink

"Keep Trying"
Warren Gilbert
Warren Gilbert
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 301
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
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3273 Visits: 6497
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)
Lisa Phillip
Lisa Phillip
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2695 Visits: 135
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.
TheRedneckDBA
TheRedneckDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4268 Visits: 2614
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
dgabele
dgabele
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3949 Visits: 668
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!



Steven Cameron
Steven Cameron
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 215
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
TheRedneckDBA
TheRedneckDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4268 Visits: 2614
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search