Skip to content

How To: Moving Data Files

November 6, 2008

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:




kick it on

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

  • Archives

  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: