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, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul 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