This blog demonstrates attaching a database on the SQL Server Instance which already has the same name database up and running.

Scenario:

Suppose, you got a hardware migration activity. The migration activity involves moving the databases from old hardware to new hardware. Let’s say the server got only one big database to be migrated. During the migration, you decided the use the “Detach and Attach” option instead of backup and restore because it will save some time.
A day later of the successful hardware migration, Application team requested to attach the “just before migration” database file on the same server so that they can perform some data comparison quickly.

Problem:

Now, If you are going to attach the “just before migration” database file on the new server, SQL Server doesn’t let you do so because SQL Server doesn’t allow to keep two databases with the same name in one instance. In this case, you may end-up with following a long process to achieve this task.

Solution:

To get the easier solution, you have to make fool to SQL Server by performing database Hack-Attach. Let’s follow the step-by-step approach to achieve it.

• Create a database, table, and insert a few rows into the table.

USE [master]
GO
CREATE DATABASE [dharmendra]
ON PRIMARY 
(NAME = N'Dharmendra', FILENAME = N'F:\Database_Hack_Attach\dharmendra\dharmendra.mdf' , 
SIZE = 10485KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB 
),
FILEGROUP [USERDATA]  DEFAULT
(NAME = N'Dharmendra1', FILENAME = N'F:\Database_Hack_Attach\dharmendra\dharmendra1.ndf', 
SIZE = 20971KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB 
 )
LOG ON 
(NAME = N'Dharmendra_Log', FILENAME = N'F:\Database_Hack_Attach\dharmendra\dharmendra_log.ldf' , 
SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB 
)
GO
USE [dharmendra]
go
CREATE TABLE dbo.studentlist
(
studentid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL
)
GO
INSERT INTO dbo.studentlist (firstname, lastname) VALUES ('Sachit', 'Keshari')
INSERT INTO dbo.studentlist (firstname, lastname) VALUES ('Dharmendra', 'Keshari')
INSERT INTO dbo.studentlist (firstname, lastname) VALUES ('Henry', 'Yo')
GO
SELECT * FROM [dharmendra].dbo.studentlist

• Verify the created database status, and check the inserted records. The below snapshot confirms that the database “dharmendra” got three records in the table “Studentlist”

• Now, let’s check the database files properties – Logical Name, FileName, and Location.

SELECT 
DB_NAME(DBID) DBName, DBID
name,
filename
FROM SYS.SYSALTFILES
WHERE DB_NAME(DBID) = 'dharmendra'

• Bring the database “dharmendra” offline so that SQL Server allows to copy the mdf,ndf and ldf files of the database to a different location.

USE [master]
ALTER DATABASE [dharmendra] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

• Let’s copy & paste the database files to a different location. In this example, I am copying the files from the folder “F:\Database_Hack_Attach\dharmendra\” to “F:\Database_Hack_Attach\abc\”.

• Now, bring the database “dharmendra” online.

USE [master]
ALTER DATABASE [dharmendra] SET ONLINE

• Try to attach the copied database files from the location “F:\Database_Hack_Attach\abc\”.  You will end up with below error which says “SQL Server doesn’t allow you to attach a database with the same name as an existing database.”

Below is the complete error details

TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'DELL\SQL2017'.  (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
Cannot attach a database with the same name as an existing database. (Microsoft.SqlServer.Smo)
------------------------------

Let’s Perform the database Hack-Attach steps

• Create a dummy database (HackAttach) with the same logical name what we have for the database “dharmendra” to the different location and also verify the created dummy database files’ property.

USE [master]
GO
CREATE DATABASE [HackAttach]
ON PRIMARY 
(NAME = N'Dharmendra', FILENAME = N'F:\Database_Hack_Attach\HackAttach\dharmendra.mdf' , 
SIZE = 10485KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB 
),
FILEGROUP [USERDATA]  DEFAULT
(NAME = N'Dharmendra1', FILENAME = N'F:\Database_Hack_Attach\HackAttach\dharmendra1.ndf', 
SIZE = 20971KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB 
 )
LOG ON 
(NAME = N'Dharmendra_Log', FILENAME = N'F:\Database_Hack_Attach\HackAttach\dharmendra_log.ldf' , 
SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB 
)
GO
SELECT 
 DB_NAME(DBID) DBName, DBID, 
 name as LogicalName, filename
 FROM SYS.SYSALTFILES
WHERE DB_NAME(DBID) = 'HackAttach'

Here is the output of the above script. It confirms that the database “HackAttach” got the same logical name what the database “Dharmendra” has.

• Now bring the database “HackAttach” offline.

USE [master]
ALTER DATABASE [HackAttach] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

• Delete the database “HackAttach” files from the location “F:\Database_Hack_Attach\HackAttach\”

• Copy the database “dharmendra” files from the location “F:\Database_Hack_Attach\abc\” to  “F:\Database_Hack_Attach\HackAttach\”

• Bring the database “HackAttach” online, you will encounter the error 5120.

• To address the error 5120, you need to give access on files/folder by following below steps;

– Right-click the database (mdf/ndf/ldf) files or folder and select “Properties”.
– Go to “Security” tab, click the “Edit” button, and Click the “Add” button.
– Enter the object name to select as NT Service\MSSQL$SQL2017' and click “Check Names” button.
– It will change to MSSQL$SQL2017
– Click “OK” button.
– Give this service account “Full control” to the file or folder.
– Click “OK” button.

• Now, let’s bring the database “HackAttach” online.

• As we have successfully parked the database “dharmendra” files under the difference database (HackAttach), let’s verify the table and the inserted records in the database.

Hope, you enjoyed learning!

The post How to Perform Database Hack-Attach appeared first on .