How to connect 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 that does not have a log file.

Answer 2

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

Here are the steps (no details):

• Rename the existing .mdf file to .mdf_old
• Create a new database with the same .mdf and .ldf file as the old one.
• Stop sql server
• Rename the .mdf and .ldf files of the new db to .mdf_old and .ldf_old
• Rename .mdf_old to .mdf
• Start sql server
• You should see db in suspicious mode
• Change the database context to the Wizard and allow updates to the system tables.
• Install the database in disaster recovery mode (crawl).
• Stop and restart the SQL server.
• Restore the log.
• Install the database in single-user mode and run DBCC CHECKDB to verify physical consistency.
• Disable updates to system tables.

Answer 3

Try these steps through SQL Server Management Studio.

• Open SSMS and right click on the databases.
• Select the Attach option
• Then click "Add" to add the MDF file.
• Select a file from the list and click Ok.
• Now the screen displays the MDF file and the LDF file (not found)
• Select the LDF file and click "Delete."
• After deleting the LDF file, click OK.
• The MDF file was successfully added to the database list.

Answer 4

Try connecting it by adding the MDF file to the "Attach Databases" dialog. You will notice that a missing LDF file is listed in the dialog box. Follow the steps as shown in the figure:

Answer 5

You can "just do it", this will raise a warning that it cannot find .ldf, but it will still be attached to db.

Answer 6

If you are having problems, make sure the mdf file is not read-only.

Answer 7

Here are the code snippets for programmatically creating .ldf files

Below are 3 methods.

Method -1

In my case, I have my database in the DATA folder.

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

As in my case, the path is as follows.

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

Now here is the first method using the storage procedure (sp_attach_single_file_db) and passing its 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, which after executing the code, go to the database folder where it is located, you will see the .ldf file there.

However, you will receive the 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 the Attach Database application, right-click on the name of your server in the Object Explorer and update it.

Method 2

If your database contains one or more log files, you can use the 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 the database has only one log file, 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 for more information.

Answer 8

 EXEC sp_attach_single_file_db @dbname = 'DBNAME',@physname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBNAME_Data.mdf'; 

I tried and it worked ... Hope this helps.