August 13, 2009 at 10:39 am
Long story short.
Our server running SQL 2000 crashed and we had to move all data to our new servers running SQL 2008.
I want to attach the DB from 2000 into 2008. However I get an error message saying the LDF is corrupt.
I've tried to piece together advice from similar postings but to no avail. What are the steps I need to take to restore this DB? (FYI, I had other DBs that I was able to transfer fine using this method. This is the only problem one.)
Thank you,
Paul
August 13, 2009 at 12:19 pm
You could try attaching without the LDF in either new installation of 2000 or 2008 starting with the later, if your database is not transaction heavy you will be fine. If you are transation heavy then you need a backup to restore or you may run into some serious issue.
In the attach GUI remove the blank LDF and you should be able to attach only the MDF and look in the database for issues.
This should get you started.
Kind regards,
Gift Peddie
August 13, 2009 at 12:46 pm
yeah I tried that and get this message in 2008 (2000 gives the same message pretty much):
==============
Attach database failed for Server '21444-52024'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not open new database '[dbname]'. CREATE DATABASE is aborted.
File activation failure. The physical file name "m:\data files\microsoft sql server\MSSQL\data\[dbname]_log.LDF" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. (Microsoft SQL Server, Error: 1813)
August 13, 2009 at 1:06 pm
You may need to talk to Microsoft support if you don't have a backup or try Lumigent to see if you can repair the LDF file.
http://www.iturnity.com/?Products:Lumigent_Log_Explorer
Kind regards,
Gift Peddie
August 14, 2009 at 1:48 am
Do you have backups? If so, restore them.
If not, take a look at this blog post for a method of hacking the DB back into the server and the repairs that will be required afterwards.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
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
September 4, 2009 at 3:27 pm
A couple of weeks ago someone told me to attach the database without a log file. And I went Huh? But it's actually quite easy. Attach the database using Management Studio, select the .mdf file, highlight the .ldf file and click remove. SQL Server will create a new .ldf file in the default log file location.
September 4, 2009 at 3:45 pm
cathy.baker (9/4/2009)
Attach the database using Management Studio, select the .mdf file, highlight the .ldf file and click remove. SQL Server will create a new .ldf file in the default log file location.
That's only true if the database was shut down cleanly before the log was deleted. If it was not, then attempting to attach will result in an error message and the attach will fail.
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
September 8, 2009 at 4:10 am
Hi,
I'd the same problem but I did this in SQL 2000 and 2005, never attempted in 2008.
Use the Management Studio and follow these steps:
--1. Create a database with the same name
--2. Stop SQL SERVER instance and replace the MDF file
--3. Start SQL SERVER and execute:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = 'db1'
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
--4. Restart SQL SERVER. The database will be in emergency mode. Execute:
DBCC REBUILD_LOG(db1,'D:\SQL Server\DataFiles\db1.ldf')
Execute sp_resetstatus db1
--5. Restart SQL SERVER. The database should be ok.
Hope this helps,
Pedro
September 8, 2009 at 5:20 am
That technique is valid on SQL 2000 only. It cannot be done on 2005 or 2008 since the system tables are not updatable any longer and sysdatabases is not a table.
DBCC REBUILD_LOG is also deprecated (if not removed)
There is a fully documented way to put a DB into emergency mode in SQL 2005 and 2005. ALTER DATABASE ... SET EMERGENCY. Once in emergency mode a CheckDB with repair_allow_data_loss will rebuild the log.
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
September 8, 2009 at 7:18 am
Doesn't CREATE DATABASE FOR ATTACH_REBUILD_LOG do the job in SQL Server 2005 or 2008?
Or since the database wasn't "shutdown" properly the command has problems?
Pedro
September 8, 2009 at 7:28 am
PiMané (9/8/2009)
Doesn't CREATE DATABASE FOR ATTACH_REBUILD_LOG do the job in SQL Server 2005 or 2008?
Yes, if the database was shut down cleanly. For the cases where is wasn't, the DB has to be hacked back into the server and an emergency mode repair run.
See - http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
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
September 9, 2009 at 5:32 am
A couple of weeks ago someone told me to attach the database without a log file. And I went Huh? But it's actually quite easy. Attach the database using Management Studio, select the .mdf file, highlight the .ldf file and click remove. SQL Server will create a new .ldf file in the default log file location.
That was my first answer to this thread before offering Lumigent. Per Microsoft their Transaction Logs are proprietary binary files and Lumigent is one of the first companies with license to read the log files. If I have an important database with problems I will test drive Lumigent.
There is nothing wrong with user solutions but a call to Microsoft will tell any user the Transaction Logs are proprietary.
Kind regards,
Gift Peddie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply