• Lasse Schioettz (5/27/2009)


    Hey Jonathan,

    Good article. A couple of minor things to mention, though. To detach the database you use "sp_detach_db" - not "sp_attach_db". It may also be worth noting that the physical_name you look up in the master db is the current file path and the FILENAME argument to the ALTER DATABASE MODIFY FILE is the path to the new location.

    Sincerely,

    Lasse

    Thanks, bad copy/paste when I was working on this. I'll submit a correction to Steve for that.

    The physical_name is not necessarily the current file path. It is the location that SQL will look for the file at startup. If you issue an ALTER DATABASE MODIFY FILE, and then pull the physical_name it will reflect the changes, even if the database hasn't been taken offline, and the files haven't been moved yet.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]