How to programatically do a Restore VerifyOnly

Question

How to programatically do a Restore VerifyOnly

bob mazzo

SSCommitted

Points: 1684
More actions
May 8, 2006 at 8:30 am

#112738

I tried to programatically do a Restore VerifyOnly, but it returns no results. I'm kicking off our backup from a Visual Foxpro ODBC connect, which works just fine. However, I would also like to verify the backup.
In t-sql it displays a message "The backup set is valid.", but I can't seem to get that message back via my ODBC connection.
Any ideas here ?
Thanks very much,
Bob

Viewing 5 posts - 1 through 4 (of 4 total)

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

David Manix Say Hey Kid Points: 685 More actions · Answer 1

I have not used Visual FoxPro before, but I know I have done this kind of thing with some simple Visual Basic before and maybe you can use these ideas..

I did what you did but I just had to read the results of my resultset that was returned to get the "backup set is valid/invalid" text..

For example (with VB):

Dim result As QueryResults

Dim getInfo As String

Set result = server.ExecuteWithResults("RESTORE VERIFYONLY FROM DISK ='" & Path to my BAK file I want to check\filename.BAK" & "'")

getInfo = result.GetColumnString(1, 1)

So I execute the VerifyOnly with my ExecuteWithResults and the results of that are in my 'results' (my QueryResults). Then I just read the first (row, column) in the query result set.

Not sure that will help but I thought maybe it would give you some more ideas. I'm sure someone else in this forum will be able to help more specifically than I could.

Good luck.

vadba SSChampion Points: 11133 More actions · Answer 2

If you can get it to work programmatically, as with the VB solution, I would do that. If that doesn't work, here are a few other possibilities:

1. Run the statement using xp_cmdshell and osql.exe

-------------------------------------------

declare @cmd varchar(1000)

SET @cmd = 'osql -S YourServer -E -d YourDatabase -q "RESTORE VERIFYONLY FROM DISK=''c:\sqldata\mssql\backup\yourBackup.bkp''" -o c:\YourFolder\result.txt'

-- an alternate for -o is \\servername\sharename\result.txt'

PRINT @cmd -- always make sure the command is correct before EXEC'ing

EXEC master.dbo.xp_cmdshell @cmd

-------------------------------------------

You could then read the output file from disk. Note that this location is relative to the server your are logged into.

2. Create a job that runs a single TSQL step (RESTORE VERIFYONLY).

In the step edit window, there is an Advanced tab. It allows any output from the step to be written to a text file in either overwrite or append mode.

To use this method, you would start the job from FoxPro instead of directly issuing the RESTORE VERIFYONLY command. Again, you would have to read the file to get the result. Again, the file location is relative to the server on which the job runs.

bob mazzo SSCommitted Points: 1684 More actions · Answer 3

I would love to implement it using the OOP method as follows, but I still don't get a message !

oDatabase = osqlserver.Databases("orderfx1202")

pMsg = ""

oQryResults = oDatabase.ExecuteWithResultsAndMessages("restore verifyonly from

Backup_BeforeEOD", 100, pMsg )

? oQryResults.Rows&& is returning zero ?!!!

? pMsg && still blank ?!!!

Thanks,

Bob

David Manix Say Hey Kid Points: 685 More actions · Answer 4

Might it be as simple as

SET oQryResults = ...

?

I think I have also only ever used the ExecuteWithResultsAndMessages against the server level, never tried against the database level like that..

so maybe:

sql = "Use orderfx1202; restore verifyonly from Backup_BeforeEOD"

oDatabase = osqlserver.Databases("orderfx1202") 'may not need this line..

Set oQryResults = osqlserver.ExecuteWithResultsAndMessages(sql, 100, pMsg)