March 23, 2015 at 3:21 pm
What is the best method to restore a DBTest1 (with one .mdf and one .ldf) into DBTest2 (with one .mdf, multiple .ndf data files and with 4 filegroups associated with specific data files). I do not see how the one .mdf file (in DBTest1) can be separated into the other 4 filegroups (in DBTest2). This does not sounds like it is possible with Backup DBTest1/Restore to DBTEST2 or (Detach/Attach) because the underlying filegroup and file structure is different.
What method should be used to get the data and structure from DBTest1 (includes 1100 Tables and 550 GBs of Data) into DBTest2 (with 4 filegroups)? Is the following possible:
1) First, in DBTest2, execute a script to create tables/indexes on appropriate filegroups.
2) In DBTest2, use scripts to pull data from DBTest1 into DBTest2, for example INSERT INTO DBTest2.dbo.tables with SELECT FROM DBTest1.dbo.tables OR use SELECT/INTO DBTest2.dbo.tables FROM DBTest1.dbo.tables.
Or, is it possible to use the BULK INSERT or BULK COPY Options? Export/Import Wizard?
Does the Create Index step needs to be done after the data is loaded into DBTest2?
Just looking for some practical guidance, suggestions, and recommendations. Thanks.
March 24, 2015 at 4:11 am
You will need to do the backup/restore and then create the additional filegoups and migrate tables / indexes into the appropriate filegroups by rebuilding the indexes. https://msdn.microsoft.com/en-us/library/ms175905.aspx
You can also create the new database with the appropriate filegroups and objects and migrate the data using import/export or ssis. Beware as this can get messy if there are dependancies like foreign keys. You may need to carefully sequence the tables being populated or drop the constraints and recreate them after the import.
March 24, 2015 at 4:13 am
You can't do it with a backup/restore. A restore recreates the database as it was at the time of backup, identical files. Restore the DB and then add the new filegroups and move the data around.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2015 at 4:14 am
The easiest way is going to be to run the RESTORE using WITH MOVE. But, that means the file layout has to be the same, even if the locations are different. Meaning, the same number of files. They can all be on different drives and have different names, but you have to have the same number.
If you use any other method for moving the data over, SSIS, BULK INSERT, or just INSERT, you will need to run index maintenance and statistics maintenance at the end of the move process. That will ensure your indexes are defragmented and your statistics are up to date.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply