June 23, 2011 at 6:52 am
Hi ...
currently we have a database with 4 datafiles in 4 different filegroups or 4 different drives ...
these files are not the same size or within 10% of each other. The datafile in the Primary filegroup is the biggest which I understand. (was default inititially)
We want to move to a new machine and add 2 extra datafiles and would like to spread the data across all 6 datafiles on 6 different drives on 1 filegroup (not Primary).
How can we do this as we are having issues with the import and export wizard?
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
June 23, 2011 at 7:21 am
Your title indicates something different from what your post says. Are you dealing with multiple files in one filegroup or multiple filegroups?
EDIT: The reason I ask is that you can stripe data across multiple files in a single filegroup, but you can't reasonably stripe data between filegroups. I.E., a table rests solely on one filegroup and its data cannot be shared between multiple filegroups, so far as I know.
June 23, 2011 at 7:25 am
Oh, wait. I forgot about table partitioning. You could probably do that.
1) Backup the database, restore it to the new machine with each of the existing files on a different drive.
2) Add your two new files to the two other drives with the ALTER DATABASE statement.
3) Add your table partitions to the different drives.
June 23, 2011 at 7:41 am
from the 4 datafiles on 4 filegroups and 4 drives we would like to migrate to a new server
6 datafiles, 1 filegroup (defined filegroup) 6 drives
will not partition the tables
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
June 23, 2011 at 8:04 am
1) Backup the database, restore it to the new machine
2) Add 5 new files to your default filegroup, each pointing to a different drive.
3) Move the data from the old file/filegroups to the new files. This can be achieved by removing the tables' clustered indexes and recreating them on the new files. If you have heap tables, create a temporary clustered index on the new file location to move the data.
4) Delete old file/filegroups
Note: ALTER DATABASE ... MODIFY FILE can change directories for a file. If there is a way to change the file's filegroup using this command, I recommend it above my other instructions, but I don't see any way it actually changes the file's filegroup.
June 29, 2011 at 7:27 am
Brandie Tarvin (6/23/2011)
3) Move the data from the old file/filegroups to the new files. This can be achieved by removing the tables' clustered indexes and recreating them on the new files. If you have heap tables, create a temporary clustered index on the new file location to move the data.
found this ...
http://www.sqlservercentral.com/scripts/FileGroup/67723/
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply