Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is happened? My client has lost the hard disk. He has to restore database.

  • He has the full backup of database 15 days old.
  • He has ldf (log) file of this database.
  • He has NOT mdf file (it was located on that hard disk).

Does it have enough components to restore SQL Server 2008 database, if he used standard options in SQL Management Studio when he did full backup 15 days ago (still database structure has changed)? Is it possible to restore at least database structure (it's possible to regenerate data from other source) that is was actual on crash moment?

Finally:

1) Is it possible to restore all database (data+structure)

2) Is it possible to restore database structure

Thank you.

ADDED:

LiteSpeed for SQL Server Enterprise (6.1 or 5.x) helped me.

1) The database was restored from full backup (15 days old backup)

2) Then LiteSpeed uses copy of mdf file (see previous step) and saved ldf file (log at crash moment) generates sql script of all changes (DDL/DML).

3) Then this script was applied to restored db in step 1)

Any way there were a lot of issues (bugs) of this application, but it helped to restore at least database structure changes.

share|improve this question
    
You might also look through Paul Randal's blogs to see if you can find your scenario: sqlskills.com/BLOGS/PAUL –  Eric Humphrey - lotsahelp Jan 26 '11 at 11:28
    
can you give some details about how Litespeed has helped? –  Marian Jan 27 '11 at 9:04
    
@Marian i have updated my question –  garik Jan 27 '11 at 14:48
    
Thanks. Glad you had a solution to the problem! –  Marian Jan 27 '11 at 15:16

5 Answers 5

up vote 1 down vote accepted

If your database is in simple recovery model, then you cannot perform a point-in-time recovery. If the database is not in simple-recovery model and you have log backups right upto the point of failure without a break in the log chain, then you can take a tail-log backup (if LDF is intact and the command succeeds) as pointed out by yrushka and perform a point-in-time recovery of the database without losing any data. If there are no log backups till now, then nothing can be done unfortunately.

Another option is that if you have the database in full/bulk logged-recovery model and no log backups have been taken, then the transaction log can be read using tools (eg. from Idera, LiteSpeed) which will allow you to generate the commands from the transaction log file which can be replayed on the database once it is restored.

Additionally, if you have a full backup and all the log backups till now, then you can restore them till the latest available log backup. Then using log reader tools above, you can generate the rest of the commands from the LDF file and replay them against the database.

share|improve this answer
    
it has the full recovery model and has not log backups –  garik Jan 26 '11 at 19:07
    
LiteSpeed works on my test db. Thank you! –  garik Jan 27 '11 at 0:24

It was essential that your database was in the Full recovery model and no transaction log backups were created in the last 15 days. That provided that all transactions that happened after the full database backup were still in your LDF file. Otherwise, there would have been a gap between your LDF and full database backup transactions and you would not be able to recover everything successfully (maybe that was the case)

There's another tool that can be used in the same scenario to read the transactions from the LDF file and create a redo script for them - ApexSQL Log.

share|improve this answer

The LDF is not a log backup, so I think you're stuck with restoring the db as it was during the last full backup. If you had transaction log backups since the last full backup, you could restore those as well and get you up to your most recent one.

Unless someone answers that knows of some tricks to pull, afraid you've lost 15 days worth of data.

share|improve this answer

If you have all the transaction log backups from the last full backup to the present day and the database was operating under the Full backup model, then yes, you can restore right up to the present. You should be relying on the backup to truncate the transaction log anyway...

share|improve this answer

You can restore the database using the LDF file only if you have Full backups and Log backups until now with no gap between them (corrupt log backup). The current log file can be backed up. It is called tail-of-log backup that can be used at the final of the restoration process. You will restore this backup with Recovery option at last after restoring FULL + ALL LOG backups

BACKUP LOG your_db_name TO DISK = 'disk:\location' WITH INIT, NO_TRUNCATE;

share|improve this answer
1  
This requires that the current database is online and functional. As you don't have the MDF you can't bring the database online, so you can't take a backup. –  mrdenny Jan 26 '11 at 23:07
    
@mrdenny right, i have no mdf file :( (this scenario does not work) –  garik Jan 27 '11 at 0:26
    
I have to disagree with mrdenny. tail-of-log backup can absolutely be performed for an offline/suspect database with a missing mdf file. –  yrushka Jan 27 '11 at 9:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.