Attach MDF File Without LDF file by using T-SQL script: You can run a TSQL Script on SQL Query to attach your MDF file and recreate your transaction log file.
Method 1:
Create Database testdb ON
(FILENAME=N'C:\File location of your mdf file\testdb.mdf')
FOR ATTACH_REBUILD_LOG
Go
For Example Here testdb.mdf
is your (.mdf
) file name.
Note : These methods will work only if your database is cleanly shut down
and you have a healthy MDF
file database. Be sure you have valid mdf file backup.
Method 2:
USE [master]
GO
CREATE DATABASE [Testdb] ON
( FILENAME = N'C:\File location of your mdf file\Testdb.mdf' )
FOR ATTACH
GO
Trying to Attach the Damaged SQL Server Database
You can also use stored procedure to attach the (.mdf) file without (.ldf) file.
USE master
GO
EXEC sys.sp_attach_single_file_db @dbname = 'TestDB',
@physname = N'C:\File location of your mdf file\TestDBCopy.mdf'
GO
Note:
This feature (sp_attach_single_file_db
) 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 (SQL
Server Transact-SQL). Do not use this procedure on a replicated
database.
We recommend that you do not attach or restore databases from unknown
or untrusted sources. Such databases could contain malicious code that
might execute unintended Transact-SQL code or cause errors by
modifying the schema or the physical database structure. Before you
use a database from an unknown or untrusted source, run DBCC CHECKDB
on the database on a nonproduction server and also examine the code,
such as stored procedures or other user-defined code, in the database.
For your further ref Here & Here and How to Attach a SQL Server Database without a Transaction Log and with Open Transactions
cleanly
shutdown and no running transaction was their. In your case SQL Server is not sure about status of transaction which was forcefully closed so cannot bring database online – Shanky Aug 9 '17 at 6:18