July 27, 2022 at 10:55 am
Hi,
If this post is not in the correct forum, please let me know which forum it should be in and I will repost.
Here is my issue. I need to make a Backup of a Full Recovery Model Db and Restore that Backup to a Copy Db that is being used for Development. The Copy database has a Simple Recovery Model. The Copy Db should "truncate" the log file to recover hard disk space.
Currently, the Full Recovery Model DB has a .mdf size of 51.5 GB and its .ldf is 57.5 GB.
The end result would be for the Simple Recovery Model Copy Db to have a .mdf of 51.5 GB and a .ldf as small as possible (a couple of MB?).
Using SSMS (ver 15.0.18369.0 but I will use whatever version you might suggest), how do I make a backup up of a full recovery model Db and restore it to a Copy Db with a reduced Log file? How much free hard disc space should there be before I begin this process?
MS SQL Version 10.50.4042.0
Thanks,
pat
July 27, 2022 at 10:58 am
You can restore any kind of db as long as you have a FULL backup (and the needed diff/log restore to get it to the PIT you need).
After the restore, you can alter the databases recovery mode.
alter database [yourdb] set recovery simple with nowait;
Keep in mind your database always needs a log file to support its largest transactions ! ( even in simple recovery )
So, after restore, you can shrink the log file, but set it to a size it needs !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 27, 2022 at 12:20 pm
Johan,
Thank you for the quick reply.
I tried the code "alter database [yourdb] set recovery simple with nowait;". However, SSMS generated an error on "with nowait". When I removed the clause "nowait" the code executed and the recovery model was changed from FULL to SIMPLE. (note: I tested this in SQL Express 2019 and not Standard 2008 R2. Perhaps it is a version issue. No need to comment. I just wanted to explain).
Since these databases are large (at least in my world), I need to ensure I do not encounter any out of disk space errors during this process.
Would I be correct in assuming that SQL Server restores the .bak file to "temporary" .mdf and .ldf files. Then as the restore finishes the original .mdf and .ldf files are deleted and the restored temporary .mdf and .ldf are renamed to the restored database names.
In my mind if the above describes what happens, I need to plan on 109GB for the .bak file PLUS an additional 109 GB of free space during the restore process. Do you know if that is correct?
Thanks,
pat
July 27, 2022 at 1:45 pm
Your assumption is incorrect !
It needs the space, not according to backup size, but according to the size of the files of the original database !
Sequence should be:
/*typo corrected*/
alter database [yourdb] set recovery simple with no_wait;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 1, 2022 at 8:24 am
The scenario you want to do is something most organisations have faced. Unfortunately it is not possible to do it in a single step.
Any database that is in Full Recovery mode when it is backed up will always be restored in that mode. The restored database will always need the same amount of disk space as the original database. If you want to make any changes to this they can only be done after the restore is complete. You have to work within the limits of how the restore process works.
As Johan says, you can change your DB to Simple recovery and truncate the log file after the recovery is complete.
I suggest you try out some of the restore processes you want to do and monitor disk space usage while they are running. This could help you understand more of how Restore works.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 1, 2022 at 7:18 pm
If your ldf is actually larger than your mdf, like you say it is, then you have a much worse problem on your hands. You need to find and fix that problem even if you don't need to copy the database.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 8, 2022 at 10:55 am
Johan, Ed and Jeff, Thank you all for your assistance. I have a much better understanding of the process now. Over the weekend more space was added to this server. So the space problem is solved.
I have made a backup of the database with the "copy only Backup" box checked. The .bak file is 57 GB, not the 108GB I anticipated. I guess this suggests the data inside the .ldf is very small. . Jeff, I did have the numbers reversed. I anticipate the restore will require the full 108 GB for the restored mdf and ldf files.
I will restore the backup to a different Database on the Same SQL server. (Original Db = Data_02. New database = Data. SQL server instance unchanged.).
I will change the Recovery model of the restored database to Simple
I will Truncate the log file.
Thank you all again.
pat
August 8, 2022 at 2:00 pm
Thanks for the feedback, Pat. It sounds like you've got things under control.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply