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
how can i attached mdf files in my sal database
pls reply some one
thank you
Hi everybody,
im using the first method:
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_50.SQLSERVER\MSSQL\DATA\Mydatabase.mdf’
GO
but i have this error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file “C:\Exe & Database\RTO DataBase\Database.mdf” failed with the operating system error 3(The system cannot find the path specified.).
Solution Plz…
Great stuff as usual!
Attach database
How To Attch Database In SqlServer 2008
Hi, I have a big porblem, I delete one data base from sql server I dont know haow to recover it, please SOME ONE THAT CAN HELP ME HOW..! THANK YOU SO MUCH.
thanks, worked.
HI!
i have downloaded a project from internet.. i got to play with it… it need microsoft sql server for db connection…the folder i have downloaded is having 2 db files….one is .mdf and other is .bkp (m sure its for backup).
the problem is when i open my project in visual studio and try to run one of its form…it give me error of “no database connection”..
can you help me where to place that .mdf file in sql server to make it work and only .mdf file would be enough.?
thanks its worked and you saved my day sir :)
Regards,
Manikandan.
Thanks for the post, it is helpful. I tried using all three methods but each method gave me an error which looked like:
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.
Any pointers?
Hi,
I have deleted a database which i don’t need any more. but .mdf and .ldf files are still present in …….\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA folder. these are of huge size. So how can i delete those files. If i delete is there any bad effect to database..?
This solution worked for me
USE [master]
GO
CREATE DATABASE [Supervisor] ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataBase.mdf’ )
FOR ATTACH_FORCE_REBUILD_LOG
GO
can i delete the ldf and mdf file directly from the hard disks?
will this affect any of the MySQL functions? please let me know.
Method 1 worked. Just what I needed to know. Five years later your article is still helping people. Thanks!
Glad that it helped you GollyRojer!
i used this method but the result is
Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\TestDb.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.
How can i do?
OS Error 2 means the file specified is missing. Are you using SSMS which is connected to remote machine? the method would look at the file on that location on the server.
Thanks for saving my data….Sami
Sure, most welcome. Thanks.
Worked for my situation too! Thank you.
Thanks for letting me know Chaz.
Hello ,
I tried all methods but it says, the following message. can you please help?
Could not open new database ‘testdb’. CREATE DATABASE is aborted.
Device activation error. The physical file name ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.LDF’ may be incorrect.
Hi Pinal,
When ever we create new data base it will create 2 files (Like mdf and ldf file) by default how much size it requires and if we want how can we increase size of mdf and ldf files .
Please advice.
1. it would be same as model database MDF and LDF size.
2. You need to use ALTER DATABASE …. FILE…. command
File activation failure. The physical file name “G:\DMarkosHLDB\PharmInventory_log.ldf” may be incorrect.
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 1
Could not open new database ‘TestDb’. CREATE DATABASE is aborted.