MDF and LDF files are protected and cannot be moved while the database is online. You can see this described in more detail here. Just copy the files over using your favorite method (Click 'n Drag, XCopy, Copy-Item, Robocopy)īring the database online ALTER DATABASE foo SET ONLINE (I use WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions) ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE Changing this path does not take effect immediately, but will be used the next time the database starts up. Note, you do not need to declare the old location in this command. ,filename='X:\NewDBFile\DB_Data1.mdf') -Filename is new location Detach/Attach was already described, but it is not nearly this complex.Ĭhange the file locations with an ALTER DATABASE command: USE master -do this all from the master The process to move them is fairly simple. This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application. You don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |