How to Perform Database Hack-Attach

, 2018-04-23

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 saysSQL 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 .

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads