If MSSQL says successfully backed up... should be fine.... ?

I have created the database maintenance plan for SQL Server for full backup,
differential backup and transactional backup and scheduled. If I see the job
status as completed successfully and if I can see the physically the file
being created, am I on the safe side of thinking that I have the recoverable
backup. I normally check the restore activity once in 6 months. Do I have to
check any thing else to make sure my back up is successful? Does any one has
any experience of not being able to restore the SQL Server instances from
the backup copies.
I am making sure that I am perfect in terms of backup and restore
activities.
SQL2K.
Thank you,
AllenAllenHubatka (AllenHubtka_67@.hotmail.com) writes:
> I have created the database maintenance plan for SQL Server for full
> backup, differential backup and transactional backup and scheduled. If I
> see the job status as completed successfully and if I can see the
> physically the file being created, am I on the safe side of thinking
> that I have the recoverable backup. I normally check the restore
> activity once in 6 months. Do I have to check any thing else to make
> sure my back up is successful? Does any one has any experience of not
> being able to restore the SQL Server instances from the backup copies.
There is really only one way to check if a backup is good, and that
is to restore it and run DBCC CHECKDB on it. Whether you always do this,
or only at random occassions depends on how paranoid you are. Personally,
I'd say twice a year is a tad seldom.
Not that backups fail that often, but then again maybe you have some
creeping corruption. I have experienced that DBCC on a restored file
revealed errors that did not exhibit in the original database. This
was in SQL 6.0, so this may not happen today. Then again, it's all a
matter of how paranoid you are...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp