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
Very timely – I just had a SAN vanish from my DR environment (don’t ask), and had to replace the MDF files. You have to DROP the database first, as SQL insists it is there (and online, no less), and then use Method 1.
As a great engineer once said, the more complicated they make it, the easier it is to stop up the plumbing.
you saved my day ;)
thx
Perfect article Pinal!
Thank you!
Thank you very much.
you saved my day
thanks sir !
When executing this, getting error Device not ready
EXEC sp_attach_single_file_db @dbname=’TestDb’,
@physname=N’D:\Temp.mdf’
GO
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file “D:\Temp.mdf” failed with the operating system error 21(The device is not ready.).
How to resolve?
Assuming there really is a Drive D:, I suspect a permissions problem. See what account your SQL instance is running under and make sure it has read/write access to the D: drive.
I’m generally not a fan of putting files in the root directory of a drive, you probably want to make a folder.
Also, make sure it is an NTFS drive and not FAT32 (unlikely, but possible). Not sure, but perhaps the newer versions of SQL Server don’t like FAT32 drives.
Thanks, worked well and im back in business.
Thanks I was searching on the web since yesterday and tried couple of other methods as well.
This one worked like a charm.
Cheers
When executing this, getting error File activation failure
EXEC sp_attach_single_file_db @dbname=’platinumlive’,
@physname=N’F:\Hotel Management Soft\platinumlive.mdf’
File activation failure. The physical file name “D:\DataBase Backup\platinumlive_log.ldf” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘platinumlive’. CREATE DATABASE is aborted.
How to resolve?
Your .mdf file is on read only mode just remove…………and then check
I m getting same error. I have removed read only mode also..
When executing this, getting error File activation failure
EXEC sp_attach_single_file_db @dbname=’platinumlive’,
@physname=N’F:\Hotel Management Soft\platinumlive.mdf’
Go
File activation failure. The physical file name “D:\DataBase Backup\platinumlive_log.ldf” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘platinumlive’. CREATE DATABASE is aborted.
How to resolve?
HI Shamim, can you please post the result if you had any?
i want to know how to resolve this problem. Thanks.
Hi Shamim and Nagavara,
i would try to move platinumlive_log.ldf to D:\DataBase Backup if you have one.
someone response plzzzzzzzzz
Shamim, Did you ever get your database back online? We are having the same problem. How did you fix it?
Nice one pinal thank for the same
it worked, thanks
Nice one, saved my day as well!
Method 1 worked like a charm, thanks.
Remember guys to refresh the object explorer so it shows the db attached with the new ’empty’ ldf.
very nice, simple work for developers.thank u
Thank you!
Restoring database in 2008 ,but build the mdf and log file in 2005
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 3415, Niveau 16, État 3, Ligne 1
Impossible de mettre à niveau la base de données ‘TestDb’ car elle est en lecture seule ou contient des fichiers en lecture seule. Rendez la base de données accessible en écriture ou réexécutez la récupération.
Msg 1813, Niveau 16, État 2, Ligne 1
Impossible d’ouvrir la nouvelle base de données ‘TestDb’. Abandon de CREATE DATABASE.
Someone can help me please how can i fix that!!!!!!!!!