Moving Database Files Detach/Attach or ALTER DATABASE?

Question

Moving Database Files Detach/Attach or ALTER DATABASE?

Viewing 15 posts - 16 through 30 (of 58 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

David Benoit (5/27/2009)
Jonathan Kehayias (5/27/2009)
Brian Munier (5/27/2009)
Interesting article, thank you. My question is if detach/attach is going away, and I want to move a database from one server to another server does that mean I now have to use backup/restore which can be slower and require more effort to work?
You should use CREATE DATABASE [databasename] FOR ATTACH as shown in the quote from the Books Online. This has a much more verbose syntax and allows for the options needed to be configured while the database is created to include, who the owner should be, one of the other problems listed in the comments here.
...and there is a great script for this HERE[/url]

Excellent script David. Thanks for pointing that out.

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 2

GRE (5/27/2009)
--------------------------------------------------------------------------------
Have i missed a trick here? I understand that the old way to attach a database is be depreciated, But is SP_Detach_DB bein decreciated too? or will that remain?
Yes, both the attach and detach stored procedures are deprecated, here is the page for sp_detach_db from Books Online (see the Important box just after the syntax section):
http://msdn.microsoft.com/en-us/library/ms188031.aspx

Yes i saw that....but It offers no alternative,like the SP_Attach_Db BOL entry does, so if both these sp are being depreciated, how are you going detach a db in the first place to warrant attaching it using this new script?

Gethyn Elliswww.gethynellis.com

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 3

Noel McKinney (5/27/2009)

GRE (5/27/2009)
Have i missed a trick here? I understand that the old way to attach a database is be depreciated, But is SP_Detach_DB bein decreciated too? or will that remain?

Yes, both the attach and detach stored procedures are deprecated, here is the page for sp_detach_db from Books Online (see the Important box just after the syntax section):

http://msdn.microsoft.com/en-us/library/ms188031.aspx%5B/quote%5D

Yes i saw that....but It offers no alternative,like the SP_Attach_Db BOL entry does, so if both these sp are being depreciated, how are you going detach a db in the first place to warrant attaching it using this new approach?

Gethyn Elliswww.gethynellis.com

Jorge Segarra SSCrazy Points: 2571 More actions · Answer 4

Jonathan Kehayias (5/27/2009)
jboc (5/27/2009)
On occasion a log file will fill up to the problem point and we could use detach/attach to delete the old log and start over. Not something that we would normally want to do but something that was an option in an emergency. Is there a simple way to do that once detach/attach goes away?
This should never be used as an option. It is essentially ripping the transactional heart out of your database. Despite being successful doing this numerous times in the past, it only takes once for the disasterous effects of doing this to ruin your day, job, career when the database fails to attach without its log.
I'd recommend reading Paul Randal's article:
http://technet.microsoft.com/en-us/magazine/cc895648.aspx
and his blog posts on the Transaction Log:
http://sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx
Specifically:
http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx
http://sqlskills.com/BLOGS/PAUL/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx
When you attach without the log file, it is rebuilt, which can leave your database incosistent, not a good thing to do.

Please please listen to Jonathan and Paul's advice on this. Do not EVER delete the transaction log like that. Recently I had to deal with that mess and Paul helped me through it. He wrote a blog post about it: http://www.sqlskills.com/BLOGS/PAUL/post/A-sad-tale-of-mis-steps-and-corruption-(from-today).aspx

I highly suggest you read that post to understand why you never want to do this.

=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management

don_goodman Say Hey Kid Points: 670 More actions · Answer 5

It is a terrible practice to detach a database from a production server. If you had any corruption in any database file or in the server itself, you will never be able to re-attach it. I know from experience this is so.

The CORRECT way to migrate a database and its files to a new location is through a manual backup and restore.

Ensure you use INIT in the backup statement. Ensure you disconnect the users before restoring it.

In this way you avoid business exposure to corruption. If it is corrupt, it will not restore. But the database will remain online for the business to use it while you and your team figure out what to do about the corruption. Then in an orderly, non-destructive way, you can migrate the business to a new solution, whatever that is.

NEVER detach a production database unless you want to answer the business challenge with a job interview elsewhere.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Brian Munier (5/27/2009)
Interesting article, thank you. My question is if detach/attach is going away, and I want to move a database from one server to another server does that mean I now have to use backup/restore which can be slower and require more effort to work?

Brian,

I actually wrote another article on this exact topic that is pending publication here called "Use Backup/Restore to Minimize Upgrade Downtimes". It will be published sometime in the middle of next month, and details how to minimize the downtime for upgrades by using Backup/Restore. It might be a bit more involved, but the benefits far outweigh any additional efforts.

JJ B SSCertifiable Points: 5130 More actions · Answer 7

What a great article. And the questions and answers in the discussion have been equally illuminating. Thanks.

turnegw1 SSC Enthusiast Points: 130 More actions · Answer 8

Jonathan,

Would the method described in your article work if, instead of moving files to a different location, I wanted to change the underlying physical files for a given database? What would be the effect of using it instead of attach/detach?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

turnegw1 (5/27/2009)
Jonathan,
Would the method described in your article work if, instead of moving files to a different location, I wanted to change the underlying physical files for a given database? What would be the effect of using it instead of attach/detach?

What do you mean by change the underlying physical files for a given database? Like renaming them or something else?

turnegw1 SSC Enthusiast Points: 130 More actions · Answer 10

I have a test database used by some third party software. I have two versions of it that contain vastly different data so that different scenarios can be tested. Currently for example, we detach the database when the physical file is a.mdf and attach the same database name with physical file b.mdf. I hope this explains.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 11

Great article. I was under the impression that ALTER DATABASE MODIFY FILE actually moved the file. You seem to indicate that you have to phyically move the files after issuing the comand.

ATBCharles Kincaid

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 12

don_goodman (5/27/2009)
It is a terrible practice to detach a database from a production server. If you had any corruption in any database file or in the server itself, you will never be able to re-attach it. I know from experience this is so.
The CORRECT way to migrate a database and its files to a new location is through a manual backup and restore.
Ensure you use INIT in the backup statement. Ensure you disconnect the users before restoring it.
In this way you avoid business exposure to corruption. If it is corrupt, it will not restore. But the database will remain online for the business to use it while you and your team figure out what to do about the corruption. Then in an orderly, non-destructive way, you can migrate the business to a new solution, whatever that is.
NEVER detach a production database unless you want to answer the business challenge with a job interview elsewhere.

I think there occaions when an deatach/attach is the best option especially when dealing with larger databases and you only have native SQL backup to use...Sometimes you don't have the neccessary disk space to take a backup and restore the database files (2x times the size of the database) expecially when migrating accross two servers where you can detach the db, copy the database files accorss to the new server and reattch there...If you are worried about corruption run a DBCC CHECKDB before you start.

SQL Won't restore the database unless it has exclusive access to it...so if users are connected the restore will fail.

Gethyn Elliswww.gethynellis.com

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 13

Charles Kincaid (5/27/2009)
Great article. I was under the impression that ALTER DATABASE MODIFY FILE actually moved the file. You seem to indicate that you have to phyically move the files after issuing the comand.

Yes,

You issue the ALTER DATABASE commands. Then take the database OFFLINE. Physically Move the files to the location specified in the ALTER DATABASE commands, and then bring the database back ONLINE. It doesn't move the files automatically for you.

DouglasAcosta Ten Centuries Points: 1082 More actions · Answer 14

Great article!!

For production environments it is recommended to do the most effort to accomplish a database moving without losing any of its settings (security, replication, backup profiles...) this means more work also, but what about our development/test environments these usually have a few or not at all "complex" configuration so in my opinion it is necessary to have t-sql instructions like "sp_detach / attach" and others to allow faster tasks inside these environments without much worry about what happens behind the scenes.

Again in the test/development environment I am very used to use SqlServer Management studio to accomplish all management tasks, I think if this processes become obsolete or deprecated means these "common tasks" using Management Studio will be off also?

Best regards

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 15

turnegw1 (5/27/2009)
I have a test database used by some third party software. I have two versions of it that contain vastly different data so that different scenarios can be tested. Currently for example, we detach the database when the physical file is a.mdf and attach the same database name with physical file b.mdf. I hope this explains.

I haven't actually tested it, but you should be able to use ALTER DATABASE to change the filenames out, then take the database OFFLINE and bring it ONLINE and have it switch files out without problems. Something to test. I'll post back here in a bit.