How to attach a database without an LDF file?

Question

How can I attach a database without an LDF file in SQL Server?

Answer 1

You can use sp_attach_single_file_db to attach a database which is missing it's log file.

Answer 2

Try to attach it by adding the MDF file to the Attach Databases dialog. You'll note that the dialog will report the missing LDF file. Follow the steps as shown on the picture:

Answer 3

Here are Code Snippets to programaticaly create .ldf files

Following are 3 Methods.

Method -1

In my case I have my Database in DATA folder.

You can get the full path to your Database by right clicking and then going to properties then you can copy the full path to your Database

As In my case path is as follows.

C:\Program Files\Microsoft SQL Server\MSSQL11.DRIBBLEE\MSSQL\DATA

Now here is First Method by using store procedure(sp_attach_single_file_db) and passing it arguments(database name and physical path)

 USE [master]
 GO

EXECUTE sp_attach_single_file_db 
@dbname='AdventureWorksDW_2012',
@physname=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL11.DRIBBLEE\MSSQL\DATA\AdventureWorksDW2012_Data.mdf'

GO

execute the code you after executing the code go to your database folder where it resides you will see .ldf file created over there.

However you will get following message in your

The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf" may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL11.DRIBBLEE\MSSQL\DATA\AdventureWorksDW_2012_log.ldf' was created.

Now you can attach your database and after Attaching the Database right click at your server name in Object Explorer and refresh.

Method-2

IF your database have one or more log files missing you can use following

 CREATE DATABASE db_namehere ON
 (
    FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_namehere.mdf')

FOR ATTACH_REBUILD_LOG
GO

Method-3

If you database has only one log file missig you can use this

 CREATE DATABASE db_name ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH
GO

Further you can read in BOOKs Online to get more information.

Answer 4

You can try what is posted here by MohammedU. Basically, what he uses the DBCC REBUILD_LOG command. It will work depending on the version of your server.

Here are the steps (without details):

1. Rename existing .mdf file to .mdf_old
2. Create a new database with same .mdf and .ldf file as old one.
3. Stop the sql server
4. Rename .mdf and .ldf files of the new db to .mdf_old and .ldf_old
5. Rename .mdf_old to .mdf
6. Start sql server
7. You should see db in suspect mode
8. Change the database context to Master and allow updates to system tables
9. Set the database in Emergency (bypass recovery) mode.
10. Stop and restart SQL server.
11. Rebuild the log.
12. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency.
13. Turn off the updates to system tables.