CODEWITHSUNDEEP
sql-server
Ricardo
Ricardo

Reputation: 1025

How to attach a database without an LDF file?

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

Upvotes: 9

Views: 40600

Answers (9)

Hem Raj Bhatt
Hem Raj Bhatt

Reputation: 1

CREATE DATABASE TESTDB ON (FILENAME = 'C:\MSSQL\DATA\TESTDB.mdf') For ATTACH_FORCE_REBUILD_LOG

Upvotes: 0

Husrat Mehmood
Husrat Mehmood

Reputation: 2310

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.

Upvotes: 6

Ivan Stankovic
Ivan Stankovic

Reputation: 1602

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:

enter image description here

Upvotes: 8

user3571601
user3571601

Reputation:

Try this steps through SQL Server Management Studio

  1. Open SSMS and made a right click on databases.
  2. Select Attach option
  3. Then click on add to attach MDF file.
  4. Select the file from the list and click on Ok
  5. Now the screen shows MDF file and LDF File(Not found)
  6. Choose the LDF File and click on Remove option.
  7. After removing LDF File click on OK.
  8. MDF file attached successfully in the databases list.

Upvotes: 3

swathi kamaraju
swathi kamaraju

Reputation: 31

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... Hopefully this helps.

Upvotes: 3

KielW.
KielW.

Reputation:

If you run into problems, verify that the mdf file is not read-only.

Upvotes: 1

eKek0
eKek0

Reputation: 23289

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.

Upvotes: 3

Kyle West
Kyle West

Reputation: 9098

You can "just do it" it'll throw a warning that it couldn't find the .ldf, but it will still attach the db.

Upvotes: 1

Aaron Alton
Aaron Alton

Reputation: 23236

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

Upvotes: 9

Related Questions