Restore a database with 2 datafiles into one datafile - possible?

Hi,
I do have a large Axapta database (114GB) with following file conent:
Datafiles:
d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
Filegroup
f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
Filegroup
Logfile:
e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
transactionlog file
Unfortunately does this application perform not well with this file
config (known problem within the Axapta enviroment)
So my issue is now following:
How can I move this database to a new server, and create only one
datafile?
I would like this new file config:
d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
e:\....\log\axapta_log.ldf
Can anyone explain to me if it is possible, and then... how?
Thanks in advance...
Regards
Torgeir
A restored database is exactly like the original so you can't change the
file configuration during the restore. If your plan is to move the database
using BACKP/RESTORE, you'll need to change the file configuration before the
backup or after the restore. To do this, expand the data01 file to the
desired size and then migrate data from data02 using DBCC
SHRINKFILE...EMPTYFILE. You can then remove the data02 file using ALTER
DATABASE.
Although it is not enforced, the file extension for secondary data files
should be 'ndf' rather than 'mdf'. The 'mdf' extension should be used only
for the primary data file in order to avoid later confusion.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135896641.887437.220980@.g44g2000cwa.googlegr oups.com...
> Hi,
> I do have a large Axapta database (114GB) with following file conent:
> Datafiles:
> d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
> Filegroup
> f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
> Filegroup
> Logfile:
> e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
> transactionlog file
> Unfortunately does this application perform not well with this file
> config (known problem within the Axapta enviroment)
> So my issue is now following:
> How can I move this database to a new server, and create only one
> datafile?
> I would like this new file config:
> d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
> e:\....\log\axapta_log.ldf
> Can anyone explain to me if it is possible, and then... how?
> Thanks in advance...
> Regards
> Torgeir
>
|||Hi, and thank you for your quick response!
I assume I also can do this operation after I have moved the database
to a new server.
The existing server do not have enought diskspace.
Regards
Torgeir
|||Yes, you can consolidate the files after moving to the new server.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135898358.019743.33740@.f14g2000cwb.googlegro ups.com...
> Hi, and thank you for your quick response!
> I assume I also can do this operation after I have moved the database
> to a new server.
> The existing server do not have enought diskspace.
> Regards
> Torgeir
>