.MDF recovery after data failure

Hi,
I recently had a problem with a database which resulted in restoring the
database from backup. The .mdf and .ldf files were restored, however I could
not reattach the database.
I attempted to create a new database, stop SQL Server, and copy over the
.mdf and .ldf files which put the database into Suspect/Online. I was not
able to bring the database back online.
Further, after changing the status in to Emergency Mode and attempting a
DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
error with some extra data.
I also tried a third party .MDF data recovery software which yielded 100KB
worth of one table instead for a 100MB database. Any advice?Hi
I am not sure where the problem is if you restored the database from backup!
I can only assume there was data loss because either didn't have full
recovery mode and/or log backups available to do point in time recovery?
If your database is suspect you could try, but make sure that you copy the
files first
http://tinyurl.com/jhv9k
John
"t-o-m-h-VA" wrote:
> Hi,
> I recently had a problem with a database which resulted in restoring the
> database from backup. The .mdf and .ldf files were restored, however I could
> not reattach the database.
> I attempted to create a new database, stop SQL Server, and copy over the
> .mdf and .ldf files which put the database into Suspect/Online. I was not
> able to bring the database back online.
> Further, after changing the status in to Emergency Mode and attempting a
> DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> error with some extra data.
> I also tried a third party .MDF data recovery software which yielded 100KB
> worth of one table instead for a 100MB database. Any advice?|||The recovered MDF file was pulled from disk backup and did not have full
recovery, it was the exact file from the server. The log file is also there
but i tried the steps in the URL,
1.) Reset the status from "Suspect':
EXEC sp_resetstatus 'myDatabaseName'
2.) Set the database property "allow updates':
exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
3.) Set the database to "Emergency Mode":
UPDATE master..sysdatabases SET status=-32768 WHERE
name='myDatabseName'
4.) Rebuild the database log file:
dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ myDatabaseName2_log.LDF')
However, when I try to do a DTS Import it will not authenicate the SYSTEM
user saying initalization failed. I suspect the .mdf file is corrupt
somehow, is there any recovery tools to view the MDF file?
"John Bell" wrote:
> Hi
> I am not sure where the problem is if you restored the database from backup!
> I can only assume there was data loss because either didn't have full
> recovery mode and/or log backups available to do point in time recovery?
> If your database is suspect you could try, but make sure that you copy the
> files first
> http://tinyurl.com/jhv9k
> John
>
> "t-o-m-h-VA" wrote:
> > Hi,
> >
> > I recently had a problem with a database which resulted in restoring the
> > database from backup. The .mdf and .ldf files were restored, however I could
> > not reattach the database.
> >
> > I attempted to create a new database, stop SQL Server, and copy over the
> > .mdf and .ldf files which put the database into Suspect/Online. I was not
> > able to bring the database back online.
> >
> > Further, after changing the status in to Emergency Mode and attempting a
> > DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> > error with some extra data.
> >
> > I also tried a third party .MDF data recovery software which yielded 100KB
> > worth of one table instead for a 100MB database. Any advice?|||Hi
Check to see if you have orphaned users see
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#XSLTH3182121121120121120120 also there is link regarding resolving permissions issues.
John
"t-o-m-h-VA" wrote:
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also there
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:
> > Hi
> >
> > I am not sure where the problem is if you restored the database from backup!
> > I can only assume there was data loss because either didn't have full
> > recovery mode and/or log backups available to do point in time recovery?
> >
> > If your database is suspect you could try, but make sure that you copy the
> > files first
> > http://tinyurl.com/jhv9k
> >
> > John
> >
> >
> >
> > "t-o-m-h-VA" wrote:
> >
> > > Hi,
> > >
> > > I recently had a problem with a database which resulted in restoring the
> > > database from backup. The .mdf and .ldf files were restored, however I could
> > > not reattach the database.
> > >
> > > I attempted to create a new database, stop SQL Server, and copy over the
> > > .mdf and .ldf files which put the database into Suspect/Online. I was not
> > > able to bring the database back online.
> > >
> > > Further, after changing the status in to Emergency Mode and attempting a
> > > DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> > > error with some extra data.
> > >
> > > I also tried a third party .MDF data recovery software which yielded 100KB
> > > worth of one table instead for a 100MB database. Any advice?|||>is there any recovery tools to view the MDF file?
http://www.officerecovery.com/mssql/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"t-o-m-h-VA" <tomhVA@.discussions.microsoft.com> wrote in message
news:31D481E6-B762-4090-8A79-E469E3CD661D@.microsoft.com...
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also there
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:
>> Hi
>> I am not sure where the problem is if you restored the database from backup!
>> I can only assume there was data loss because either didn't have full
>> recovery mode and/or log backups available to do point in time recovery?
>> If your database is suspect you could try, but make sure that you copy the
>> files first
>> http://tinyurl.com/jhv9k
>> John
>>
>> "t-o-m-h-VA" wrote:
>> > Hi,
>> >
>> > I recently had a problem with a database which resulted in restoring the
>> > database from backup. The .mdf and .ldf files were restored, however I could
>> > not reattach the database.
>> >
>> > I attempted to create a new database, stop SQL Server, and copy over the
>> > .mdf and .ldf files which put the database into Suspect/Online. I was not
>> > able to bring the database back online.
>> >
>> > Further, after changing the status in to Emergency Mode and attempting a
>> > DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
>> > error with some extra data.
>> >
>> > I also tried a third party .MDF data recovery software which yielded 100KB
>> > worth of one table instead for a 100MB database. Any advice?