Restoring of backup file giving error

Hi,
Recently when I restoring my backup file from another XP 2003 standard
server on another XP 2003 server, I get the 'internal consistency error
occurred..., SQLState: 42000'
Even using the 'RESTORE VERIFYONLY ...' command reported that my backup file
is valid but I can't restore it.
Therefore,
01) How can I ensure that my backup file is fine, able to restore back in
this case.
02) Is there any ways that I need to ensure/process to take note of before
backing up my database
03) How to fix above issues if I encountered similar issue again.
04) Any recommendation/advise to prevent similar problem again
05) Any good tool to fix/solve this issue.
Thank youSee what Books Online has to say about VERIFYONLY and you will see that it b
asically only check that
the file is readable and it is a SQL Server backup file. With that in mind:

> 01) How can I ensure that my backup file is fine, able to restore back in this cas
e.
Perform an actual restore of the backup.

> 02) Is there any ways that I need to ensure/process to take note of before backing
up my database
Run regular DBCC CHECKDB for the database. You should always have the option
to restore from a
database backup taken when the database was clean (and then possibly restore
all subsequent
transaction log backups if you want to try to reach zero data loss).

> 03) How to fix above issues if I encountered similar issue again.
See 02

> 04) Any recommendation/advise to prevent similar problem again
See 02

> 05) Any good tool to fix/solve this issue.
The problem is that probably the source database has a corruption in it. Res
tore will not allow
restoring an inconsistent database. And you probably don't want to bring ove
r the database with
corruption in it. I recommend you start on the source machine and follow bel
ow guidelines:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Recently when I restoring my backup file from another XP 2003 standard ser
ver on another XP 2003
> server, I get the 'internal consistency error occurred..., SQLState: 42000
'
> Even using the 'RESTORE VERIFYONLY ...' command reported that my backup fi
le is valid but I can't
> restore it.
> Therefore,
> 01) How can I ensure that my backup file is fine, able to restore back in
this case.
> 02) Is there any ways that I need to ensure/process to take note of before
backing up my database
> 03) How to fix above issues if I encountered similar issue again.
> 04) Any recommendation/advise to prevent similar problem again
> 05) Any good tool to fix/solve this issue.
> Thank you
>
>
>|||From BOL:
"Verifies the backup but does not restore the backup. Checks to see that the
backup set is complete and that all volumes are readable. However, RESTORE
VERIFYONLY does not attempt to verify the structure of the data contained in
the backup volumes"
In effect, it checks that all the files are there nd that the header
inforamtion is right.
With SQL Server 2000, the only way you know you have valid backups is the
actaully restore it on another machine.
What is the actual error code as you might have actual DB corruption, and
the restore process is actaully finding it.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vladimir Sim" wrote:

> Hi,
> Recently when I restoring my backup file from another XP 2003 standard
> server on another XP 2003 server, I get the 'internal consistency error
> occurred..., SQLState: 42000'
> Even using the 'RESTORE VERIFYONLY ...' command reported that my backup fi
le
> is valid but I can't restore it.
> Therefore,
> 01) How can I ensure that my backup file is fine, able to restore back in
> this case.
> 02) Is there any ways that I need to ensure/process to take note of before
> backing up my database
> 03) How to fix above issues if I encountered similar issue again.
> 04) Any recommendation/advise to prevent similar problem again
> 05) Any good tool to fix/solve this issue.
> Thank you
>
>
>|||Hi,
Thank for your valuable advise.
However, I have problem restoring the same database that was backup on
Thursday
but able to restore the same database that was backup on Friday, Satursday
and Sunday.
Why is that so, please explain ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
> See what Books Online has to say about VERIFYONLY and you will see that it
> basically only check that the file is readable and it is a SQL Server
> backup file. With that in mind:
>
> Perform an actual restore of the backup.
>
> Run regular DBCC CHECKDB for the database. You should always have the
> option to restore from a database backup taken when the database was clean
> (and then possibly restore all subsequent transaction log backups if you
> want to try to reach zero data loss).
>
>
> See 02
>
> See 02
>
> The problem is that probably the source database has a corruption in it.
> Restore will not allow restoring an inconsistent database. And you
> probably don't want to bring over the database with corruption in it. I
> recommend you start on the source machine and follow below guidelines:
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
> news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
>|||Hi,
In addition, if the only way to test that the backup is valid is by restore,
is there
any best know way to automate this process as I need to ensure the backup
files
are not corrupted.
Does other third party SQL server tool offer any help in this area ? Do they
suffer the same problem ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
> See what Books Online has to say about VERIFYONLY and you will see that it
> basically only check that the file is readable and it is a SQL Server
> backup file. With that in mind:
>
> Perform an actual restore of the backup.
>
> Run regular DBCC CHECKDB for the database. You should always have the
> option to restore from a database backup taken when the database was clean
> (and then possibly restore all subsequent transaction log backups if you
> want to try to reach zero data loss).
>
>
> See 02
>
> See 02
>
> The problem is that probably the source database has a corruption in it.
> Restore will not allow restoring an inconsistent database. And you
> probably don't want to bring over the database with corruption in it. I
> recommend you start on the source machine and follow below guidelines:
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
> news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
>|||> Why is that so, please explain ?
Impossible to answer. If you post the RESTORE command you execute and the er
ror message(s), we might
get a clue. Also, post the RESTORE command you execute from a backup that wa
s successfully restored
(the Fri, Sat or Sun backup).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:%236YE4schFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thank for your valuable advise.
> However, I have problem restoring the same database that was backup on Thu
rsday
> but able to restore the same database that was backup on Friday, Satursday
and Sunday.
> Why is that so, please explain ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
>|||It all depends on how you schedule your backups. If you schedule a simple BA
CKUP command from a TSQL
Agent jobstep, then just add a jobstep that does a RESTORE (to a new databas
e name) and then drop
that database. Log the errors to an output file and make sure you are notifi
ed on errors.
Most people don't to restore of each backup, but are satisfied doing regular
DBCC CHECKDB and then
do test restore at more or less random intervals.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:uUje7schFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In addition, if the only way to test that the backup is valid is by restor
e, is there
> any best know way to automate this process as I need to ensure the backup
files
> are not corrupted.
> Does other third party SQL server tool offer any help in this area ? Do th
ey
> suffer the same problem ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
>

Restoring of backup file giving error

Hi,
Recently when I restoring my backup file from another XP 2003 standard
server on another XP 2003 server, I get the 'internal consistency error
occurred..., SQLState: 42000'
Even using the 'RESTORE VERIFYONLY ...' command reported that my backup file
is valid but I can't restore it.
Therefore,
01) How can I ensure that my backup file is fine, able to restore back in
this case.
02) Is there any ways that I need to ensure/process to take note of before
backing up my database
03) How to fix above issues if I encountered similar issue again.
04) Any recommendation/advise to prevent similar problem again
05) Any good tool to fix/solve this issue.
Thank you
See what Books Online has to say about VERIFYONLY and you will see that it basically only check that
the file is readable and it is a SQL Server backup file. With that in mind:

> 01) How can I ensure that my backup file is fine, able to restore back in this case.
Perform an actual restore of the backup.

> 02) Is there any ways that I need to ensure/process to take note of before backing up my database
Run regular DBCC CHECKDB for the database. You should always have the option to restore from a
database backup taken when the database was clean (and then possibly restore all subsequent
transaction log backups if you want to try to reach zero data loss).

> 03) How to fix above issues if I encountered similar issue again.
See 02

> 04) Any recommendation/advise to prevent similar problem again
See 02

> 05) Any good tool to fix/solve this issue.
The problem is that probably the source database has a corruption in it. Restore will not allow
restoring an inconsistent database. And you probably don't want to bring over the database with
corruption in it. I recommend you start on the source machine and follow below guidelines:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Recently when I restoring my backup file from another XP 2003 standard server on another XP 2003
> server, I get the 'internal consistency error occurred..., SQLState: 42000'
> Even using the 'RESTORE VERIFYONLY ...' command reported that my backup file is valid but I can't
> restore it.
> Therefore,
> 01) How can I ensure that my backup file is fine, able to restore back in this case.
> 02) Is there any ways that I need to ensure/process to take note of before backing up my database
> 03) How to fix above issues if I encountered similar issue again.
> 04) Any recommendation/advise to prevent similar problem again
> 05) Any good tool to fix/solve this issue.
> Thank you
>
>
>
|||From BOL:
"Verifies the backup but does not restore the backup. Checks to see that the
backup set is complete and that all volumes are readable. However, RESTORE
VERIFYONLY does not attempt to verify the structure of the data contained in
the backup volumes"
In effect, it checks that all the files are there nd that the header
inforamtion is right.
With SQL Server 2000, the only way you know you have valid backups is the
actaully restore it on another machine.
What is the actual error code as you might have actual DB corruption, and
the restore process is actaully finding it.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vladimir Sim" wrote:

> Hi,
> Recently when I restoring my backup file from another XP 2003 standard
> server on another XP 2003 server, I get the 'internal consistency error
> occurred..., SQLState: 42000'
> Even using the 'RESTORE VERIFYONLY ...' command reported that my backup file
> is valid but I can't restore it.
> Therefore,
> 01) How can I ensure that my backup file is fine, able to restore back in
> this case.
> 02) Is there any ways that I need to ensure/process to take note of before
> backing up my database
> 03) How to fix above issues if I encountered similar issue again.
> 04) Any recommendation/advise to prevent similar problem again
> 05) Any good tool to fix/solve this issue.
> Thank you
>
>
>
|||Hi,
Thank for your valuable advise.
However, I have problem restoring the same database that was backup on
Thursday
but able to restore the same database that was backup on Friday, Satursday
and Sunday.
Why is that so, please explain ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
> See what Books Online has to say about VERIFYONLY and you will see that it
> basically only check that the file is readable and it is a SQL Server
> backup file. With that in mind:
>
> Perform an actual restore of the backup.
>
> Run regular DBCC CHECKDB for the database. You should always have the
> option to restore from a database backup taken when the database was clean
> (and then possibly restore all subsequent transaction log backups if you
> want to try to reach zero data loss).
>
>
> See 02
>
> See 02
>
> The problem is that probably the source database has a corruption in it.
> Restore will not allow restoring an inconsistent database. And you
> probably don't want to bring over the database with corruption in it. I
> recommend you start on the source machine and follow below guidelines:
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
> news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
>
|||Hi,
In addition, if the only way to test that the backup is valid is by restore,
is there
any best know way to automate this process as I need to ensure the backup
files
are not corrupted.
Does other third party SQL server tool offer any help in this area ? Do they
suffer the same problem ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
> See what Books Online has to say about VERIFYONLY and you will see that it
> basically only check that the file is readable and it is a SQL Server
> backup file. With that in mind:
>
> Perform an actual restore of the backup.
>
> Run regular DBCC CHECKDB for the database. You should always have the
> option to restore from a database backup taken when the database was clean
> (and then possibly restore all subsequent transaction log backups if you
> want to try to reach zero data loss).
>
>
> See 02
>
> See 02
>
> The problem is that probably the source database has a corruption in it.
> Restore will not allow restoring an inconsistent database. And you
> probably don't want to bring over the database with corruption in it. I
> recommend you start on the source machine and follow below guidelines:
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
> news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
>
|||> Why is that so, please explain ?
Impossible to answer. If you post the RESTORE command you execute and the error message(s), we might
get a clue. Also, post the RESTORE command you execute from a backup that was successfully restored
(the Fri, Sat or Sun backup).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:%236YE4schFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thank for your valuable advise.
> However, I have problem restoring the same database that was backup on Thursday
> but able to restore the same database that was backup on Friday, Satursday and Sunday.
> Why is that so, please explain ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
|||It all depends on how you schedule your backups. If you schedule a simple BACKUP command from a TSQL
Agent jobstep, then just add a jobstep that does a RESTORE (to a new database name) and then drop
that database. Log the errors to an output file and make sure you are notified on errors.
Most people don't to restore of each backup, but are satisfied doing regular DBCC CHECKDB and then
do test restore at more or less random intervals.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:uUje7schFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In addition, if the only way to test that the backup is valid is by restore, is there
> any best know way to automate this process as I need to ensure the backup files
> are not corrupted.
> Does other third party SQL server tool offer any help in this area ? Do they
> suffer the same problem ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
>