Restoring Database with help of data files

Question

Restoring Database with help of data files

Nayan Maishari

SSC Rookie

Points: 48
More actions
August 27, 2005 at 11:38 pm

#102748

hi guys , i need help with restoration .
I have a kept my transaction log in mirror volumes . Due to bad sector my transaction log was corrupted on both the disk , Now my database is in suspect mode .. i dont have backup also ... what i have is just data files ....
is it possible to restore database with help of data files ... i have try sp_dettach & attach .. but when i try to attach my data files it gives error log file not found ... Is their any solution to rebuild ur database with help of data files & no backup & no transaction log !!!!!!!!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

It is possible to restore without the log file - SQL Server should create a new log for you. Are you working in EM or QA and exactly what is the error message?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Nayan Maishari SSC Rookie Points: 48 More actions · Answer 2

I am working in EM . I have read some article it says if u have 1 data file sql creates a log file . But if u have mutilple data files ...it wont create a log file ..

Zia Khan Old Hand Points: 341 More actions · Answer 3

Hi,

Rename the old data files for the suspeted DB and delete the suspected DB then create new DB with the same name.New DB will create the new data files for new DB rename the new data files and then rename the old data file as the as it was before then u can have the old DB.

Hope it will work.

Regards

Bob Plouffe Valued Member Points: 51 More actions · Answer 4

I have the same problem of having to use my mdf files - I have created new sql2000 server on box with same name - the attach as worked for 1 data file and I will continue on the rest of them - my question is how do I replace master with my old master.mdf?

Thank you in advance -

Bob

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 5

I don't know if it's still relevant but here are the steps I performed several times in test env as a part of disaster recovery tests:

1.First, backup all database files (mdf) before you begin to play with them

2.Rename the database files

3.Create a new db with the same number of data and log files as the original db had (for the files you've got left specify exactly the same size as they have now and for all the files specify the same physical file names as the original)

4.Stop SQL Server

5.Replace the newly created db files with the original files

6.Start SQL Server (there might be a error message in log 'Cannot associate files with different databases.')

7.Database is now suspect. Set it to Emergency mode.

sp_configure 'allow updates', 1

reconfigure with override

go

update sysdatabases

set status = 32768

where name = 'db_name'

go

sp_configure 'allow updates', 0

reconfigure with override

go

8.Now the data should be accessible. You have got at least two options. a) to pull out all data using bcp or DTS b) to use undocumented comman DBCC REBUILD_LOG to recreate (consolidate) your log file.

To use DBCC REBUILD_LOG:

a) stop sql server

b) rename or delete the existing log files

c) start SQL Server (db is now suspect)

d) switch to master database and issue (change parameters as needed)

DBCC REBUILD_LOG (db_name,'C:\Program Files\Microsoft SQL Server\MSSQL\Data\file_name.ldf' )

new log file will have the size 512 KB

e) now db should be ONLINE with RESTRICTED_USER access - you can run consistency checks now

if your transaction log contained committed transactions that were not written to disk at the time of a failure, they will be lost. Also modifications that were under way at the moment of failure and were not completed need rollback which is not possible without the original transaction log. So your database might be in an inconsistent state. Be aware of this. Hope this helps

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 6

coincidences, coincidences...

Last night i was called to work, the sql-database behind our company's VERITAS Backup Exec was defect, with no backup having ever been made (obviously not a server/db i was responsible for). The server could not load the ta-logfile. Having read (and saved to disk) this thread it was an easy thing to get the db running again, following the procedure Martin Mojzisek posted.

THANK YOU MARTIN!!!

The crucial point was that even though SQL server does not attach databases without valid logfile, if it's started and the logfile is invalid, you _can_ set the emergency mode and modify the db.

karl

Best regards
karl

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 7

I'm glad that this procedure helped you get out of trouble. In fact it works even in the case you lost all secondary data files to extract data from primary data file. If you are interested in these non-standard solutions, I would recommend Chris Kempster's ebook which is an excellent source of information in this area.