To attach a database for which we have only the .mdf file and not .ldf file of the database. The methods will work only when the database has been detached properly.
1. We only have a .mdf file
2. Right click “Databases” and click “Attach” in the object explorer.
3. Now click on “Add”. And browse to the location of the .mdf, select it and click “OK”.
4. Now in the database details it will show the message the message “Not Found” for the Log file.
5. Click in the Log file and then “Remove”.
The file will be removed.
6. Now click “OK”. It will attach the database and will rebuilt the Log.
The above step actually runs the Create Database query. So instead of doing it in GUI you can write it the query as:
CREATE DATABASE [AdventureWorks2012] ON
( FILENAME = N'C:\AdventureWorks2012_Database\AdventureWorks2012_Data.mdf' )
We can also use sp_attach_single_file_db. But as per BOL this feature will be removed from SQL Server 2016 onwards and CREATE DATABASE with FOR ATTACH does the same thing, so that’s why not adding it in the method.
NOTE: This method only works when the database had only 1 Log file.
When I add one more log file to the database and try to attach this database by the above two methods I get the following errors.
When using GUI.
As GUI does the same thing as running the query so its obvious that we will get the error when running the Create database query.
If you are thinking that might be sp_attach_single_file_db will be helpful here.
Answer is NO.
The only method I know off to attach this database is using CREATE DATABASE with FOR ATTACH_REBUILD_LOG.
CREATE DATABASE AdventureWorks2012 ON
(FILENAME = N'C:\AdventureWorks2012_Database\AdventureWorks2012_Data.mdf')
Attaching the database creates only 1 Log file instead of two.
So if you really need two log files than create it.