How to Attach and Detach Databases in SQL Server

Question

Post reply

How to Attach and Detach Databases in SQL Server

Noman072

SSC Enthusiast

Points: 190
More actions
December 30, 2024 at 12:00 am

#4492883

Comments posted to this topic are about the item How to Attach and Detach Databases in SQL Server

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27065 More actions · Answer 1

Great article!

Couple things to add, based upon doing this and encountering issues many times:

Unless you have to, do not move the detached log and data files. Copy and paste them, leaving the original files in place just in case...

Make sure you have a good backup that you can recover from that meets the RPO!!!!! I have had re-attaches fail more than once.

When using the GUI to attach a database, you will need to run SSMS as administrator if you are logged into the server. Otherwise you will receive an error.

Checking the "Update statistics" will take a very long time on a larger database. I would recommend updating statistics after you re-attach the database.

If you are moving from one version to a higher version, you will need to adjust the compatibility if desired.

Before detaching, set the database to "Simple" and shrink the transaction log to almost zero. The file copy will be faster.

Lastly, we almost always did a detach/copy/reattach when we needed to move databases. But, if you are backing up to a local drive and are restoring from the same drive with move, a backup / restore has usually been faster. As an example we added a second set of data and log disks to a server. Backup restore was far faster than attach.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 2

Michael L John wrote:

I would recommend updating statistics after you re-attach the database.

I've seen several people say that. I have to ask "Why"? The statistics are stored in the database just like indexes are.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael L John One Orange Chip Points: 27065 More actions · Answer 3

Jeff Moden wrote:

Michael L John wrote:
I would recommend updating statistics after you re-attach the database.
I've seen several people say that. I have to ask "Why"? The statistics are stored in the database just like indexes are.

I dunno. He had it checked in the detach part. I figured they wanted to update stats!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/