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
I just Attached .mdf file and removed .ldf, but database performance is very slow, how to improve performance, please suggest.
Thanks dear……………….u really save my alot of time
Error :
The database ‘shopping’ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
for this type of error what i have to do
please reply e
I had a similar situation yesterday where my server had crashed due to some electric fault. all the db on that server were restored properly but one of the db was not restoring. moreover, the same was detached by one of my colleague.
After detaching, the db was not reattaching as the ldf file seem to be corrupt.
I did the following tasks:
1. renamed the original db from orchid.mdf to orchid_old.mdf and orchid_log.ldf to orchid_log_old.ldf
2. created another db with the same name “Orchid” with the files in the same path as that of the old
3. Stopped the SQL server.
4. deleted the newly created DB file (mdf only) from the path and renamed the old mdf file to original name: orchid.mdf
5. now i restarted sql server services. This caused the db to use the old db file with the newly created ldf file for the new db.
6. The database was in suspect mode still
7. next i put this db in emergency mode and single user mode using the following commands:
USE [master]
GO
ALTER DATABASE ‘orchid’ SET EMERGENCY
GO
ALTER DATABASE ‘orchid’ SET SINGLE_USER
GO
8. the next step is very important: i then opened the properties for the database and changed the logging method from simple to full mode
9. again changed the db logging method from full to simple.
10. The database was now working. next i put the db back in multi_user mode by using following command:
ALTER DATABASE ‘orchid’ SET multi_USER
GO
all the above steps mentioned brought my db back in operation,
hope this helps others who are not able to use dbcc tool for rebuilding the log file (being sql server 2005)
ps: by logging method; i meant : the recovery model
Thanks Shahab, you save my life.
TY U SAVED MY LIFE :D lol
Many Thanks!!
it helped me! thanks Pinal!
You rock Pinal. You saved my day. Thanks a ton. Hats-off
I had a server crash and the backups didn’t run for the database backup. I was able to recover the mdf files, but not the ldf files.
When attempting method 1 and method 2, I get the following errors.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘TestDb’. CREATE DATABASE is aborted.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:8). It occurred during a read of page (1:9) in database ID 5 at offset 0x00000000012000 in file ‘D:\SQL Server Database Primary Data File (mdf)0000006.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I have used methods one and two before when restoring databases with no ldf files with no problems. I am not sure what I can do next.
hi Pinal,
master database is corrupted. I don’t have backup also. How to recover that database to start sql server instance? I tried to rebuild master database with no luck. got below error:
Overall summary:
Final result: Failed: see details below
Exit code (Decimal): -2068643838
Exit facility code: 1203
Exit error code: 2
How to start my instance in Local system? Please help
Thanks Pinal, I added Adventureworks2000DW database without log file.
by
Palani
is it possible to attach a AdventureWorksDW2008R2 into sql server 2008 ?
thanks for all of your articles and samples, i use them frequently.
this time it didn’t help
but it gave me some ideas for other problems.
in the end what works for me was regular restore database.
sp_attach_single_file_db (Transact-SQL)
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL). Do not use this procedure on a replicated database.
Try to this way:
USE [master]
GO
CREATE DATABASE [YourDB] ON
( FILENAME = N’C:\Data\yourdb.mdf’ )
FOR ATTACH
GO
hello,, can any one suggest me some solutions for the given scenario?
i do have a database , i am taking regular back of the database
the last log back up i have taken at 12. after that there are few changes on the database (some DML operation). next log back is scheduled at 2 . but at 1 o clock i deleted my MDF file(accidentally) deleted… but my ldf file is still intact
i want to restore my database till the point of failure without taking any tail log backup…
how can i do that?
wow!!! it worked :)
thanx a lot
Thanks Sir, for great work.