Database Log File

Question

Database Log File

AER

SSCrazy

Points: 2763
More actions
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 7, 2022 at 7:11 am

This was removed by the editor as SPAM
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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

AER SSCrazy Points: 2763 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

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)

AER SSCrazy Points: 2763 More actions · Answer 6

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

jonau1 Grasshopper Points: 19 More actions · Answer 7

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 ?

MMartin1 One Orange Chip Points: 27795 More actions · Answer 8

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

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