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
Perfect Explanation thank You Very Much Sir……
plz any1 can help me i m in v trouble, i try my best but i am not successful. i have code and database i want my application update mdf file from diff. system. at least of 2 computer network can access .mdf from different location.
i want to put my mdf file in server and my code or application access from different computers attached in network
plz help me
i m not professional i m just seeker
I tried with 2nd method but not able to restore or take a back up of the attached database
It is very dangerous if anyone follows this in production. Please highlight at the top of the post that it should not be done in prodution environment and data loss may occur! I hope no one did it in production environment.
It is very dangerous if anyone follows this in production. Please highlight at the top of the post that it should not be done in prodution environment and data loss may occur!
I just hope no one did it in production environment.
I have a mdf file from a crashed machine and when I try to restore using different tricks I keep getting the error “The log cannot be rebuilt because the database was not cleanly shut down” . Please let me know if you have a solution to restore the DB.
Saved the day again!
Thank you!
One problem I’m getting is this:
“File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf” may be incorrect.”
It seems to me that SQL Server is looking for the log file on a default folder that doesn’t exist in my installation of SQL Server. Any tips? Thank you!
Ok, I found my own answer. I used method 1 from above. This is what I did:
1. Copied the .mdf file to the DATA folder of the server instance I’m using under SQL Server.
2. I used method 1 from Pinal’s description.
(I had Management Studio opened as Administrator, but I think this may not be necessary if you copy to a folder where SQL Server has permissions to create a log file)
3. I got an error referring to SQL not being able to create a log file in a default folder, however it created the log file in the DATA folder of my current instance. After refreshing I could see and query my new database.
This was the message:
“File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf” may be incorrect.
New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.Mydatase\MSSQL\DATA\AdventureWorks2008R2_Data_log.LDF’ was created.”
Now I can work. Thank you so much!
Directory lookup for the file “D:\database1.mdf” failed with the operating system error 21(The device is not ready.).
File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10_50.ACC\MSSQL\DATA\test_1.LDF” may be incorrect.
Msg 5123, Level 16, State 1, Line 3
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘e:\Test_log.LDF’.
Msg 1813, Level 16, State 2, Line 3
Could not open new database ‘Test’. CREATE DATABASE is aborted.
Either the file does not exist or the permission is denied to access the file
Hello , what do I do with this situation?
The log cannot be rebuilt because the database was not cleanly shut down
I had a situation where non of those methods work, bu this method worked for me fine :
1) Create database with same name as MDF file you have.
2) Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.
3) Start SQL Server. Database will be now in suspect state because log file is not correct.
4) Run the following script:
USE [master]
GO
ALTER DATABASE [MyDatabase] SET EMERGENCY
GO
ALTER DATABASE [MyDatabase] SET SINGLE_USER
GO
DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER
GO
ALTER DATABASE [MyDatabase] SET ONLINE
GO
Thanks a lot Shahab… this worked just fine for me.
It is in the state of Emergency mode after SET EMERGENCY AND SET SINGLE_USER in step 4. It doesn’t work to run DBCC CHECKDB.
When the time to run: DBCC CHECKDB (fay_fayetteville_tst, REPAIR_ALLOW_DATA_LOSS)
I got following message:
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SMTKINGDOM\MSSQL\DATA\fay_fayetteville_tst_log.ldf’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘\\seismicmicro\support\client_and_rti_data\SWN\fay_fayetteville_tst\ProjectDatabase\fay_fayetteville_tst_log.ldf’.
Msg 5024, Level 16, State 2, Line 1
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 1
The system could not activate enough of the database to rebuild the log.
DBCC results for ‘fay_fayetteville_tst’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘fay_fayetteville_tst’.
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SMTKINGDOM\MSSQL\DATA\fay_fayetteville_tst_log.ldf’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘\\seismicmicro\support\client_and_rti_data\SWN\fay_fayetteville_tst\ProjectDatabase\fay_fayetteville_tst_log.ldf’.
Msg 5024, Level 16, State 2, Line 1
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 1
The system could not activate enough of the database to rebuild the log.
DBCC results for ‘fay_fayetteville_tst’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘fay_fayetteville_tst’.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.
Thanks Shabab! Your post helped me.
My problem was the transaction log file being deleted while the database is detached. I noticed that detaching locks the physical files.
this method works when no one method works .. thanks
Thanks Mr.Shahab..
this solution was solve my problem
great day! thank you very much mr shahab, the problem finally solved :D
Renn – Thanks for letting us know.
anyone new reviewing the commentary:
I have used this exact method for years. Tried and true. This has worked for me, every time I have had to use it.
Hi Guys..My server got crashed but i recovered the mdf and ldf file..when i try to attach the file..i got following error..
the pageaudit property is incorrect. microsoft sql server error 5172
The header for file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Abakdata.mdf’ is not a valid database file header. The PageAudit property is incorrect.
Can anyone help me to get rid out of this problem ??
Thank you very much in advance..
nice pinal all the three syntax worked …thanx
Pinal sir thanks a lot.
Thanks A Lot…..Sir
Nice article. Thanks to Pinal!