Insufficient space for logs in a restore. how can get rid of the logs?

Question

Hi guys, 

When I try to restore a database in the Dev Environment it gives error:

There is insufficient space in E:\\Logs....

Is there a way to restore the database from a backup without the logs (which I don't need in the dev environment).

Many Thanks

Answer 1

No, you cannot, but you can add more space to the drive E, do not you?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

You cam read this article 

https://www.stellarinfo.com/blog/attach-sql-database-without-transaction-log-file/

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 3

which I don't need in the dev environment

At least one log file is mandatory, you can not have a database without a log file.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

I did manage to save some space and to restore the db. However, as theoretical speaking I find wrong that you can have the option to restore a db with an empty log file. Why shall I need the log file in another environment (especially dev)? 

Answer 5

A backup is an exact 1:1 copy of the source database and of course includes the complete log file.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 6

@Olaf Agree with the backup but the restore could have an option, do not copy the log file or something similar. 

Answer 7

I did manage to save some space and to restore the db. However, as theoretical speaking I find wrong that you can have the option to restore a db with an empty log file. Why shall I need the log file in another environment (especially dev)? 

SQL Server requires at least one data file and one log file. The product does not know if the environment is prod or dev.  This is a fundamental architecture of the product for decades.

---

22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

• Kevin3NF on Twitter
• Dallas DBAs
• Why is my Log file so big?

Answer 8

Architecture can be changed or modified...

Answer 9

Architecture can be changed or modified...

Yep.  But it hasn't been.

I suspect the issue is you have a large log file in Prod, which as Olaf points out is where this came from.  Root cause might be to verify/alter the prod log file.

---

22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

• Kevin3NF on Twitter
• Dallas DBAs
• Why is my Log file so big?
• PocketDBA™

Answer 10

Transaction log is required. You may check these options...1. Shrink the log file (if possible) before backup and restore or Restore to a server which has enough space and repeat step 1 to restore the db on dev server. 2. Detach the database at source, get copy of the .mdf file and then attach the .mdf on your dev server and SQL server would automatically create a new empty transaction log file.

--Below command creates an empty log file with an copied .mdf file, in my case it just created 1mb log file

USE [master] 
GO
CREATE DATABASE [TestDB] ON 
( FILENAME = N'E:\MSSQL\Data\TestDB.mdf' )
 FOR ATTACH_REBUILD_LOG 
GO

Hope this would help you.

Answer 11

Yes offcourse this is possible.

Just take the backup of your database again but now before taking backup delete all the logs except one log.

And restore the database again.

Yeahh you did it..

Hope this is helpful !!

Thank you

---

If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster

Answer 12

Architecture can be changed or modified...

Assuming MS decided to spend resourced doing that. If you wish for it, report it at https://feedback.azure.com/forums/908035-sql-server and MS can prioritize this among other work.

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 13

Assuming MS decided to spend resourced doing that. If you wish for it, report it at https://feedback.azure.com/forums/908035-sql-server and MS can prioritize this among other work.

Here is a feedback item on this theme:
https://feedback.azure.com/forums/908035-sql-server/suggestions/32902144-restore-database-should-be-able-to-reset-log-files

I will have to say that I sympathise with the idea. If you are restoring a copy to a non-prod environment, you probably aim to setting the recovery to simple, and you are glad if you have to restore that 1TB log file. There could be some options to address this.

---

Erland Sommarskog, SQL Server MVP, [e-mail]