question

Makokha avatar image
Makokha asked

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
replicationdatafiles
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Alternatively, if you already have the structure in the destination instance, you could create an SSIS package to move the data.
0 Likes 0 ·
raadee avatar image
raadee answered
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][1] [1]: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2058287
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sql_hunter avatar image
sql_hunter answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.