Restoring SQL Server database backup file for read-only access - is it possible to skip the ldf file creation?

Question

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?

Answer 1

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.

• mark: Even when the database is open for read only access only?
• marc_s: @mark: even then, yes - you CANNOT have a SQL Server database without transaction log file.

Answer 2

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

Answer 3

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