Moving DB to new drive

  • Hi everyone

    I am planning on buying a new SSD to store my SS DB.  I plan to leave the actual SS on the old drive and just move the DB to the new drive.  I found the steps to do this online:

    https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-ver16

    I have a few questions:

    1. Should I move the SS to the new drive too?  Both the new and old drives are NVME SSD so in terms of speed both are comparable.
    2. I have SSIS package that calls and updates the DB.  Do I need to make any changes to the SSIS package if only the DB is moved to the new drive?

    Thank you

    1. No
    2. No

    And, that is the correct method for moving dbs to a new drive.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher wrote:

    1. No
    2. No

    And, that is the correct method for moving dbs to a new drive.

    Thank you for the prompt reply.

  • water490 wrote:

    ... steps to do this online: ....

    online? No way !

    Keep in mind you have to put the database offline and only then you can start moving its files to the new location !

    1 ) Prepare your new drive so it has the target folders ( and the needed secutity )

    cmd as Administrator needed !!

    rem /T Creates directory structure, but does not copy files. Does not include empty directories or subdirectories.
    rem /T /E includes empty directories and subdirectories.
    rem /O Copies file ownership and ACL information

    xcopy e:\Instance1\MSSQL F:\Instance1\MSSQL /E /T /O

    2) Ask for down time for the given database

    3) alter the database file locations as mentioned in the article

    4) put the database offline

    5) copy or move the files

    6) bring the database online

    7) Verify the file changes in the database

    8) remove the old files ( in case you copied them to the new location )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ..

    • This reply was modified 1 month, 2 weeks ago by  water490.
    • This reply was modified 1 month, 2 weeks ago by  water490.
    • This reply was modified 1 month, 2 weeks ago by  water490.
  • Hi.  Is someone able to help me with this?  Thank you

  • You need to physically move the files from the old drive to the new drive.

    SQL will not do this for you.

    So when you have done the ALTER ... MODIFY, then ALTER OFFLINE, here now move the files yourself from old location to new location, THEN after you have moved them manually ALTER ONLINE.

    Ensure you also give the service full control on the folder  "NT SERVICE\MSSQLSERVER"

  • Ant-Green wrote:

    You need to physically move the files from the old drive to the new drive. SQL will not do this for you.

    So when you have done the ALTER ... MODIFY, then ALTER OFFLINE, here now move the files yourself from old location to new location, THEN after you have moved them manually ALTER ONLINE.

    Ensure you also give the service full control on the folder  "NT SERVICE\MSSQLSERVER"

    Thank you so much!

  • More technically, you need permissions for whatever account SQL Server is running under.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ..

  • The query is this one:

    INSERT INTO DBO.RunTimeSSIS
    VALUES ('Start SSIS', getdate())

    I ran this in SSMS to see if there are any issues.  No issues at all.  It ran and updated the table.  Something strange is going on with SSIS.  Any help is much appreciated.

    Thank you

     

  • Check the windows folder security at the new location and double check the service account has the needed authority !

    double check which account your service is trying to use !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply