January 11, 2008 at 7:39 am
We have an old sql server 2000 database that we are moving to another server. The database was set up with only one data file and one log file, yet it is a fairly large db. We first tried creating the database with several data files and several log files, but of course when we did the database restore it removed them and went back to using just the one file situation. Is there a way that we can take an existing database with only one data file and one log file and have it go to using several files instead; or is there a way that when we restore the old db to the new db (which is created with several files) for the old db to split its data between the files and not revert back to the one file destination?
Thanks,
Adam
January 11, 2008 at 7:45 am
No, the engine has to know where to put the pages.
Restore as one file, then create new filegroups and files and move the data (by recreating clustered indexes) in the new locations.
January 11, 2008 at 8:09 am
Sorry I am kind of new to recreating indexes. What is the fastest way to do that for an entire database with several tables (We are in a kind of time crunch). Will DBCC DBREINDEX work???
Thanks for the help,
Adam
January 11, 2008 at 8:37 am
Here is an alternative.
Create your new database on your target server, then, migrate your data using DTS.
Hopefully, it will be helpful.
January 11, 2008 at 1:57 pm
The problem with a DTS to copy all of the data is that we are in a time crunch and there are hundreds of tables that we would have to create the transformation tasks for, isn't there a fast way to recreate the indexes like Steve Jones said in the first reply to the post?
Thanks for suggestions,
Adam
January 13, 2008 at 8:50 pm
You could script out the indexes, save this. Then do a search and replace for the indexes, choosing the new filegroup as the new location.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply