Monday, April 9, 2012
Attach database without LDF file
I came across a situation when a database was detached and accidentally the log (.LDF) file was deleted. I wanted to recover the database without having log file.
Here is how to do this.
First I am detaching the database for which files are located here
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'TestDB'
After detaching the database I manually deleted the LDF file
Now first attach the database without files.
EXEC sp_detach_db @dbname = 'TestDB';
At this time you will not see the database in SSMS under databases
Once database is attached use sp_attach_single_file_db SP to attach the DB with just MDF file. LDF file will be automatically created at default location specified in server properties.
EXEC sp_attach_single_file_db @dbname = 'TestDB',
Now run following query to confirm both data and log files
select * from sys.database_files
Find more details about sp_attach_single_file_db here
There is another way to recover database without LDF files.
CREATE DATABASE TestDB
ON (FILENAME = 'D:\MSSQL\Data\TestDB.mdf') FOR ATTACH ;
Find more details about CREATE DATABASE FOR ATTACH here