Post reply

Database Log File

  • June 3, 2022 at 4:10 pm

    #4043873

    Hello,

    Whenever I am restoring the database from the backup to the database with the different name in SQL Server 2016 and 2019, it releases the used space from the log file. I need to preserve this used space for the testing, so the new database would be exactly the same as the old one.

    Is there a way to do this?

    I could not find the answer Online and need it ASAP. So, any help would be really appreciated.

    Thank you.

    Alex

     

     

     

  • June 3, 2022 at 6:00 pm

    #4043917

    Not that I know of.

    You could increase the log size to match the size it was originally if you wanted to.  That may take some time as log space must be preformatted before it can be used.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • June 3, 2022 at 7:05 pm

    #4043969

    I  thought a restored database uses the same space as the original database.

  • June 3, 2022 at 9:36 pm

    #4044032

    The data in the log file is used to roll forward and/or roll back changes that were in progress during the backup.  Once that process has completed - the log records are no longer useful and all VLF's are marked as reusable.  I believe the starting point is also reset to the beginning of the log file.

    This does not resize the actual log file - so if that is the issue then there is some other process and/or code being run after the database has been restored that is shrinking that file.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • June 5, 2022 at 1:22 am

    #4044423

    To Homebrew01:

    I thought so too. And it does. But it releases the used space during restore. I need the mimic of the original database. You would think that restore operation should have some parameter(s) to make it happen.

    To Jeffrey:

    If I am restoring from the full backup, not touching transaction log or Full and Transaction Log; it does not matter the used space is still released.

     

    Unfortunately, the answers that I received so far do not answer my question.

    Any other thoughts?

    Thank you.

  • June 5, 2022 at 4:16 am

    #4044458

    AER wrote:

    To Homebrew01:

    I thought so too. And it does. But it releases the used space during restore. I need the mimic of the original database. You would think that restore operation should have some parameter(s) to make it happen.

    To Jeffrey:

    If I am restoring from the full backup, not touching transaction log or Full and Transaction Log; it does not matter the used space is still released.

    Unfortunately, the answers that I received so far do not answer my question. Any other thoughts?

    Thank you.

    Post the code that you're using to do the restore with.  If you're using the GUI, have it generate the SQL.  Change ONLY any server names and database names.

    In the meantime, check the server settings and see if  "auto-shrink" is enable on the source and the target servers.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 6, 2022 at 2:28 am

    #4044791

    This is a script that I am using:

    USE [master]

    RESTORE DATABASE [DBName] FROM DISK = N'BackupFile' WITH FILE = 1, MOVE N'DataFile' TO N'MDFFile', MOVE N'LogFile' TO N'LDFFile', NOUNLOAD, STATS = 5

    AutoShrink is set to FALSE (default)

    As per the BBCodes I am not sure how they can be used in SQL Backup.

    Can you provide example(s)?

    I am still looking for the solution and again would appreciate any reasonable outcome.

    Thank you.

    Alex

     

     

     

  • June 7, 2022 at 7:11 am

    This was removed by the editor as SPAM

  • June 7, 2022 at 2:47 pm

    #4045679

    I'm not able to reproduce your situation with sqlserver 2019.

    I restored a database backup to a  db with a different name  ( example backup of T1 database is restored to DB1 )  the log file size is kept as it is.

    Is it possible that there is a job that shrink database logfile on your server ?

  • June 7, 2022 at 4:41 pm

    #4045730

    Set the size manually after restorng and see if it does not get shrunk after wards.

    ----------------------------------------------------

  • June 8, 2022 at 11:45 am

    This was removed by the editor as SPAM

  • June 8, 2022 at 11:45 am

    This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply