Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 backup and recovery

Author  Topic 

thaniparthi.rao
Yak Posting Veteran

96 Posts

Posted - 2008-04-02 : 11:43:10

How can we check whether the backup is in consistent state or not.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 11:48:41
What do you mean?
Go to Top of Page

thaniparthi.rao
Yak Posting Veteran

96 Posts

Posted - 2008-04-02 : 12:46:28


we are taking backups . how can we check whether the backup is in good condition or not.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 12:49:23
Restore with verify option .check books online.
Go to Top of Page

thaniparthi.rao
Yak Posting Veteran

96 Posts

Posted - 2008-04-02 : 13:16:53

This is asked in an interview.

1. actually my question is we take backups on a regular basis.
if system crashes we will restore the backups.
suppose if the backup is not working properly we will looose data.
in order to overcome this situation we have to check the backup for consistency.(whether it is in good condition or not)
how can we check the consisteny of backups ? (which methods do we use)

2. some of my tables are increasing rapidly. as a DBA how can we check which tables are increasing rapidly suppose we have around 1000 tables.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 13:23:26
Verify backup upon completion option will check the backup and its consistency. check Sql 2000 admintration for script.
Go to Top of Page

thaniparthi.rao
Yak Posting Veteran

96 Posts

Posted - 2008-04-02 : 13:40:58
Thanks for the reply.
Could u tell me abt the second one.
Go to Top of Page

Haywood
Posting Yak Master

221 Posts

Posted - 2008-04-02 : 13:41:26
The VERIFYONLY option of RESTORE for 2000 is woefully lacking. I've had numerous instances where a VERIFYONLY passes but a restore fails. The VERIFYONLY is much better in 2005 & 2008 (in the fact that it _actually_ works).

The only real way to test is by actually restoring your backups to another server (or database name).
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 13:46:16
Haywood,
We are talking about 2005 over here. Check SQL 2000 Administration forum for the script to check database growth.
Go to Top of Page

Haywood
Posting Yak Master

221 Posts

Posted - 2008-04-02 : 16:31:53
I understand that. I was trying to point out that 2000's verify doesn't work - before someone goes and writes a process that they think will validate the backups for them, when in fact it wont do it correctly.

Who said anything about a script for db growth?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 16:37:10
Who said it doesn't work for SQL 2000? Have documentation from microsoft.?
Go to Top of Page

Haywood
Posting Yak Master

221 Posts

Posted - 2008-04-02 : 17:55:09
From my experiences, it [VERIFYONLY] is not reliable on SQL Server 2000. I have had a number of occasions where a backup would pass a VERIFYONLY check, only to still fail on the restore.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 20:45:06
check this out:

http://www.mssqltips.com/tip.asp?tip=1093
Go to Top of Page

Haywood
Posting Yak Master

221 Posts

Posted - 2008-04-02 : 20:59:17
And all the way at the bottom:

quote:

Keep in mind that using the RESTORE VERIFYONLY statement is not a guarantee of reliable data restoration-it is merely to check the readability of the file. The only sure fire way of knowing if backups will accurately restore data is by performing test restores, which can be done on development servers to avoid service interruption



In my experience, the 2005 version actually works as advertised. You also have the addition of CHECKSUMs during the backup, and if available, VERIFYONLY will also validate the checksums.
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-03 : 14:10:40
The truest test of a backup is to restore them periodically to a dev/test server and verify that what you expected is actually there. Good time to run dbcc checkDB as well, especially if production is a 7 X 24 environment. I know all about the verifyonly and the checksum but for a good sanity check, be sure it works. If disaster strikes, you know you're ready. Plus, it'll give you an idea how long the process will take and what is involved.

Terry
Go to Top of Page
   

- Advertisement -