I have a database that is currently 1900GB and we normally use filegroups and smaller mount points (500GB or 1TB) for SAN performance purposes.
Is there a way to take this large mdf file and "split it into a new database with multiple filegroups?
My only other option (since this will be on VMware) is to utilize RDM's but I'd prefer not to go that route since we're also looking to utilize VMware's DR product, and RDM's are a real pain.
This depends on how many tables you have in the database. If you have only 1 table of huge size, you can create a filegroup with multiple files to this filegroup and move the data from primary to secondary filegroup (You an use create Index idx_name with Move syntax). If you have multiple tables and need to move the data to multiple files then move each table to a new file group/files independently.
you can create additional database files and force the SQL Server to move data from the primary database file to the secondary database files.
use SSMS to connect to the SQL Server, click on the database, display property page, select Files and than add additional data files using .ndf extensions specifying max file size less than the size of the drives.
After adding the additional files, click on the server and open the context menu and select Task -> Shrink > Data Files and shrink the larger primary database file (.mdf) to a size smaller than the destination drive.
Any way to split large .mdf files?
Posted - 11/09/2012 : 09:08:59
