joining multiple mdfs

Hi all,
I have a database that I'll be moving to a new machine. The existing setup
has the data in 3 separate mdf files. The new machine has more than enough
space to accommodate the 3 files in the same location, as well as enough
room
to grow for the foreseeable future.
I've done a test load of the existing data onto the new server from the .bak
dump file. Since the data was in 3 files I had to restore it into 3 files. I
was wondering if it's possible (and how I would go about it) to join the
separate mdfs into one large one.
Thanks in advance for any replies.
Ricknever done this but I think you can do this with ALTER DATABASE. Check it
out in BOL or http://support.microsoft.com/?kbid=814576
"Rick" <reddinger@.serif.com> wrote in message
news:%237o9PqbFEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a database that I'll be moving to a new machine. The existing setup
> has the data in 3 separate mdf files. The new machine has more than enough
> space to accommodate the 3 files in the same location, as well as enough
> room
> to grow for the foreseeable future.
> I've done a test load of the existing data onto the new server from the
.bak
> dump file. Since the data was in 3 files I had to restore it into 3 files.
I
> was wondering if it's possible (and how I would go about it) to join the
> separate mdfs into one large one.
> Thanks in advance for any replies.
> Rick
>
>|||Thanks, that worked. Come to realize you can also do this through the GUI in
the "Shrink files" dialog, saves some typing
Thanks again.
"ME" <mail@.moon.net> wrote in message
news:OHpXEAcFEHA.2408@.TK2MSFTNGP10.phx.gbl...
> never done this but I think you can do this with ALTER DATABASE. Check it
> out in BOL or http://support.microsoft.com/?kbid=814576
> "Rick" <reddinger@.serif.com> wrote in message
> news:%237o9PqbFEHA.624@.TK2MSFTNGP10.phx.gbl...
setup
enough
> .bak
files.
> I
the
>

joining multiple mdfs

Hi all,
I have a database that I'll be moving to a new machine. The existing setup
has the data in 3 separate mdf files. The new machine has more than enough
space to accommodate the 3 files in the same location, as well as enough
room
to grow for the foreseeable future.
I've done a test load of the existing data onto the new server from the .bak
dump file. Since the data was in 3 files I had to restore it into 3 files. I
was wondering if it's possible (and how I would go about it) to join the
separate mdfs into one large one.
Thanks in advance for any replies.
RickOne possible solution is to restore the database on to the new server and
then use DTS transfer the objects to a new database.
There is not a way to combine the mdf files.
Rand
This posting is provided "as is" with no warranties and confers no rights.

join many MDF into one

Hi,
I have a MSDE2000 installation.
There I have 120 MDF files \ databases with alltogether 10GB.
All databases have an identical structure (same tables etc.)
Is there a way to join all databases into one big database?
I would then attach this big database to an SQL2000 Server and run some
queries against it.
thank youSteffen
Are those tables which have the same stucture also contain the data?
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a MSDE2000 installation.
> There I have 120 MDF files \ databases with alltogether 10GB.
> All databases have an identical structure (same tables etc.)
> Is there a way to join all databases into one big database?
> I would then attach this big database to an SQL2000 Server and run some
> queries against it.
> thank you
>|||"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a MSDE2000 installation.
> There I have 120 MDF files \ databases with alltogether 10GB.
> All databases have an identical structure (same tables etc.)
> Is there a way to join all databases into one big database?
> I would then attach this big database to an SQL2000 Server and run some
> queries against it.
> thank you
>
If you mean like detach them and attach them no.
Unfortunately you'll probably have to script together something to move the
data.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi Uri,
Yes, these tables also contain the data.
Actually the structure of all the 120 databases is 100% identical
These databases contain logfile-data from ISA-Server 2004 - one new database
per day is created...
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eTC3OBdVHHA.4796@.TK2MSFTNGP05.phx.gbl...
> Steffen
> Are those tables which have the same stucture also contain the data?
>
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>|||I was hoping that there is a way to bulk-export the data of all 120
databases to a textfile and then import it into a new database... has
MSDE2000 some command for bulk export to textfile ? (or any other format?)
thx
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
>
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> If you mean like detach them and attach them no.
> Unfortunately you'll probably have to script together something to move
> the data.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||You're on the right track. I would write a VB.NET application that
programmatically used SqlBulkCopy to import the data into the common
database. I expect it would take a long afternoon to write.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>I was hoping that there is a way to bulk-export the data of all 120
>databases to a textfile and then import it into a new database... has
>MSDE2000 some command for bulk export to textfile ? (or any other format?)
> thx
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
> Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
>|||"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>I was hoping that there is a way to bulk-export the data of all 120
>databases to a textfile and then import it into a new database... has
>MSDE2000 some command for bulk export to textfile ? (or any other format?)
>
Look into BCP

> thx
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||DTS is the way to go.
--DatabaseAdmins.com
Remote DBA Services
"Greg D. Moore (Strider)" wrote:

> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
> Look into BCP
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||yeah it's called psuedodynamic sql
select 'insert into Destination.Dbo.Mytable Select * FROM ' + name +
'.dbo.Source'
from master.dbo.sysdatabases
if you've got to get a different table name in each database; then do a join
to sysobjects
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
> I was hoping that there is a way to bulk-export the data of all 120
> databases to a textfile and then import it into a new database... has
> MSDE2000 some command for bulk export to textfile ? (or any other format?)
> thx
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
> Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
>

join many MDF into one

Hi,
I have a MSDE2000 installation.
There I have 120 MDF files \ databases with alltogether 10GB.
All databases have an identical structure (same tables etc.)
Is there a way to join all databases into one big database?
I would then attach this big database to an SQL2000 Server and run some
queries against it.
thank youSteffen
Are those tables which have the same stucture also contain the data?
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a MSDE2000 installation.
> There I have 120 MDF files \ databases with alltogether 10GB.
> All databases have an identical structure (same tables etc.)
> Is there a way to join all databases into one big database?
> I would then attach this big database to an SQL2000 Server and run some
> queries against it.
> thank you
>|||"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a MSDE2000 installation.
> There I have 120 MDF files \ databases with alltogether 10GB.
> All databases have an identical structure (same tables etc.)
> Is there a way to join all databases into one big database?
> I would then attach this big database to an SQL2000 Server and run some
> queries against it.
> thank you
>
If you mean like detach them and attach them no.
Unfortunately you'll probably have to script together something to move the
data.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi Uri,
Yes, these tables also contain the data.
Actually the structure of all the 120 databases is 100% identical
These databases contain logfile-data from ISA-Server 2004 - one new database
per day is created...
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eTC3OBdVHHA.4796@.TK2MSFTNGP05.phx.gbl...
> Steffen
> Are those tables which have the same stucture also contain the data?
>
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a MSDE2000 installation.
>> There I have 120 MDF files \ databases with alltogether 10GB.
>> All databases have an identical structure (same tables etc.)
>> Is there a way to join all databases into one big database?
>> I would then attach this big database to an SQL2000 Server and run some
>> queries against it.
>> thank you
>>
>|||I was hoping that there is a way to bulk-export the data of all 120
databases to a textfile and then import it into a new database... has
MSDE2000 some command for bulk export to textfile ? (or any other format?)
thx
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
>
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a MSDE2000 installation.
>> There I have 120 MDF files \ databases with alltogether 10GB.
>> All databases have an identical structure (same tables etc.)
>> Is there a way to join all databases into one big database?
>> I would then attach this big database to an SQL2000 Server and run some
>> queries against it.
>> thank you
>>
> If you mean like detach them and attach them no.
> Unfortunately you'll probably have to script together something to move
> the data.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||You're on the right track. I would write a VB.NET application that
programmatically used SqlBulkCopy to import the data into the common
database. I expect it would take a long afternoon to write.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>I was hoping that there is a way to bulk-export the data of all 120
>databases to a textfile and then import it into a new database... has
>MSDE2000 some command for bulk export to textfile ? (or any other format?)
> thx
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
> Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
>>
>> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
>> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a MSDE2000 installation.
>> There I have 120 MDF files \ databases with alltogether 10GB.
>> All databases have an identical structure (same tables etc.)
>> Is there a way to join all databases into one big database?
>> I would then attach this big database to an SQL2000 Server and run some
>> queries against it.
>> thank you
>>
>> If you mean like detach them and attach them no.
>> Unfortunately you'll probably have to script together something to move
>> the data.
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting
>> sql (at) greenms.com http://www.greenms.com
>|||"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>I was hoping that there is a way to bulk-export the data of all 120
>databases to a textfile and then import it into a new database... has
>MSDE2000 some command for bulk export to textfile ? (or any other format?)
>
Look into BCP
> thx
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||DTS is the way to go.
--DatabaseAdmins.com
Remote DBA Services
"Greg D. Moore (Strider)" wrote:
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
> >I was hoping that there is a way to bulk-export the data of all 120
> >databases to a textfile and then import it into a new database... has
> >MSDE2000 some command for bulk export to textfile ? (or any other format?)
> >
> Look into BCP
>
> > thx
> >
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||yeah it's called psuedodynamic sql
select 'insert into Destination.Dbo.Mytable Select * FROM ' + name +
'.dbo.Source'
from master.dbo.sysdatabases
if you've got to get a different table name in each database; then do a join
to sysobjects
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
> I was hoping that there is a way to bulk-export the data of all 120
> databases to a textfile and then import it into a new database... has
> MSDE2000 some command for bulk export to textfile ? (or any other format?)
> thx
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
> Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
> >
> >
> > "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> > news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >>
> >> I have a MSDE2000 installation.
> >> There I have 120 MDF files \ databases with alltogether 10GB.
> >> All databases have an identical structure (same tables etc.)
> >>
> >> Is there a way to join all databases into one big database?
> >> I would then attach this big database to an SQL2000 Server and run some
> >> queries against it.
> >>
> >> thank you
> >>
> >>
> >
> > If you mean like detach them and attach them no.
> >
> > Unfortunately you'll probably have to script together something to move
> > the data.
> >
> >
> >
> > --
> > Greg Moore
> > SQL Server DBA Consulting
> > sql (at) greenms.com http://www.greenms.com
> >
>