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 ««12

move db files to another directory Expand / Collapse
Author
Message
Posted Sunday, December 09, 2012 8:25 PM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 7:44 PM
Points: 446, Visits: 1,314
I 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).

Leo
Post #1394433
Posted Sunday, December 09, 2012 8:28 PM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 7:44 PM
Points: 446, Visits: 1,314
Perry Whittle (12/5/2012)
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

ALTER DATABASE mydb SET OFFLINE


  • Once offline, copy the disk files to the new locations.

  • Bring the database online.

  • When the database comes online successfully delete the old files.



Yes I know But I've known DBAs who want to do it that way.

Leo
Post #1394434
Posted Monday, December 10, 2012 5:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 03, 2014 4:05 AM
Points: 47, Visits: 236
before 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.
Post #1394551
Posted Monday, December 10, 2012 9:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063
Perry Whittle (12/5/2012)
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

ALTER DATABASE mydb SET OFFLINE


  • Once offline, copy the disk files to the new locations.

  • Bring the database online.

  • When the database comes online successfully delete the old files.



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
Post #1394688
Posted Monday, December 10, 2012 11:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 5,958, Visits: 12,839
sqlfriends (12/10/2012)
Perry Whittle (12/5/2012)
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

ALTER DATABASE mydb SET OFFLINE


  • Once offline, copy the disk files to the new locations.

  • Bring the database online.

  • When the database comes online successfully delete the old files.



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

Yes, you need to use the ALTER DATABASE ... MODIFY FILE command but in your first post you've already done that!!!


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1394715
Posted Monday, December 10, 2012 11:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063
Thanks much!
Post #1394721
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse