2

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.

Improve this question
3
  • 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, 2011 at 11:28
  • can you give some details about how Litespeed has helped?
    – Marian
    Jan 27, 2011 at 9:04
  • @Marian i have updated my question
    – garik
    Jan 27, 2011 at 14:48

5 Answers 5

Reset to default
1

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.

Improve this answer
2
  • it has the full recovery model and has not log backups
    – garik
    Jan 26, 2011 at 19:07
  • LiteSpeed works on my test db. Thank you!
    – garik
    Jan 27, 2011 at 0:24
2

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.

Improve this answer
1

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.

Improve this answer
0
1

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...

Improve this answer
0

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;

Improve this answer
3
  • 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, 2011 at 23:07
  • @mrdenny right, i have no mdf file :( (this scenario does not work)
    – garik
    Jan 27, 2011 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, 2011 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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