Q: I’ve just taken over a SQL Server system and found that the previous owner installed all the databases on the C drive, including the 800GB production database, which has only a single data file. I’d like to move this database to a new location and create some more file groups for it. Can you suggest a safe methodology for doing so?
A: You’re absolutely right that having all the databases installed on the same drive, shared with the OS and the page file, is a recipe for poor performance, especially for a database that’s so large. There are a variety of methods for moving the database to a new location, but I’ll explain what I think is the most efficient and safest method.
First, you’d like to create some new file groups and move some of the tables to them. You can do that by creating the file groups and using the CREATE INDEX WITH (DROP_EXISTING=ON) syntax. This syntax rebuilds the index and lets you specify the new file group location of the index after it has been rebuilt, and you can even perform this operation online when using the Enterprise Edition. You can do this for the tables themselves (assuming they have clustered indexes) and the non-clustered indexes.
Once all the user tables and indexes have been moved out of the primary file group (with its single data file), you can reclaim the empty space by performing a DBCC SHRINKFILE operation on the data file. The system tables should already be at the logical start of the data file, and given that all the user tables have already been moved to other file groups, there’s no danger of causing index fragmentation on them because of the shrink operation. (For more information, see my blog post "Why you should not shrink your data files.")
Now you’re ready to move the primary data file to a new location. The safest procedure for doing so isn’t to detach/attach the database, and the most efficient procedure isn’t to backup and restore the database. Instead, the data file can be moved very efficiently and safely, but with some downtime involved. The following is the method for doing so:
1. Set the database offline using ALTER DATABASE paulsdb SET OFFLINE.
2. Copy the data file to the new location. Do not move the data file because this means there would only be a single copy of the file.
3. Copy the log file to the new location.
4. Inform SQL Server of the new location of the primary data file and the log file using ALTER DATABASE to specify the new location of the primary data file.
5. Set the database online using ALTER DATABASE paulsdb SET ONLINE.
This mechanism preserves the file in the old location so that if anything goes wrong, you have a fall-back strategy. By performing the index moves before the data file move, you limit the amount of data copying being performed. I would also perform a full database backup before the operations (for easy disaster recovery if anything goes wrong) as well as after the operations so that any disaster recovery doesn’t mean you have to perform the entire data migration again.
