November 2, 2022 at 8:25 am
Hi all,
I have some questions about database restore.
What are the disadvantages of restoring a database using MDF file only.
I am trying to restore a database from one computer to another and I have noticed that the LDF file size is too huge (around 190gb) and mdf size is 20gb.
What if I only use MDF file to restore database and create new log file for it?
For now I am only interested in the data inside the tables and not how that data got there.
So if new LDF file is created, will I lose any important log data of old file which I will need later?
Regards.
November 2, 2022 at 9:01 am
The log file is an important part of the database as it is used to both roll forward and roll back on recovery. (ie When a transaction commits it is written to the log. Transactions are only written to the mdf when a checkpoint occurs; this happens asynchronously later.)
If you just want some test data then restoring from the MDF only is probably okay. If you want to retain production data then you need to include all database files, including the log, in the recovery. Basicly the log guarantees the A and D bits of ACID.
Also you really need to find out why the log file is so large. Are log backups being done, are there coding problems causing transactions not to commit etc.
November 2, 2022 at 11:50 am
The log file is an important part of the database as it is used to both roll forward and roll back on recovery. (ie When a transaction commits it is written to the log. Transactions are only written to the mdf when a checkpoint occurs; this happens asynchronously later.)
If you just want some test data then restoring from the MDF only is probably okay. If you want to retain production data then you need to include all database files, including the log, in the recovery. Basicly the log guarantees the A and D bits of ACID.
Also you really need to find out why the log file is so large. Are log backups being done, are there coding problems causing transactions not to commit etc.
Well I have tried a lot but its not letting me restore just mdf file. Its giving different errors about missing ldf files or ldf file is not proper etc.
I read somewhere that it is possible to create new LDF file but thats not working either.
When I created this db, I didnt set and log backups. I sometimes take manual backup of whole db as .bak file.
How do I check these 2 things you have mentioned: Are log backups being done, are there coding problems causing transactions not to commit etc.
November 2, 2022 at 1:52 pm
You can't "restore" the mdf file on its own. You can attach it though. Here's an example.
By the way, sounds like the database is in FULL recovery, but you're not running log backups. Might want to look into that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 2, 2022 at 2:50 pm
You can't "restore" the mdf file on its own. You can attach it though. Here's an example.
By the way, sounds like the database is in FULL recovery, but you're not running log backups. Might want to look into that.
On the back of Grant's fine comment, have you run a log backup and then observed the log file's free space?
😎
Could you tell the number of VLFs?
November 2, 2022 at 4:14 pm
Start with the original source system - either implement daily full backups with frequent transaction log backups, or switch the database recovery model to simple and only schedule daily full backups. Which one you choose depends on how much data loss you are willing to suffer.
Once you have that done - then shrink the log file to a reasonable size. If you decide to go with full recovery and log backups - you may need to run multiple cycles of shrinkfile and log backups to get the size down to a reasonable size. I would shrink the file as far as possible and then manually grow it to 512MB under either recovery model.
Once you have that done - take a backup, copy the backup file to the other system and restore that backup. The restored database will be the same size as the source systems with all data up to the end of the full backup.
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
November 3, 2022 at 12:58 pm
You can't "restore" the mdf file on its own. You can attach it though. Here's an example.
By the way, sounds like the database is in FULL recovery, but you're not running log backups. Might want to look into that.
Yes its in full recovery mode and I haven't taken log backups up till yet but I have just put one backup process on. The log file size is around 190GB.
What should be my next step after taking the backup of this log file? How should I maintain it?
November 3, 2022 at 12:59 pm
Grant Fritchey wrote:You can't "restore" the mdf file on its own. You can attach it though. Here's an example.
By the way, sounds like the database is in FULL recovery, but you're not running log backups. Might want to look into that.
On the back of Grant's fine comment, have you run a log backup and then observed the log file's free space? 😎
Could you tell the number of VLFs?
Ok backup process is running. How do I check VLFs?
November 3, 2022 at 1:05 pm
Start with the original source system - either implement daily full backups with frequent transaction log backups, or switch the database recovery model to simple and only schedule daily full backups. Which one you choose depends on how much data loss you are willing to suffer.
There will be data loss in both situations?
November 3, 2022 at 9:25 pm
Jeffrey Williams wrote:Start with the original source system - either implement daily full backups with frequent transaction log backups, or switch the database recovery model to simple and only schedule daily full backups. Which one you choose depends on how much data loss you are willing to suffer.
There will be data loss in both situations?
Potential data loss - yes.
If you set the recovery model to simple - you can only perform a full backup, no transaction log backups. If you have to go back to the full backup to recover you could lose all data from the last known good backup forward. If the latest backup file is corrupted and you have to resort to the previous backup - then you would lose all data from that point forward.
If you set the recovery model to full - and you are performing log backups every hour and have to restore, then you would restore the latest full backup and all transaction log backups since that full backup up to the current point in time. If the system crashed 59 minutes after the last log backup you could lose up to an hour's data. If any of those log backups are corrupted - that is the end of the log chain and you would lose all data after that log backup.
Note: there is the possibility of performing a tail-log backup, and if you can do that then you could restore all the data up to the point of the failure. As long as you have the full backup and all log backups to the current point in time.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply