Background Story:
One of my friends recently called up and asked me if I had spare time to look at his database and give him a performance tuning advice. Because I had some free time to help him out, I said yes. I asked him to send me the details of his database structure and sample data. He said that since his database is in a very early stage and is small as of the moment, so he told me that he would like me to have a complete database. My response to him was “Sure! In that case, take a backup of the database and send it to me. I will restore it into my computer and play with it.”
He did send me his database; however, his method made me write this quick note here. Instead of taking a full backup of the database and sending it to me, he sent me only the .mdf (primary database file). In fact, I asked for a complete backup (I wanted to review file groups, files, as well as few other details). Upon calling my friend, I found that he was not available. Now, he left me with only a .mdf file. As I had some extra time, I decided to checkout his database structure and get back to him regarding the full backup, whenever I can get in touch with him again.
Technical Talk:
If the database is shutdown gracefully and there was no abrupt shutdown (power outrages, pulling plugs to machines, machine crashes or any other reasons), it is possible (there’s no guarantee) to attach .mdf file only to the server. Please note that there can be many more reasons for a database that is not getting attached or restored. In my case, the database had a clean shutdown and there were no complex issues. I was able to recreate a transaction log file and attached the received .mdf file.
There are multiple ways of doing this. I am listing all of them here. Before using any of them, please consult the Domain Expert in your company or industry. Also, never attempt this on live/production server without the presence of a Disaster Recovery expert.
USE [master]
GO
-- Method 1: I use this method
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
GO
-- Method 2:
CREATE DATABASE TestDb ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO
Method 2: If one or more log files are missing, they are recreated again.
There is one more method which I am demonstrating here but I have not used myself before. According to Book Online, it will work only if there is one log file that is missing. If there are more than one log files involved, all of them are required to undergo the same procedure.
-- Method 3:
CREATE DATABASE TestDb ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH
GO
Please read the Book Online in depth and consult DR experts before working on the production server. In my case, the above syntax just worked fine as the database was clean when it was detached. Feel free to write your opinions and experiences for it will help the IT community to learn more from your suggestions and skills.
PS: Do not try this on production server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
126 Comments. Leave new
Hi Pinal,
Here is my situation. I would like to distribute(or call it send across…. though not to you :-)) the mdf file of my 800GB database without the log file, so that the user (the person who has received my mdf file could attach this mdf to his instance without a log file). Currently the database has both the mdf and ldf file and transactions only happen once in a while perhaps 2 transactions in a day. What do I need to do or the steps need to be taken to distribute such mdf file of a database. Thanks. Josh
OK I re-read the Technical Talk section in the above post. And as it says the database needs to be cleanly shutdown. So my question re-phrased would be how to ensure that my 800GB database is cleanly shutdown and then how could I ensure I would be able to distribute my mdf file across. Thanks Josh
Thank you.
Tried method 1:
USE [master]
GO
— Method 1: I use this method
EXEC sp_attach_single_file_db @dbname=’AD_Report1′,
@physname=N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA’
GO
with a .mdf and received a permissions issue.
Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA”. Operating system error 5: “5(Access is denied.)”.
I checked the permissions of the file in question and all users/groups have full access to the file.
Any ideas?
Permission should be there for SQL Service Account. Use configuration manager to find the account. Worst case, you can add “everyone” user and give permission to whole world. (I know it’s a bad advice but can’t explain more in comment)
Hi
I have a client who also needs to restore from an MDF file. I retrieved the files from an image backup that while not SQL aware , it does freeze the SQL I/O during backup ( as there are event logs that show this at the time of backup.) The concern is that the LDF file is over 2 months older than the MDF file. Should I recover just using the MDF file or use both LDF and MDF. I fear that the old LDF would just corrupt or cause instability in the database
Thanks
i am taking backup Remotely but below error is occurred..i have tried different ways to take like i have tried to move into other Directory but mdf file error occur, i have try to run script but same error occur . please tell me if you are familiar with such issue
“System.Data.SqlClient.SqlError: Read on “D:databases/XYZ.mdf” failed: 23(Data error (cyclic redundancy check).) (Microsoft.SqlServer.Smo) “
Thanks Dave, this article saved my soul big time. We had an issue where one of the SQL Log (BIG LOG)files filled the entire Log Drives. This caused SQL to fail! I would be able to restart the SQL Services, but the corresponding Database for the “BIG LOG” could not reload… It was forever in “In Recovery” status forever. FYI – the “BIG LOG” was taking up 98.6GB of the total 100GB Log Drives… I tried all possible SQL Log Truncate statements to no avail…
Eventually I visited Dave’s site and found this script to Restore a Database without Log file…
USE [master]
GO
EXEC sp_attach_single_file_db @dbname=’TestDb’,
@physname=N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’
GO
So I renamed my “BIG LOG” file to simulate the missing Log file problem, so this time a created new problem for myself… Now I was solving a missing Log issue… I ran the above script and new script was created… I performed few tests to ensure that everything runs fine… After I was happy with my test results I deleted the “BI LOG” and recovered 98.6GB.
Thanks, I hope this might be useful to someone as well.
Regards,
Sammy Machethe
I tried it gd script tnx a lot for ur article
Great¡¡¡¡¡¡¡¡¡
Angelillo – I am glad that it was helpful !
hi.
thank you a lot.
Although this post is over six years old, it worked perfectly in a similar situation: I was provided a .mdf file with no log file in a training course. Used method two and, presto! Perfect.
Thanks for being a great resource Dave!
Excellent!!
It was like life jacket, I accidentally deleted log file and was not able to restore it using management studio. But the very first command given above worked.
thanks So much Pinal , saved me from a bunch of unneeded work
Works so great for me!!!!! Many many thankz!!!!
USE [master] GO
— Method 1: I use this method
EXEC sp_attach_single_file_db @dbname=’TestDb’,
@physname=N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’
GO
—- You know, you save my life. I had been delete the .ldf file and cannot attach .mdf file to create database again. Many thanks
This was the solution for me. Had no ldf file.
Method 1 is not recommended by Microsoft, they recommend that you use CREATE DATABASE … FOR ATTACH instead.
Source:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-2017
Great Post, Thanks lot Pinal..
Hi my database crashed therefore no point of clean shutdown..
while recovering the .mdf file through this and lot of other ways in different websites..
I am able to rebuld log file but the data is all gone….
which is a soup for me…
any help on how to keep the data safe.
Thanks for your life-saving tip (Method 2 worked)