Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Moving Files Expand / Collapse
Author
Message
Posted Tuesday, July 1, 2008 11:06 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item Moving Files
Post #527021
Posted Wednesday, July 2, 2008 3:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 1,385, Visits: 1,243
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.
Post #527108
Posted Wednesday, July 2, 2008 3:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #527126
Posted Wednesday, July 2, 2008 4:12 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, January 8, 2009 1:32 AM
Points: 129, 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... :)
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!
Post #527134
Posted Wednesday, July 2, 2008 4:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 1,385, Visits: 1,243
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.
Post #527144
Posted Wednesday, July 2, 2008 6:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:13 AM
Points: 2,878, Visits: 468
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



Post #527213
Posted Wednesday, July 2, 2008 6:37 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
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.


---------------------------------------------------------------------

Post #527248
Posted Wednesday, July 2, 2008 6:49 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
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.


---------------------------------------------------------------------

Post #527258
Posted Wednesday, July 2, 2008 6:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #527263
Posted Wednesday, July 2, 2008 6:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 1,385, Visits: 1,243
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.
Post #527267
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse