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.

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?

share|improve this question
add comment

migrated from stackoverflow.com Jan 22 '12 at 13:04

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Even when the database is open for read only access only? –  mark Jan 20 '12 at 10:49
    
@mark: even then, yes - you CANNOT have a SQL Server database without transaction log file. –  marc_s Jan 20 '12 at 11:09
add comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

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.