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.
| Author |
Topic |
|
gmeovr83
Starting Member
4 Posts |
 Posted - 2008-10-06 : 07:49:00
|
| "An error occurred while processing the log for database 'MyDB'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log."Hey all, I have a SQL 2005 database that was upgraded from SQL 2000 some time ago. One day suddenly, the db was in a 'suspect' state. No one had touched it, but something had happened. We've tried EVERYTHING to get this database back up...but to no avail.We tried simply restoring from a day-old backup, but the restore as 'terminating abnormally'. My first thought was that it was corrupted data, so I used a month-old backup, but still got the same error. We were able to restore on another server and its been running fine there for a month or so, but it needs to get back to its original server.I've even tried reinstalling SQL server, but that didn't help at all either. Any help on this issue would be great. I've been racking my brain for some time...but I'm not getting anywhere... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 10:38:43
|
| this is what books online suggestshttp://msdn.microsoft.com/en-us/library/aa337407.aspx |
 |
|
|
gmeovr83
Starting Member
4 Posts |
Posted - 2008-10-06 : 10:51:20
|
| Hmm...the page doesn't really tell me much more than the error message did. I have tried restoring from the backup many times, and with several different backups. How would one go about rebuilding the log? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-06 : 11:41:58
|
You could try using the sp_attach_single_file_db stored procedure to attach your mdf file.quote:
sp_attach_single_file_db works only on databases that have a single log file. When sp_attach_single_file_db attaches the database to the server, it builds a new log file. If the database is read-only, the log file is built in its previous location.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-06 : 11:45:14
|
If you are using SQL Server 2005, you can also use CREATE DATABASE ... FOR ATTACH
E 12°55'05.63"N 56°04'39.26" |
|
|
|
gmeovr83
Starting Member
4 Posts |
Posted - 2008-10-06 : 13:14:10
|
| Thanks. I read the msdn page for CREATE DATABASE % FOR ATTACH and it looks like it might work. However, I may need some help with the syntax. Also, will I be able to attach from a backup? Or will I need actual .ldf and .mdf files?EDIT::Well I got the syntax figured out, and it looks like this is going to do the trick. I have to wait until tomorrow morning to try it, but thanks a lot for the help. I'll post again tomorrow after I try it. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-06 : 15:14:21
|
You do so.Thank you for your feedback.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
gmeovr83
Starting Member
4 Posts |
Posted - 2008-10-07 : 07:53:45
|
| Alas...no luck. I honestly thought that this was going to do it. It created new log files and everything. I actually have 3 databases, say DB1, DB2, and DB3...and initially, DB2 was my problem. But then I had issues restoring DB1 and now, when I tried this, which I thought was going to work, I access DB3 a little bit and it gets marked 'suspect' when I tried to query it.This has to be some sort of environment issue. All three of these databases have been up and running for a month now in another location, but I can't successfully restore them on this server no matter what I try. Does anyone have any other thoughts perhaps? |
|
|
|
|
|
|
|
|
SQL Server Error 9004