A Safe Method for Moving a Database to a New Location

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.

Discuss this Blog Entry 5

on Nov 4, 2010
You state that "the safest procedure for this is not to detach/attach the database". Can you expand on why that would not be safe to a) detach the database, b) copy (not move) the files to the new location & c) re-attach them?
on Nov 4, 2010
I agree 100% getting the database off the same drive as the operating system and page file is critical. However, I have a question about creating additional file groups. If one is using RAID 5 or RAID 10 for the data files, isn't adding a new file group on the same RAID drives sort of limiting. I mean you really gain nothing since in essence the new file group is on the same drives and the original data files.
Is this correct or have I been reading the wrong magazines again?
Thanks
Gary


on Nov 2, 2010
As I understand it this method will NOT move LOB data. The LOB data (image,text,etc) will stay in the original file group when you do the CREATE INDEX … WITH (DROP_EXISTING=ON). Is there any good way to move the LOB data to a new Filegroup?

Please correct me if I'm wrong.

thanks
Mike




on Nov 2, 2010
Yes - unfortunately there's no good way to move LOB data except extracting out into a new table. I wish this would be fixed but I'm not holding my breath.
on Nov 4, 2010
@Kevin I don't like that approach because if the database is damaged in some way, it may be very hard to get it attached again. Following my procedure doesn't go through the attach process so even if the database is damaged, it will still be attached.

@Gary I'm afraid you've been reading the wrong magazeines again :-) Take a look at the benchmarking blog post where I show (for an example workload) that having multiple data files on the same RAID-10 array can lead to a perf gain: http://www.sqlskills.com/BLOGS/PAUL/post/Benchmarking-do-multiple-data-files-make-a-difference.aspx

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×