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
 General SQL Server Forums
 Data Corruption Issues
 Database corrupted

Author  Topic 

pupo
Starting Member

3 Posts
Posted - 2009-12-07 : 14:33:52
Hi guys,

I am new to sqldatabase. I am having a problem to open my database. I am not sure what happened to it, but below is error message when I am trying to repair it from this codes.

ALTER DATABASE db1 SET EMERGENCY;
GO
DBCC CHECKDB (db1, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

ALTER DATABASE db1 SET SINGLE_USER;
GO
DBCC CHECKDB (db1, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

Error messages;
Msg 945, Level 14, State 2, Line 2
Database 'db1' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Msg 824, Level 24, State 6, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x58063006; actual: 0x8271ce1b). It occurred during a read of page (1:0) in database ID 5 at offset 0000000000000000 in file 'C:\db1.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: invalid encryption key. It occurred during a read of page (2:0) in database ID 5 at offset 0000000000000000 in file 'C:\db1.LDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.


Can any experts please give me advise? I have been spending so much time fixing it, but i am going no where. I also don't have a backup for this database.

Thank you so much,

Paul

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2009-12-08 : 03:33:19
Why no backup?

First things first. What's in the SQL error log regarding this database?
If you just run ALTER DATABASE db1 SET EMERGENCY; (with nothing else), what does it do?

One step at a time.

Also, does this database have any encryption? (column level or transparent database encryption?)

--
Gail Shaw
SQL Server MVP
Go to Top of Page

pupo
Starting Member

3 Posts
Posted - 2009-12-08 : 10:20:37
quote:
Originally posted by GilaMonster

Why no backup?

First things first. What's in the SQL error log regarding this database?
If you just run ALTER DATABASE db1 SET EMERGENCY; (with nothing else), what does it do?

One step at a time.

Also, does this database have any encryption? (column level or transparent database encryption?)

--
Gail Shaw
SQL Server MVP


Hi Gail,


Thank you for your reply. About the backup, I am really new with sql and I didn't know how and why I need to backup until now. I will try to schedule the backup from now on. :)

I went into SQL error log, but I could not find any more error other than it showed on my previous post.

After I set this database into Emergency, the status was staying as Emergency for a while then changed status to Recovery_Pending. Even though the database was in Emergency status, I still cannot manage to retrieve any data from this database by using Sql management studio.

About encryption, I don't think there is any encryption on this database, because I have never set it up.

I am wondering whether it would be easier just to retrieve only all scripts in database. I would like to get only scripts for tables, views, and stored procedures.


Please help.

Thank you.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2009-12-08 : 13:10:47
Post the SQL error log. I want to see exactly what it says. What's the current state of that database? (query sys.databases for the column state_desc)

My gut feeling, from the errors that you've posted, is that this is completely irreparable and there's no way that we'll be able to get anything back at all. It'll be an all or nothing. Structure and (most) data or nothing at all.

quote:
I will try to schedule the backup from now on. :)

I hope you'll do more than try.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2009-12-08 : 15:05:34
quote:
Originally posted by GilaMonster


quote:
I will try to schedule the backup from now on. :)

I hope you'll do more than try.

--
Gail Shaw
SQL Server MVP


No kidding. pupo, if you're responsible for the database, then making sure that backups are occurring is your most important task. Everything else is secondary.

I walked into a client shop one time as they were having performance issues that they couldn't resolve. Within 15 minutes of arriving I found out that they also hadn't been able to take a backup for several weeks! I said "Ok, let's talk about performance later and start talking about the backups." That got their attention. Especially after I asked what the impact to the business would be if the SQL Servers went away...
Go to Top of Page

pupo
Starting Member

3 Posts
Posted - 2009-12-09 : 09:47:22
Thank you guys for your comment about the backup. I took you guys advise and setup a daily backup for my database yesterday.:)

Hi Gail, Below is the error log on this database. The status of my database is showing "Recovery_Pending"

error log:

12/06/2009 22:50:49,spid54,Unknown,fcb::close-flush: Operating system error (null) encountered.
12/06/2009 22:50:49,spid54,Unknown,Error: 17053<c/> Severity: 16<c/> State: 1.
12/06/2009 22:50:49,spid54,Unknown,fcb::close-flush: Operating system error (null) encountered.
12/06/2009 22:50:49,spid54,Unknown,Error: 17053<c/> Severity: 16<c/> State: 1.
12/06/2009 22:50:42,spid52,Unknown,The operating system returned error 1006(The volume for a file has been externally altered so that the opened file is no longer valid.) to SQL Server during a read at offset 0x00000009c32000 in file 'C:\db1.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.
12/06/2009 22:50:42,spid52,Unknown,Error: 823<c/> Severity: 24<c/> State: 2.
12/06/2009 22:48:49,spid52,Unknown,The operating system returned error 1006(The volume for a file has been externally altered so that the opened file is no longer valid.) to SQL Server during a read at offset 0x00000000162000 in file 'C:\db1.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.
12/06/2009 22:48:49,spid52,Unknown,Error: 823<c/> Severity: 24<c/> State: 2.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2009-12-09 : 14:23:39
Interesting. Is this database on a local drive or a SAN?

Please stop the SQL service and restart it. Once it has restarted, post the error log and check the status of the database again.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

suretalu
Starting Member

2 Posts
Posted - 2009-12-18 : 12:28:54
the data can be also restored with the best mssql recovery program, it is here: http://www.recoverytoolbox.com/microsoft_tool_for_ms_sql_recovery_for_sql_server_2000_corrupt_database.html.
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2009-12-21 : 07:27:03
Interesting. With a total history of 2 posts on this website, you manage to "solve" this OP's problem with a redirection to another website....without an explaination of "why" this redirection is of value. Why do I smell some advertising?
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2009-12-21 : 10:20:33
I must admit, I'm getting tired of all this advertising for 'data recovery' tools that appears in every single thread on data corruption.
I'd like to see some proof that they actually work.


--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site