Answered by:
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
Answers
-
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
- Marked as answer by Olaf HelperMVP, Moderator Sunday, November 10, 2019 8:21 PM
-
- Marked as answer by Olaf HelperMVP, Moderator Sunday, November 10, 2019 8:21 PM
All replies
-
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
- Marked as answer by Olaf HelperMVP, Moderator Sunday, November 10, 2019 8:21 PM
-
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
-
- Marked as answer by Olaf HelperMVP, Moderator Sunday, November 10, 2019 8:21 PM
-
-
-
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.
-
-
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.
-
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
GOHope this would help you.
-
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
-
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. -
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-filesI 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, esquel@sommarskog.se
- Proposed as answer by Dedmon DaiMicrosoft contingent staff Thursday, October 17, 2019 7:50 AM