Move a large database with a single datafile to new database using multiple datafiles
We have a database that is nearly 1.8TB on a VMware environment. this is approaching the 2TB volume limit we can have on the VMware environment. We would like to move it to a new database server but this time stripe out the database onto several data files of 500 GB. we hav e tried the following 1. backup and restore, but the restore only gives option of mapping onto one datafile of the same size 2. Replication. we are using transactional replication but it is taking far too long. we are not sure if it will work because we cannot see the progress even with replication monitor (stuck at 4 % for the snapshot agent) any ideas? Old database is on SQL Server 2008 R2, we are trying to get it to SQL server 2014 regards Francis
I recommend the following steps: 1. Create 1 or more new filegroups in the existing database. 2. Create 1 or more files per filegroup. 3. Move some existing tables to the new filegroup(s). 4. Shrink the primary filegroup after tables are moved. 5. Backup the database and then restore in the new instance. As an added bonus, I've recently started putting *all* tables in non-primary filegroups so that I can do a partial backup/restore to just get the structure without data. That has proved very useful when DBAs make changes directly on a production system, and I need to make sure those changes are also in our dev environment and database projects.
An example: 1. Create a new .vmdk/disk for your server. 2. Create a MSSQL\Data folder and set apropriate permissions on this new disk. 3. Create a new filegroup, set this filegroup as the default filegroup for newly created objects. 4. Create a new datafile (.ndf) in the new filegroup, put in on the new disk. 5. Find a table that you want to move to the new disk/datafile 6. Recreate your index and put it in the new filegroup, the table will be moved to the new disk. CREATE CLUSTERED INDEX ClusteredIndexName ON dbo.YourTable(clusterkeyfields) WITH DROP_EXISTING ON [new filegroup] OR if unique: CREATE UNIQUE CLUSTERED INDEX ClusteredIndexName ON dbo.YourTable(clusterkeyfields) WITH DROP_EXISTING ON [new filegroup] This way you have moved a table to the new disk to avoid the size limitations. Another options is to upgrade vmware to 5.5 for larger than 2TB vmdk support: [Support for virtual machine disks larger than 2 TB in VMware ESXi 5.5] :
I will recommend same as Raadee. Usually index occupies around 40% of your data file size, (though it depends on your indexing policy). if you move your indexes to separate filegroup, you will definitely shrinking the data file size. And them also move some big tables to a different file group. Remember to shrink your files. and also create new files for new data.