Recover from error log scan number(93799:2191:1) database not valid

Question

Recover from error log scan number(93799:2191:1) database not valid

timotech

SSCrazy

Points: 2698
More actions
January 20, 2015 at 4:24 am

#300535

Hi Everybody,
Please how do i recover from this error while trying to attach my database:
Could not open new database 'MediaMonitoring'. Create database is aborted
msg 9003, level 20, state 1, line 1
The log scan number (993799:2191:1) passed to log scan in database 'MediaMonitoring' is not valid. This error may indicate data corruption or that he log file (.ldf) does not match the data file (.mdf).
Please i don't have a backup for the database and the database is quite large about 18gb.
Please what can i do.
Thanks
Tim

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

If your database is not production than you can attached mdf file without any ldf file and it will automatically take care of creating new ldf file. check following link for further detail

http://www.mssqltips.com/sqlservertip/1894/attach-a-sql-server-database-with-a-missing-transaction-log-file/

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

timotech SSCrazy Points: 2698 More actions · Answer 2

free_mascot (1/20/2015)
If your database is not production than you can attached mdf file without any ldf file and it will automatically take care of creating new ldf file. check following link for further detail
http://www.mssqltips.com/sqlservertip/1894/attach-a-sql-server-database-with-a-missing-transaction-log-file/
HTH

Thanks free_mascot,

tried your link, but its still bringing the same error above

what should do next

Thanks

Tim

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

free_mascot (1/20/2015)
If your database is not production than you can attached mdf file without any ldf file and it will automatically take care of creating new ldf file.

No, it won't.

SQL can only recreate a log file if the database was cleanly shut down prior to the log being deleted/lost. If there are errors about log scan or missing ldf, then the database was not cleanly shut down and the log cannot be recreated automaticallu

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

timotech (1/20/2015)
Hi Everybody,
Please how do i recover from this error while trying to attach my database:
Could not open new database 'MediaMonitoring'. Create database is aborted
msg 9003, level 20, state 1, line 1
The log scan number (993799:2191:1) passed to log scan in database 'MediaMonitoring' is not valid. This error may indicate data corruption or that he log file (.ldf) does not match the data file (.mdf).

No backup, mis-matched log file?

How much of that database are you willing to lose?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

timotech SSCrazy Points: 2698 More actions · Answer 5

GilaMonster (1/20/2015)
timotech (1/20/2015)
Hi Everybody,
Please how do i recover from this error while trying to attach my database:
Could not open new database 'MediaMonitoring'. Create database is aborted
msg 9003, level 20, state 1, line 1
The log scan number (993799:2191:1) passed to log scan in database 'MediaMonitoring' is not valid. This error may indicate data corruption or that he log file (.ldf) does not match the data file (.mdf).
No backup, mis-matched log file?
How much of that database are you willing to lose?

Well, its not the way you're viewing it, there's actually a backup, but its back dated to september last year, but any solution will be appreciated.

Thanks

Tim

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Can you restore a backup?

If so, do so.

If not, you'll have to hack the DB into the server and repair it, you may lose data, you may lose transactional consistency, you may loose referential integrity, it may not work at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

timotech SSCrazy Points: 2698 More actions · Answer 7

GilaMonster (1/20/2015)
Can you restore a backup?
If so, do so.
If not, you'll have to hack the DB into the server and repair it, you may lose data, you may lose transactional consistency, you may loose referential integrity, it may not work at all.

how do i hack the DB?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

I'm going to assume by that question that you don't have a backup.

If you do have a backup you can restore, please rather do that.

The hack is on Paul Randal's blog post - http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/

Please note, it is not guaranteed to work. It's a last resort for when there's no backup, the database is damaged and the owner of the DB is happy with potentially inconsistent data in the database after it is repaired. If it works, you should check for rows in violation of foreign keys and other logical data problems.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prettsons SSCertifiable Points: 7453 More actions · Answer 9

http://www.mssqltips.com/sqlservertip/1894/attach-a-sql-server-database-with-a-missing-transaction-log-file/

http://www.stellarinfo.com/blog/attach-sql-database-without-transaction-log-file/

Is there any other option?

SQL Database Recovery Expert 🙂