How To: Moving Data Files

In this post let’s move a data file from its default location to another location. I have created a database I called “Test” for the demo. First let’s just check where the data file is currently stored:

The “Test” database needs to be put offline before we can move the data file. We do that by issuing the following ALTER DATABASE statement:

To actually move the data file, we issue another ALTER DATABASE statement:

After you issue that command, you should get the following message:

The file “TEST” has been modified in the system catalog. The new path will be used the next time the database is started.

Now, we can move the data file to the new directory we specified (Cut-and-Paste or Drag-and-Drop, your choice) . Let’s double check if we have successfully moved the data file to the new location; but, we need to put the TEST database back online first:

Let’s query the sys.master_files again to make sure we have our data file where we want it: