SQL SERVER – Attach mdf file without ldf file in Database

USE [master]
GO
— Method 1: I use this method
EXEC sp_attach_single_file_db @dbname=’TestDb’,
@physname=N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’
GO

— Method 2:

CREATE DATABASE TestDb ON
(FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’)
FOR ATTACH_REBUILD_LOG
GO

— Method 3:
CREATE DATABASE TestDb ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’)
FOR ATTACH
GO

3579_CreateDatabase4Attach

Attaching the Damaged SQL Server Database

USE [master]
GO

CREATE DATABASE [TestDB_Repair]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N’TestDB_Repair_file1′,
FILENAME = N’E:\MSSQL\TestDB_Repair_1.mdf’,
SIZE = 8MB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 64MB)
LOG ON
( NAME = N’TestDB_Repair_log_file1′,
FILENAME = N’E:\MSSQL\TestDB_Repair_1.ldf’,
SIZE = 8MB,
MAXSIZE = 2048GB,
FILEGROWTH = 32MB)

Now we set the database offline.

USE master
GO

ALTER DATABASE [TestDB_Repair] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

At this point we can change the file location of our new database to point to our orphaned mdf file and set the location of the log file to a non-existent file.

USE master
GO

ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME=’TestDB_Repair_file1′, FILENAME= ‘E:\MSSQL\TestDBCopy.mdf’)
ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME=’TestDB_Repair_log_file1′, FILENAME= ‘E:\MSSQL\TestDBCopy.ldf’)
GO
Let’s bring the database back online.

USE master
GO

ALTER DATABASE [TestDB_Repair] SET ONLINE
GO

2

Rebuilding the SQL Server Transaction Log

USE master
GO

DBCC TRACEON(3604)
GO

ALTER DATABASE TestDB_Repair SET EMERGENCY
GO

ALTER DATABASE TestDB_Repair SET SINGLE_USER
GO

DBCC CHECKDB('TestDB_Repair', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS
GO

ALTER DATABASE TestDB_Repair SET MULTI_USER
GO

3

http://littleprograming.blogspot.com/2015/10/sql-server-attach-mdf-file-without-ldf.html

Advertisements