locked
SQL data move RRS feed

  • Question

  • Would there be any problems using robocopy to copy SQL database data (databases and logs) from one disk to another (both disks NTFS). The SQL service will be stopped and the server will be offline, would like to know if this is the best way to do this data copy?
    Thursday, March 26, 2015 11:37 AM

Answers

  • There's no problem with doing this, just make sure that you copy over the file permissions as well (or grant permissions to the new service account if you're using one).

    You should also read the MSDN document that outlines how to move user databases (if you're remaining on the same SQL instance and just provisioning a new disk). Rather than detach the database you should take it offline when moving the files.

    https://msdn.microsoft.com/en-us/library/ms345483(v=sql.110).aspx

    After bringing the database back online run checkdb just to make sure everything is ok.


    • Edited by jmcmullen Thursday, March 26, 2015 11:41 AM
    • Proposed as answer by Michelle Li Thursday, March 26, 2015 1:20 PM
    • Marked as answer by Michelle Li Wednesday, April 8, 2015 6:27 AM
    Thursday, March 26, 2015 11:41 AM
  • Yes you can use robocopy to copy files 

    http://sqlserverdb.blogspot.in/2012/06/robocopy-files-to-another-drive.html

    I would suggest to take database offline instead of stopping SQL. That way you will be able to move 1 database at a time and less downtime.

    • Proposed as answer by Michelle Li Thursday, March 26, 2015 1:20 PM
    • Marked as answer by Michelle Li Wednesday, April 8, 2015 6:27 AM
    Thursday, March 26, 2015 11:42 AM

All replies

  • There should not be any issues in using robocopy and copying over the files to the new disk. 

    Hope it Helps!!

    Thursday, March 26, 2015 11:40 AM
  • There's no problem with doing this, just make sure that you copy over the file permissions as well (or grant permissions to the new service account if you're using one).

    You should also read the MSDN document that outlines how to move user databases (if you're remaining on the same SQL instance and just provisioning a new disk). Rather than detach the database you should take it offline when moving the files.

    https://msdn.microsoft.com/en-us/library/ms345483(v=sql.110).aspx

    After bringing the database back online run checkdb just to make sure everything is ok.


    • Edited by jmcmullen Thursday, March 26, 2015 11:41 AM
    • Proposed as answer by Michelle Li Thursday, March 26, 2015 1:20 PM
    • Marked as answer by Michelle Li Wednesday, April 8, 2015 6:27 AM
    Thursday, March 26, 2015 11:41 AM
  • Yes you can use robocopy to copy files 

    http://sqlserverdb.blogspot.in/2012/06/robocopy-files-to-another-drive.html

    I would suggest to take database offline instead of stopping SQL. That way you will be able to move 1 database at a time and less downtime.

    • Proposed as answer by Michelle Li Thursday, March 26, 2015 1:20 PM
    • Marked as answer by Michelle Li Wednesday, April 8, 2015 6:27 AM
    Thursday, March 26, 2015 11:42 AM
  • Hi Mika,

    I have different thoughts here instead f migrating using robocopy please use Backup and restore mechanism in SQL Server to move database. This is MUCH BETTER than all other methods

    You dont have to take database offline

    You dont have to take SQL Server offline.

    Please read Copy database using backup and Restore.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Monday, March 30, 2015 7:37 AM