.mdf and .ldf files

SQL Server Administration
1

Hello Experts,

My online database server got crashed. Fortunatly we recovered the *.mdf and *.ldf files from the crashed hard dick.
Now I want to restore the database from *.mdf and *.ldf files on new sql server.
I did below procedure to attache the database from *.mdf and *.ldf files.
I copied the *.mdf and *.ldf files into current sql server data folder and ran the below script.

use master
exec sp_attach_db 'DB_NAME', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_NAME.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_NAME.ldf'

But i got below error message...

Server: Msg 9004, Level 21, State 1, Line 1
An error occurred while processing the log for database 'DB_NAME'.
Connection Broken

Could you please help me with this problem?
Regards!

2

Might be worth trying an MDF-only attach. Remove the LDF file (re-copy the MDF file if you think it might have been modified by your previous attempts), then:

CREATE DATABASE [DB_NAME] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_NAME.mdf' )
FOR ATTACH

If that goes OK (it will give you a warning message that a new LOG file was created) be sure to use DBCC CHECKDB to validate that the database is not corrupted.

USE MyDatabaseName
GO
-- Last known-good date - search for dbi_dbccLastKnownGood
DECLARE	@strDBName	sysname = DB_Name()
DBCC DBINFO(@strDBName) WITH TABLERESULTS
GO
--
SELECT 'DBCC CHECKDB [' + COALESCE(@@SERVERNAME, 'NULL_SERVER??') + '.' + COALESCE(DB_NAME(), 'NULL_DB??') + '] ...'
GO
DBCC CHECKDB WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY	-- , ALL_ERRORMSGS 
PRINT 'DBCC CHECKDB Done [' + COALESCE(DB_NAME(), 'NULL_DB??') + ']'
GO

There is a risk that an MDF file, which was "in use" at the time of the crash, may not be salvageable. It would be preferable to restore from Backups instead

1 Like
3

Thanks for the help! but I did not succeed. if there are any ideas still?

4

Were you not performing SQL Server backups on the databases?

1 Like
5

What error do you get?

Are you sure that you have recovered the files correctly? e.g. not accidentally use a Text Copy instead of a Binary Copy or somesuch? If the files have been recovered from "undelete" or a tape dump of the whole disk (rather than file-by-file tape), or somesuch then there is a high possibility that the file is not "intact" - that said, files usually attach even with a large amount of damage (that can then be review with DBCC CHECKDB)

1 Like
6

you can also attach .mdf without .ldf file. See here for How To Attach MDF File Without LDF File

1 Like
7

Try

exec sp_Attach_Single_File_DB to attach the MDF file. It will create a new Ldf file.

Also have a look in books online for...

sp_add_log_file_recover_suspect_db

If these options don't help then you could save file use a utility called SQL Server Recovery Toolbox. It is a powerful tool for sql server recovery MDF file which I've heard a lot of good reviews. I hope it will help you. http://www.oemailrecovery.com/sql_recovery.html

Also have a look this Recommended actions
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp

1 Like
8

Thank you all for the help!