Hello,

I'm writing a DTS package to restore databases from backups, this would run
everynight. These backups are often corrupted, which I don't want to try
and restore anything if they are.

I've trying to use the "RESTORE HEADERONLY" command to check if the backup
is corrupted or not, this command will give me a one-record result, just
like a select query.

I need to somehow check the values in one field of this query, using DTS.
If the value is one thing, NULL, proceed to the 'On Success' step, if it is
'*** INCOMPLETE ***', proceed to the "On Fail" Step.

Can someone help me set up this part, I'm not sure exactly how to grab
values out of a result set, and go to appropriate "on fail", or "on success"

Thanks, Michael

Just a quick idea to get you started:

All this can be done within a single SQL Execute task.

Simply create a temporary table, that matches the result set of RESTORE
HEADERONLY command.

Then run the following to capture the output of RESTORE HEADERONLY into this
temp table:

INSERT INTO #YourTable
EXEC ('RESTORE HEADERONLY FROM DISK=''G:\MSSQL\Backup\Pubs.BAK''')

Now you have the resultset inside a temp table. Simply query it and based on
the result either execture the restore or raise an error.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm




[quoted text, click to view]
Hello,

I'm writing a DTS package to restore databases from backups, this would run
everynight. These backups are often corrupted, which I don't want to try
and restore anything if they are.

I've trying to use the "RESTORE HEADERONLY" command to check if the backup
is corrupted or not, this command will give me a one-record result, just
like a select query.

I need to somehow check the values in one field of this query, using DTS.
If the value is one thing, NULL, proceed to the 'On Success' step, if it is
'*** INCOMPLETE ***', proceed to the "On Fail" Step.

Can someone help me set up this part, I'm not sure exactly how to grab
values out of a result set, and go to appropriate "on fail", or "on success"

Thanks, Michael