database repair without data loss?

Scenario: Database maintenance plan failed in "check data and index linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which revealed a few "page id" problems. It appears all errors on related to one table. The CHECKDB stated specifically: "repair_allow_data_loss is the minimum repair level for the errors found"
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.

Database reindex

Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks
Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor and
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fill
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:

> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks