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.