none
Shrinking / Deleting Transaction logs from a .BAK file SQL Server 2012

    Question

  • Hi,

    I am using SQL Server 2012 and AX 2012 R2 CU7. I am running into some issue which is detailed as below,

    Details:

    I have taken the back up of Business data from LIVE as a .BAK file (File Name - MicrosoftDynamicAX.bak)

    Size of this file is around 44 GB.

    My Dev Server is having 64 GB in my drive as the free space available. Now while restoring this MicrosoftDynamicAX.bak file on my DEV server, I am getting insufficient available space.

    When i went to LIVE database server, i observed the below,

    Size of MDF file is 44 GB 

    Size of LDF file is 40 GB

    So total of MDF + LDF = 84 GB.

    But the bak file that I have is of 44 GB.

    I read in some blog that while restoring this BAK file it will treat the size of the file to be 84 GB (MDF+LDF).

    (Please correct me if my understanding is wrong)

    I know of the option that we can change to Recovery mode to SIMPLE and then shrink to database(LOG - LDF) to few MBs and then take the backup.

    My query ::

    Is it possible to shrink / delete ONLY the Transaction LOGs from this BAK file  which I already have with me using some command?

    Sincerely,

    Muneeb

    Wednesday, November 9, 2016 7:01 AM

Answers

All replies

  • You need to shrink the log file before implement the backup 

    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

    Wednesday, November 9, 2016 7:39 AM
  • Ok.

    Thank you so much for your feedback.

    Sincerely,

    Muneeb

    Wednesday, November 9, 2016 7:46 AM
  • Just a thought,

    Can we just Pickup the MDF file from LIVE and restore it on my DEV as anyways I am not looking for Transaction logs on my DEV system! (Would be giving a shot with this)

    One query, How different would having ONLY the MDF file as stated above be from other scenario wherein if I will shrink the log on LIVE and then take the back up as a .bak file to restore it on my DEV?

    Sincerely,

    Muneeb


    Wednesday, November 9, 2016 7:51 AM
  • Hi Muneeb,

    Surely you can attach a MDF file without LDF to a SQL Server instance, but it’s not possible to take a copy of the MDF file from your LIVE system without shutting it down first(since the file is used by the SQL Server instance in your LIVE system) and I don’t think that’s what you wanted.

    Attaching MDF file without LDF file normally serves as last resort when your log file is corrupted and you need to save the data, some of database functions wouldn’t work without LDF file.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 9, 2016 10:03 AM
    Moderator
  • Hi Lin,

    Thank you so much for your response.

    I have not made my hands dirty into Database much.

    I would appreciate if you can throw some light on the below concepts and the the technical details in the below mentioned 3 points,

    1. Just picking up the MDF file and restoring it on my DEV

    2. I have replica of PROD which is TEST, I should set the Recovery mode as SIMPLE, then shrink the log file and then revert it back to FULL. After this, I take the backup as a .BAK file

    3. Will the MDF file that we picked up in Point #1 and the MDF file after performing Point #2 be IDENTICAL as what we did in step-2 was done to LDF file explicitly i assume. 

    I would appreciate for your help time and help for the same.

    Thanks a lot in advance.

    Sincerely,

    Muneeb

    Wednesday, November 9, 2016 12:30 PM