SQL Server – How to attach database without LDF file

There can be a situation where you don’t have full database backup and you have corrupted your ldf file due to power shutdown (or some one intentionally deleted the ldf file). And you are left with only the data file (i.e mdf file).  You can still attach your mdf file with database and recover it.

Below is an example of how, you can attach a database without ldf file.

Database Name : Northwind

Data File:              C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\northwnd.mdf

Log File:                Not Available

 

Using SSMS : Attaching database without LDF File

  • Connect to the SQL server instance using SSMS
  • From the Object Explorer, right click the Databases node and a drop-down menu will be appeared.
  • Now click on the Attach tab.

 

2

 

 

  • Click on the Add button from appeared dialog box.

3

 

 

  • Now a Locate Database File, select it and click ok. Now you will see your mdf file is selected but SQL server is unable to select your transaction log file because transaction log file was deleted.

 

 

4

 

  • Now if you click OK, it will fail with the operating system error ( unable to open the physical file).

 

 

5

 

 

6

 

  • To attach .mdf file without transaction log file; select the log file and click on the remove button to remove it.
  • Now click on the OK button to attach the database file. SQL server will create a transaction log file for you while attaching the database.

 

7

 

 

 

9

 

 

Hope this was helpful !!!

Advertisements

SQL Server – Different ways to check recovery model

Recovery Model (SQL Server)

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

 

There are many ways to check recovery model of a database in SQL Server.  Below are few commonly used examples.

1. Using Management Studio

Right click on Database in Object Explorer > go to Properties dialog box > Options page >Recovery model

2

 

2. Using catalog view [sys.databases]

SELECT [DatabaseName]=name,
[RecoveryModel]=recovery_model_desc
FROM sys.databases
GO

3

3. Using Metadata function – DATABASEPROPERTYEX()

SELECT ‘AdventureWorks2012’ AS [Database Name],
DATABASEPROPERTYEX(‘AdventureWorks2012’, ‘RECOVERY’)
AS [Recovery Model]
GO

4

 

4. Using Object Explorer Details

Click on the Database Node in Object Explorer. In Object Explorer Details, you can find Recovery Model for all databases.

1

 

 

Hope this was helpful!!!