3

I need to read some data from a database given by its backup file. So, I issue the RESTORE DATABASE command, which creates two files - the database MDF file and the log LDF file.

Is it possible to restore the database without creating the LDF file?

3 Answers 3

4

In brief: NO.

The transaction log file (the .ldf file) is a vital and absolutely necessary part of any SQL Server database. You cannot skip this, you cannot opt to not have it - just doesn't work. SQL Server must have a transaction log stored in a file - no way around that.

2
  • Even when the database is open for read only access only?
    – mark
    Jan 20, 2012 at 10:49
  • @mark: even then, yes - you CANNOT have a SQL Server database without transaction log file.
    – marc_s
    Jan 20, 2012 at 11:09
4

(If I had the privilages, I would make this a comment and not an answer. But I don't, so here goes.)

Mark, why are you asking? What Marc says is correct. You can't restore the database without the LDF file. However, depending on your future rollback needs for the database and your current objective (save disk space?), the LDF file is not required to read the data.

You can detach the database and physically delete the LDF file and then reattach the database. On SS 2008, a 500k LDF file will be automatically recreated.

I've done this to have much smaller ZIPs to send databases to offshore development teams.

You can also execute SQL command DBCC SHRINKFILE. But, this does not alway minimize log file and there are different methods for different versions of SS. You may want to check this.

1

Your best bet is to use 3th party software and try it yourself. I found one link to get you started - http://www.codeproject.com/Questions/58905/Read-sql-server-backup-files-without-restoring

Your Answer

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

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