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:

datafilemove11

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:

datafilemove2

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

datafilemove31

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:

datafilemove4

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

datafilemove5

 

 

kick it on DotNetKicks.com

Advertisements
No comments yet

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

You are commenting using your Google+ 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: