0

I need to restore a database from a .bak backup. When i tried it is showing insufficient disk space becuase the mdf and ldf files are also moving and in those ldf is aroud 100GB. Is there a way to do it by omiting these files. Also when i checked i can see db restore can also be done by mdf files attaching is this same as normal database restore with all the tables and contents in place.

1
  • 4
    Why not just add more disk space, or restore to another a disk?
    – vonPryz
    Commented Feb 9, 2023 at 9:34

1 Answer 1

2

I need to restore a database from a .bak backup. When i tried it is showing insufficient disk space becuase the mdf and ldf files are also moving and in those ldf is aroud 100GB.

Correct, the MDF and LDF files are the database, which are stored in the backup file. The MDF is the data file for your database, that holds all the data in its current state. The LDF file is the Transaction Log file, which logs all the transactions that occurred in the database to put the MDF in it's current state, essentially.

Is there a way to do it by omiting these files.

Nope, you need both to have a functioning database. It is not possible to even omit the Transaction Log file (LDF) and create a new one, when restoring from a backup. You'll need to increase the available space on the drive of where the database is being restored to, in order to restore that backup.

Also when i checked i can see db restore can also be done by mdf files attaching

Yes, you can copy or move a database by only copying the MDF file and then Attaching it to the SQL Server instance you want to move it to. (There's more steps to the process than that, but just speaking high level of what's possible.)

is this same as normal database restore with all the tables and contents in place.

Not exactly. By leaving the LDF behind and using the ATTACH_REBUILD_LOG, a new one will be created in its place. This is not necessarily a bad thing. But there is a timing risk of when you grab a copy of the MDF without the LDF, that it could be in an inconsistent state, if the database has uncommitted transactions, has not been CHECKPOINTed, or was not shut down cleanly. Then when you attempt to attach the MDF, SQL Server will be unable to complete the CREATE DATABASE...FOR ATTACH_REBUILD_LOG operation, and will return the following error:

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 5
Could not open new database 'YourDatabaseName'. CREATE DATABASE is aborted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.