If you are trying to attach a database and you only have the database file (.mdf) and not the transaction log file (.ldf) the steps are fairly similar to what they were in my previous blog – How to attach database using different methods in SQL Server Instance.
Just like when attaching a database with transaction log files, you need to remember some important notes:
- The database must first be detached.
- You need membership in the db_owner fixed database role or CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permissions to Attach Database in SQL Server.
- You will not be able to detach/attach database from a higher version of SQL Server to a lower version. Downgrade cannot be archived using Backup/Restore Database Method or Detach/Attach Database Method either.
- You also need to have permissions on the database folders to be able to move the database files.
One option is to use SQL Server Management Studio (SSMS) to attach database:
1. Connect to SQL Server Using SQL Server Management Studio
2. Right click Databases -> Attach… option from the drop down list
3. In Attach Databases dialog box click on Add… button
4. In Locate Database Files dialog box select the .MDF file of the database and click OK.
5. When you return to Attach Databases dialog box, you will see all the information of the database that you are attaching, under the Databases to attach and Database Details windows. But under Database Details, you will notice that the SQL Server is unable to find the LDF file.
6. To attach the MDF file without the LDF file: select the transaction log file and then click on the Remove
7. Click OK to attach the database to SQL Server.
Or you can use below T-SQL Script:
CREATE DATABASE [Database_Name] ON
( FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DatabaseNameData.mdf’ )
FOR ATTACH_REBUILD_LOG;
GO
When database file (MDF) is successfully attached without transaction log file (LDF), you need to execute DBCC CHECKDB for integrity check.
USE [Database_Name];
GO
DBCC CHECKDB;
GO
