InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 03:38:17 GMT20http://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxThanks much!Mon, 10 Dec 2012 11:26:30 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspx[quote][b]sqlfriends (12/10/2012)[/b][hr][quote][b]Perry Whittle (12/5/2012)[/b][hr]Stopping sql server is a bit like using a sledge hammer to crack a nut. You only need to take the database offline as follows[code="SQL"]ALTER DATABASE mydb SET OFFLINE[/code][ul][li]Once offline, copy the disk files to the new locations.[/li][li]Bring the database online.[/li][li]When the database comes online successfully delete the old files.[/li][/ul][/quote]It looks a step missing in above.Before take offline, should there be a step that change the logical file to another directory?I know how to do in SQL for this step, is there a way to do it in ssms?Thanks[/quote]Yes, you need to use the ALTER DATABASE ... MODIFY FILE command but in your first post you've already done that!!!Mon, 10 Dec 2012 11:05:08 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspx[quote][b]Perry Whittle (12/5/2012)[/b][hr]Stopping sql server is a bit like using a sledge hammer to crack a nut. You only need to take the database offline as follows[code="SQL"]ALTER DATABASE mydb SET OFFLINE[/code][ul][li]Once offline, copy the disk files to the new locations.[/li][li]Bring the database online.[/li][li]When the database comes online successfully delete the old files.[/li][/ul][/quote]It looks a step missing in above.Before take offline, should there be a step that change the logical file to another directory?I know how to do in SQL for this step, is there a way to do it in ssms?ThanksMon, 10 Dec 2012 09:44:54 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxbefore moving file please take full backup of that database .if you are using attached detached for this there may problem .i had face problem while moving file to another location. it is giving file copying error,insufficient system resource ,new file location is SAN drive and mdf size is around 58 GB or use attached detached,please make database offline then move.Mon, 10 Dec 2012 05:02:15 GMTHemant.Rhttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspx[quote][b]Perry Whittle (12/5/2012)[/b][hr]Stopping sql server is a bit like using a sledge hammer to crack a nut. You only need to take the database offline as follows[code="SQL"]ALTER DATABASE mydb SET OFFLINE[/code][ul][li]Once offline, copy the disk files to the new locations.[/li][li]Bring the database online.[/li][li]When the database comes online successfully delete the old files.[/li][/ul][/quote]Yes I know :-) But I've known DBAs who want to do it that way.LeoSun, 09 Dec 2012 20:28:58 GMTLeo.Millerhttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxI strongly recommend you take the database OFFLINE rather than detaching. Detaching as mentioned removes the database from the system catalog, which means some database options (saved in the system catalog) are lost. These don't come back when you attache the database. An example of this id database chaining (which I recommend you don't used if at all possible). LeoSun, 09 Dec 2012 20:25:52 GMTLeo.Millerhttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspx[quote][b]sqlfriends (12/5/2012)[/b][hr]Thanks, but it seems detach and attach is easier and involves less steps to do.[/quote]Do whatever you feel comfortable with, just know that in SQL server 2005 on ALTER DATABASE is the preferred methodWed, 05 Dec 2012 12:16:14 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxNot enough to worry about! the alter database command was designed for just such tasks and is the safest way.Wed, 05 Dec 2012 11:10:58 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxThanks, but it seems detach and attach is easier and involves less steps to do.Wed, 05 Dec 2012 10:46:47 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxAlter database is the preferred method of doing this. Its safer as the database is never actually removed form the instance so any risk when reattaching is mitigated. Also when you reattach the owner of the database could change, which may cause you an issue.Whatever way you do it (and it can be done via backup\restore) having a backup to go back to is sensible.Wed, 05 Dec 2012 10:06:05 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxthey both achieve the same result. Detaching removes the database from the system catalogs whereas offline doesn't.Wed, 05 Dec 2012 10:03:08 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxBy the way, what is the difference by take offline, then copy to anther directory , then bring onlinewith detach the database, copy to another directory, then attach it.Which is better way?ThanksWed, 05 Dec 2012 09:53:33 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxThanks much, will give it a tryWed, 05 Dec 2012 09:44:01 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxStopping sql server is a bit like using a sledge hammer to crack a nut. You only need to take the database offline as follows[code="SQL"]ALTER DATABASE mydb SET OFFLINE[/code][ul][li]Once offline, copy the disk files to the new locations.[/li][li]Bring the database online.[/li][li]When the database comes online successfully delete the old files.[/li][/ul]Wed, 05 Dec 2012 02:04:08 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxThis only moves the logical location of the files. To actually get them to the new location you need to take the database off line in some way so you can move the physical files.Either stop SQL, or take the DB off line, then copy/move the files and start SQL or bring the DB back on line.LeoTue, 04 Dec 2012 18:41:29 GMTLeo.Millerhttp://www.sqlservercentral.com/Forums/Topic1392759-1550-1.aspxI would like to move database files to another drive.I used sql like below:ALTER DATABASE Z_20090501_coreSPSS MODIFY FILE ( NAME = coreSPSS, FILENAME = 'G:\DetachedArchiveDatabase\Z_20090501_coreSPSS.mdf');GOALTER DATABASE Z_20090501_coreSPSS MODIFY FILE ( NAME = coreSPSS_log, FILENAME = 'G:\DetachedArchiveDatabase\Z_20090501_coreSPSS_log.ldf');GOThe file "coreSPSS" has been modified in the system catalog. The new path will be used the next time the database is started.The file "coreSPSS_log" has been modified in the system catalog. The new path will be used the next time the database is started.But when I look at the G directory, the files are not thereWhy is that?ThanksTue, 04 Dec 2012 18:16:04 GMTsqlfriends