Saturday, October 5, 2013

How to attach MDF without transaction log file

We can attach MDF file without LDF in two ways:
Using SSMS
Using T-SQL

Using SSMS

SSMS - Databases - Rightclick - Attach - Browse and select the MDF file
In the database details section it displays two files
1) MDF file with location from where you want to attach
2) LDF file with some other location (Select this file and Click on Remove button)
3) Click OK (Database will attached to that location)

Using TSQL

 CREATE  DATABASE AdventureWorksDW2012 ON (FILENAME = N'E:\DATA1\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG

If Database has multiple log files you may not attach with SSMS use the T-SQL Syntax to do so, it will create only one ldf file while attaching not multiples.

No comments: