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? |
|
|
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. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-02 : 12:49:23
|
Restore with verify option .check books online. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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). |
|
|
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. |
|
|
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? |
|
|
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.? |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
|