SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Files


Moving Files

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12703 Visits: 22
Comments posted to this topic are about the item Moving Files
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1967 Visits: 1249
Hmm, I'm a little confused...

I assumed the question was talking about files from any arbitrary database - but the "ALTER DATABASE ... MODIFY FILE" clause can only (if I understand correctly) be used to move tempdb files, not arbitrary files (not only that, but it also requires a server restart, which is not mentioned).

If this tempdb-limited answer is valid, then the startup with "-D" for moving master databases should also be valid, right?

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18967 Visits: 12426
Tao Klerks (7/2/2008)
Hmm, I'm a little confused...

I assumed the question was talking about files from any arbitrary database - but the "ALTER DATABASE ... MODIFY FILE" clause can only (if I understand correctly) be used to move tempdb files, not arbitrary files (not only that, but it also requires a server restart, which is not mentioned).

If this tempdb-limited answer is valid, then the startup with "-D" for moving master databases should also be valid, right?


Hi Tao,

ALTER DATABASE ... MODIFY FILE can be used to move the fiels for almost any database. Not the master database (and maybe some other system databases are excluded as well), but alll user databases. The possible procedures are outlined in BOL in "Moving User Databases" (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13fb-457d-996a-66ffc2d55b79.htm). Basically, the options listed are:

1) Use ALTER DATABASE to put DB offline; physically move the files; use ALTER DATABASE ... MODIFY FILE to tell SQL Server the new location; use ALTER DATABASE to bring the DB online again,
or
2) Use ALTER DATABASE ... MODIFY FILE to tell SQL Server the new location; stop the service; physcally move the files; restart the service.


The "startup with -D" answer is invalid even if you assume that master database are included in the question, since this answer claims you have to specify database name and file location after -D, whereas in reality you may only specify a single filename (being the location of the datafile for the master database) and definitely no database name.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
luca.pierobon
luca.pierobon
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 35
I was in doubt too if choosing the third option or not, but then I noticed the "name followed by the semicolon" thing and checked that the "-d" only accepted a filename, so at last I excluded that option from my choices... Smile
besides that, I thought that having to specify my database files path from the command line each time I run the server would be quite odd.
have a nice day!
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1967 Visits: 1249
Got it, thanks for the update!

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
dgabele
dgabele
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3950 Visits: 669
Shouldn't Use sp_detachdb and sp_attachdb really be sp_detach_db and sp_attach_db?

I thought it was a trick answer...

Thanks,

Dave



george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25094 Visits: 13698
strictly speaking, with backup\restore method you do not need to drop the database in between.

thought that might be a catch but went with it as it would still work as described.

---------------------------------------------------------------------
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25094 Visits: 13698
Tao Klerks (7/2/2008)
Hmm, I'm a little confused...

I assumed the question was talking about files from any arbitrary database - but the "ALTER DATABASE ... MODIFY FILE" clause can only (if I understand correctly) be used to move tempdb files, not arbitrary files (not only that, but it also requires a server restart, which is not mentioned).

If this tempdb-limited answer is valid, then the startup with "-D" for moving master databases should also be valid, right?


that was a sql 2000 restriction, alter databases can be used for any file in 2005 (except resource database). which makes this a good, educational question. Looks like a good option to move replicated databases.

---------------------------------------------------------------------
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58763 Visits: 9730
I missed it because of the typo in sp_attachdb. Thought it might be intentional, but was wrong.

As an aside, Microsoft recommends against that specific method. They say to use Alter Database in preference, and to use Create Database For Attach instead of sp_attach_db.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1967 Visits: 1249
ah, thanks George! I wasn't planning on investigating further after Hugo's authoritative explanation, but I was a little weirded out that the BOL (2000) documentation I was looking at mentioned only the TempDB!

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search