Script move of DB files from one location to another

  • Comments posted to this topic are about the item Script move of DB files from one location to another

  • I always advise NOT detaching the original database because you might not be able to attach it again if there's a problem with the database.

    Set the database offline, copy the files to the new location, attach them, and only then detach the original database.

    Paul Randal
    CEO, Check out SQLskills online training! Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Microsoft introduced the ability to modify user database file paths for a very good reason, so you don't have to detach databases.

    Whilst it may seem a good idea I'd prefer manual control of the file moves when performing this sort of task. What is useful is the ability to script out all the database t-sql commands, can save a lot of time.


    "Ya can't make an omelette without breaking just a few eggs" 😉

  • The script has a minor problem in that if a database has multiple data files (.ndf files) then there is a backup command generated for the database once per file. The same issues exists for the number of generated attach statements.

    - -

    HTH -- Mark D Powell --

  • Thanks for the script.

Viewing 5 posts - 1 through 4 (of 4 total)

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