Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jonathan Kehayias » Moving Database Files Detach/Attach or ALTER...
Add to briefcase
58 posts, Page 1 of 6
12345»»»

Moving Database Files Detach/Attach or ALTER DATABASE? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jonathan Kehayias
Posted Tuesday, May 26, 2009 11:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742
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
Post #723786
Lasse Schioettz
Posted Wednesday, May 27, 2009 1:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, February 08, 2013 7:25 AM
Points: 443, Visits: 48
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
Post #723847
gabriele.valerio
Posted Wednesday, May 27, 2009 4:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 27, 2009 6:54 AM
Points: 1, Visits: 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
Post #723920
dmajkic
Posted Wednesday, May 27, 2009 4:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 28, 2011 7:02 AM
Points: 1, Visits: 21
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?
Post #723941
Brian Munier
Posted Wednesday, May 27, 2009 4:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 04, 2012 11:13 AM
Points: 257, Visits: 80
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?
Post #723953
sejal p gudhka
Posted Wednesday, May 27, 2009 4:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 11:29 PM
Points: 138, Visits: 236
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.
Post #723956
Jonathan Kehayias
Posted Wednesday, May 27, 2009 5:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742
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 | 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
Post #723961
Jonathan Kehayias
Posted Wednesday, May 27, 2009 5:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742
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 | 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
Post #723964
Jonathan Kehayias
Posted Wednesday, May 27, 2009 5:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742
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.

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
Post #723966
GRE (Gethyn Ellis)
Posted Wednesday, May 27, 2009 5:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024, Visits: 2,768
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 Ellis

gethynellis.com
Post #723973
« Prev Topic | Next Topic »

Add to briefcase
58 posts, Page 1 of 6
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.