Moving Database Files Detach/Attach or ALTER DATABASE?

Question

Moving Database Files Detach/Attach or ALTER DATABASE?

Jonathan Kehayias

One Orange Chip

Points: 26778
More actions
May 26, 2009 at 11:23 pm

#204612

Comments posted to this topic are about the item Moving Database Files Detach/Attach or ALTER DATABASE?
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

Viewing 15 posts - 1 through 15 (of 58 total)

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

Lasse Schioettz Right there with Babe Points: 739 More actions · Answer 1

Hey Jonathan,

Good article. A couple of minor things to mention, though. To detach the database you use "sp_detach_db" - not "sp_attach_db". It may also be worth noting that the physical_name you look up in the master db is the current file path and the FILENAME argument to the ALTER DATABASE MODIFY FILE is the path to the new location.

Sincerely,

Lasse

gabriele.valerio SSC Rookie Points: 39 More actions · Answer 2

Hi Jonathan,

it seems to me that another problem with detach/attach is that you potentially lose the original db owner. If I am right this is another good reason to use ALTER DATABASE.

Regards,

Gabriele Valerio

dmajkic SSC Enthusiast Points: 103 More actions · Answer 3

If sp_attach_db is to be deprecated, what is "the new" way to just attach a database?

BTW:

Is it possible to add a few xp calls to do file copy on server, and make moving automatic?

Brian Munier SSCrazy Points: 2875 More actions · Answer 4

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?

sejal p gudhka SSCrazy Points: 2102 More actions · Answer 5

Hi All,

One more thing to add to this is that if you want to move TEMPDB database to different location, Alter DAtabase is only option you have.

You need to restart SQL Server services in order for it to affect.

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

dmajkic (5/27/2009)
If sp_attach_db is to be deprecated, what is "the new" way to just attach a database?
BTW:
Is it possible to add a few xp calls to do file copy on server, and make moving automatic?

This is covered in the BOL reference quoted in the article:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL).

You could do xp calls to copy the files, you want to open xp_cmdshell up on your server which is a security risk, that is why it is disabled by default. I don't recommend that you do it, and I wouldn't do it myself personally.

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

Lasse Schioettz (5/27/2009)
Hey Jonathan,
Good article. A couple of minor things to mention, though. To detach the database you use "sp_detach_db" - not "sp_attach_db". It may also be worth noting that the physical_name you look up in the master db is the current file path and the FILENAME argument to the ALTER DATABASE MODIFY FILE is the path to the new location.
Sincerely,
Lasse

Thanks, bad copy/paste when I was working on this. I'll submit a correction to Steve for that.

The physical_name is not necessarily the current file path. It is the location that SQL will look for the file at startup. If you issue an ALTER DATABASE MODIFY FILE, and then pull the physical_name it will reflect the changes, even if the database hasn't been taken offline, and the files haven't been moved yet.

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

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.

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

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?

Gethyn Elliswww.gethynellis.com

jboc SSC Rookie Points: 46 More actions · Answer 10

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?

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 11

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]

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Noel McKinney Hall of Fame Points: 3452 More actions · Answer 12

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

Brian Munier SSCrazy Points: 2875 More actions · Answer 13

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]

Thanks,

I think my colleague uses this script all the time.

Brian

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

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.