| Downloads |
|---|
| 103412.zip |
SQL Server documentation describes two methods for moving database files. The first method involves detaching a database, moving the files, then re-attaching the database. The second method involves taking a database offline, running the ALTER DATABASE command to change file locations, moving the files, and bringing the database back online.
Both methods suffer from a major limitation: The database has to be offline. Another limitation is that for these methods to work, the DBA needs full access to the folders where the database files are kept. In large organizations, this often isn't the case, so the DBA doesn't have sufficient permissions to move the files. Finally, the detach/re-attach method carries with it additional issues, such as having to change the database owner and having to reset the database options to their defaults (e.g., ENABLE_BROKER).
A Different Method
To avoid all of these problems, I use a different method for moving database files. I create a new file using the ALTER DATABASE command, then move the data using the DBCC SHRINKFILE command with the EMPTYFILE option. Finally, I use another ALTER DATABASE command to remove the empty file. This migration operation is performed while the database is online.
To demonstrate this method, I created two scripts, which you can try in your own environment. To download these scripts, click the 103412.zip link near the top of the page. These scripts work on SQL Server 2008 and SQL Server 2005.
Here's how the scripts work: The first script, which Listing 1 shows, creates a database with two file groups: PRIMARY (which contains a data file named test_primary_dat) and SECONDARY(which contains a data file named test_secondary_dat). In the SECONDARY file group, a table is created and populated with some sample data.
|
Listing 1: Script That Creates a Database with a Table on a Secondary File Group |
 |
The second script, which Listing 2 shows, adds another data file named test_secondary_dat_NEW to the SECONDARY file group. Note that test_secondary_dat_NEW is created in the same folder in which test_secondary_dat was created to keep this example simple by removing the need for a second disk. In a real-life scenario, you would create the additional data file on a disk where there is more free space.
|
Listing 2: Script That Moves the Data and Removes the Old File |
 |
Next, the second script uses the DBCC SHRINKFILE command with the EMPTYFILE option to migrate the data from the old file (test_secondary_dat) to the new one (test_secondary_dat_NEW) as an online operation. The old, empty file is then removed.
As callout A in Listing 2 shows, the DBCC SHOWFILESTATS command is used to capture the size of the data files before and after the migration to see whether it was successful. This command's output includes one record for each physical data file. Each record contains several columns, one of which is UsedExtents. The value listed under the UsedExtents column represents the amount of space used by the data in the file. Note that the UsedExtents value includes system metadata, so even an empty file won't show a 0 for this value. (For more information about DBCC SHOWFILESTATS, see "Avoiding the Red Zone".)
FYI
My approach to moving database files does have a few limitations. First, it's much slower than the other two methods. However, from the database availability point of view, it's actually faster because there's a lot less downtime. Second, this method can't be used to move system objects. This limitation can be avoided if you follow Microsoft's recommended practice of keeping all your user objects in user file groups and leaving only system objects in the primary file group, as was done in this example. Third, the DBCC SHRINKFILE operation can cause index fragmentation, so you should perform index maintenance after the data migration is complete.
Learn more from "Moving the Log File."
