Move user database

Question

Move user database

rchantler

SSCrazy

Points: 2128
More actions
November 13, 2017 at 11:22 am

#341372

Why is detach/attach not recommended for moving a user database?
December 8, 2017 at 6:02 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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

ScottPletcher SSC Guru Points: 100949 More actions · Answer 1

It's no longer needed for moving files around on the same instance. Instead you can update the master catalog with the file locations, take the db offline, move the files, and bring the db back online. That is easier and safer than detach & attach.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

Either method will work, but as Scott mentioned, there isn't a need to detach the databases anymore.

rchantler SSCrazy Points: 2128 More actions · Answer 3

ScottPletcher - Monday, November 13, 2017 11:40 AM
It's no longer needed for moving files around on the same instance. Instead you can update the master catalog with the file locations, take the db offline, move the files, and bring the db back online. That is easier and safer than detach & attach.

Thanks for answering. I see the easier part. Why is it safer?

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 4

I'd say it's slightly safer as none of the meta data for the db is removed and added back. If there were some issue, you might have an issue doing the attach.

For me, I don't consider detach/attach unsafe, but it adds a very small amount of risk that isn't necessary.

rchantler SSCrazy Points: 2128 More actions · Answer 5

Steve Jones - SSC Editor - Monday, November 13, 2017 12:24 PM
I'd say it's slightly safer as none of the meta data for the db is removed and added back. If there were some issue, you might have an issue doing the attach.
For me, I don't consider detach/attach unsafe, but it adds a very small amount of risk that isn't necessary.

Curiosity satisfied. Thank you.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 6

Other issues:
1) if there's an error in a db file (such as would show up in a DBCC CHECKDB), it won't reattach
2) during a reattach, the file gets modified. if the reattach fails for any reason, even once you correct that error, the db may not reattach with the same files. You'll need copies of the original files. In short, to safely use the attach method, you must first make copies of the files to be attached in case the attach fails.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Arsh SSCertifiable Points: 6345 More actions · Answer 7

rchantler - Monday, November 13, 2017 11:22 AM
Why is detach/attach not recommended for moving a user database?

There won't be an easy way out, if binaries get corrupted and it doesn't allow restore with the old DB Name. Experts might put more proper points as have done above.