Restore DB to diferent data file struct

Hi,
I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
with 90GB each.
The thing is that the server is no moreâ?¦ diedâ?¦. Kaputâ?¦
I have backups of the database and I have a spare server with 6 drive
letters with 80GB of free space eatch (aprox).
Considering that I canâ't touch the operating system to change the drive
letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
the sql database to more data files than the original database to spread the
database over the avaible drive letters.
The problem is that when I try to restore, the sql restore utility doesâ't
allow to add new devices different from the original db file struct.
Can anyone help me on this one?oh yes ...
i'm using sql 2000 sp3a
same version as on the previous version and same collation.|||Since it sounds like a bit of an emergency, how about if you use a disk
utility and create compressed drives in the available space so the 90 GB
files will fit. Then restore the original file structure in the compressed
drives, partition them into smaller files, move them around, uncompress the
drives and go from there.
Sounds way out, but it might work. Somebody else here might have a far
better way of doing it.
Bob Castleman
DBA Poseur
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:EE908619-C160-4E11-AF90-6264D85416EE@.microsoft.com...
> oh yes ...
> i'm using sql 2000 sp3a
> same version as on the previous version and same collation.
>|||SQL Server need the same amount of database files, each having the same logical file and at least
the same size, as the database had then you performed the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> Hi,
> I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> with 90GB each.
> The thing is that the server is no more. died.. Kaput.
> I have backups of the database and I have a spare server with 6 drive
> letters with 80GB of free space eatch (aprox).
> Considering that I can't touch the operating system to change the drive
> letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
> the sql database to more data files than the original database to spread the
> database over the avaible drive letters.
> The problem is that when I try to restore, the sql restore utility does't
> allow to add new devices different from the original db file struct.
> Can anyone help me on this one?
>|||tks Tibor, that's true, and i new that, i was just hopping that someone
already managed to go around that.
But still, i realy don't see wy the hell one can't restore a backup to a
diferent data file struct, since what we nead is the data...
"Tibor Karaszi" wrote:
> SQL Server need the same amount of database files, each having the same logical file and at least
> the same size, as the database had then you performed the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> > Hi,
> > I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> > with 90GB each.
> > The thing is that the server is no more. died.. Kaput.
> > I have backups of the database and I have a spare server with 6 drive
> > letters with 80GB of free space eatch (aprox).
> > Considering that I can't touch the operating system to change the drive
> > letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
> > the sql database to more data files than the original database to spread the
> > database over the avaible drive letters.
> > The problem is that when I try to restore, the sql restore utility does't
> > allow to add new devices different from the original db file struct.
> > Can anyone help me on this one?
> >
>
>|||> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
Backup just copies all the used pages to the backup media. As they are, including pointers to other
pages, allocation pages etc.. This is why restore need to put everything in the right place.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:1C8895ED-0205-42FE-99B5-C2A4AB4E5137@.microsoft.com...
> tks Tibor, that's true, and i new that, i was just hopping that someone
> already managed to go around that.
> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
>
> "Tibor Karaszi" wrote:
>> SQL Server need the same amount of database files, each having the same logical file and at least
>> the same size, as the database had then you performed the backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
>> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
>> > Hi,
>> > I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
>> > with 90GB each.
>> > The thing is that the server is no more. died.. Kaput.
>> > I have backups of the database and I have a spare server with 6 drive
>> > letters with 80GB of free space eatch (aprox).
>> > Considering that I can't touch the operating system to change the drive
>> > letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
>> > the sql database to more data files than the original database to spread the
>> > database over the avaible drive letters.
>> > The problem is that when I try to restore, the sql restore utility does't
>> > allow to add new devices different from the original db file struct.
>> > Can anyone help me on this one?
>> >
>>

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
>

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
TorgeirA 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.googlegroups.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.googlegroups.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
>

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
TorgeirA 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.googlegroups.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.googlegroups.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
>