Copying database from one instance to another

I am very very new at SQL and MSDE, however I have the following question,
hopefully an easy one.
We have been using an instance of MSDE on a slower smaller PC and recently
installed the SQL Server 2005 Express Edition Beta 2. We are not yet fully
going to this Beta yet.
How do I copy databases including tables with data over to this new
instance?
Thanks for the information.
Brad
"Brad" <ballison@.ukcdogs.com> wrote in message
news:uLpPF79pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> I am very very new at SQL and MSDE, however I have the following question,
> hopefully an easy one.
> We have been using an instance of MSDE on a slower smaller PC and recently
> installed the SQL Server 2005 Express Edition Beta 2. We are not yet
fully
> going to this Beta yet.
> How do I copy databases including tables with data over to this new
> instance?
I'd recommend running a BACKUP of your database from MSDE, then RESTORE to
your copy of SQL Server 2005.
Steve
|||I recommend using sp_detach_db to disconnect the source database from the
Master database. This permits you to copy the MDF and LDF files to another
system. Once copied you can reattach with SQL Enterprise Manager or
sp_attach_db. In SQL Server Express (SS 2005) you can also use ADO 2.0 to
open an MDF file directly which automatically attaches the database (and
log) files unless they are already in Master. Otherwise you can use
sp_attach_db on the target system.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:u6gBWt%23pEHA.192@.tk2msftngp13.phx.gbl...
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:uLpPF79pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> fully
> I'd recommend running a BACKUP of your database from MSDE, then RESTORE to
> your copy of SQL Server 2005.
> Steve
>
|||That will certainly work, however one of the advantages of BACKUP/RESTORE is
only the data is copied from device to device. Database mdf and ldf files
will contain "empty" space.
Steve
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:%23A0beC$pEHA.2236@.TK2MSFTNGP09.phx.gbl...
> I recommend using sp_detach_db to disconnect the source database from the
> Master database. This permits you to copy the MDF and LDF files to another
> system. Once copied you can reattach with SQL Enterprise Manager or
> sp_attach_db. In SQL Server Express (SS 2005) you can also use ADO 2.0 to
> open an MDF file directly which automatically attaches the database (and
> log) files unless they are already in Master. Otherwise you can use
> sp_attach_db on the target system.
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> 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.[vbcol=seagreen]
> __________________________________
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:u6gBWt%23pEHA.192@.tk2msftngp13.phx.gbl...
to
>
|||Steve,
Being new to MSDE is this something that would be done from the command
line? What would the syntax be?
Thanks for the help.
Brad
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:u6gBWt%23pEHA.192@.tk2msftngp13.phx.gbl...
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:uLpPF79pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> fully
> I'd recommend running a BACKUP of your database from MSDE, then RESTORE to
> your copy of SQL Server 2005.
> Steve
>
|||Brad,
Yes, you can use the OSQL utility to do this, from a command line prompt
type:
osql
to get the command parameters. I find it easiest to create a batch script
with the sql syntax.
The syntax (and examples) for the other 2 commands: BACKUP and RESTORE you
can get from SQL Server BOL (as there are many options depending on what you
need),.
If you have not downloaded BOL, it's free and an excellent resource:
http://www.microsoft.com/sql/techinf...2000/books.asp
Steve
PS if you run into any issues, please post the sy
"Brad" <ballison@.ukcdogs.com> wrote in message
news:eV%23k3BtqEHA.2612@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Steve,
> Being new to MSDE is this something that would be done from the command
> line? What would the syntax be?
> Thanks for the help.
> Brad
>
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:u6gBWt%23pEHA.192@.tk2msftngp13.phx.gbl...
to
>
|||hi Brad,
"Brad" <ballison@.ukcdogs.com> ha scritto nel messaggio
news:eV%23k3BtqEHA.2612@.TK2MSFTNGP15.phx.gbl
> Steve,
> Being new to MSDE is this something that would be done from the
> command line? What would the syntax be?
the syntax can be found at
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
but this can not be done directly by cmd... you have to resort on tools
able to connect to SQL Server, as, for SQL Server 2005, SQLCMD.exe, a
command line tool similar to OSQL.exe (documented in
http://msdn.microsoft.com/library/de..._osql_1wxl.asp)
please discuss SQL Server 2005 related problems/feauures int the public beta
newsgroups, that can be found at
http://communities.microsoft.com/new...r2005&slcid=us
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

copying data oracle2sql

Hi,
I discovered that copying data from oracle using "openquery" with linked
server is sevaral time slower that using "dtsexecui" (dts import/export
wzard). I used the same query for both methods. Does anybode has expirience
solving slowly openquery behaviour? My linked server has "allow in process"
enabled.
Seems dts import/export wizzard doesn't use rollback segment? May I could
rewrite my query?
select *
from openquery (sapr3_prd, '
select MATNR, WERKS, LGORT, LFGJA, LFMON, LABST
, UMLME ,INSME, EINME, SPEME, RETME, VKLAB
, VKUML
from sapr3.mardh
where mandt = ''600''
')
Ramunasopenquery is not designed to move large amount of data.
the import/export wizard use the bulk insert feature of SQL Server which
result on the highest performance you can for a loading.
"Seems dts import/export wizzard doesn't use rollback segment?"
what do you mean by this?
you can import data in 1 transaction or multiple transactions
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174662643.833343@.loger.vpmarket.int...
> Hi,
> I discovered that copying data from oracle using "openquery" with linked
> server is sevaral time slower that using "dtsexecui" (dts import/export
> wzard). I used the same query for both methods. Does anybode has
> expirience
> solving slowly openquery behaviour? My linked server has "allow in
> process"
> enabled.
> Seems dts import/export wizzard doesn't use rollback segment? May I could
> rewrite my query?
> select *
> from openquery (sapr3_prd, '
> select MATNR, WERKS, LGORT, LFGJA, LFMON, LABST
> , UMLME ,INSME, EINME, SPEME, RETME, VKLAB
> , VKUML
> from sapr3.mardh
> where mandt = ''600''
> ')
> Ramunas
>|||you think the bottleneck is the insert part into sql, not select from
oracle? i think different - sql server is able to select data from oracle in
different way.
the openquery from oracle always fails with message: "ora-01555 snapshot too
old: rollback segment number XX with name YYY to small". my idea is to use
fresh table and load data in the same transaction like:
begin tran
truncate table MyTable
insert into MyTable
select * from openquery (sapr3_prd, '....')
if @.@.error <> 0
rollback tran
else
commit tran
is it possible to achieve this with dtsexecui? it's strange but dtsexecui
never get this error, so my idea this is because import/export wizard
doesn't use rollback segment in oracle. you said that possible to import
data in one transaction, what you mean? because when I use dtsexecui, and
try to load 100 mln of rows and if import fails after 80 mln, so these 80
mln of rows will be added to the table.
"Jeje" <willgart@.hotmail.com> wrote in message
news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> openquery is not designed to move large amount of data.
> the import/export wizard use the bulk insert feature of SQL Server which
> result on the highest performance you can for a loading.
> "Seems dts import/export wizzard doesn't use rollback segment?"
> what do you mean by this?
> you can import data in 1 transaction or multiple transactions
> "Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
> news:1174662643.833343@.loger.vpmarket.int...
could[vbcol=seagreen]|||no, the insert into sql server is not the problem.
the problem is the loading part from the Oracle driver, and the overhead of
the openquery syntax.
using a bulk insert command through an SSIS package will provide far better
performance.
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174888638.520904@.loger.vpmarket.int...
> you think the bottleneck is the insert part into sql, not select from
> oracle? i think different - sql server is able to select data from oracle
> in
> different way.
> the openquery from oracle always fails with message: "ora-01555 snapshot
> too
> old: rollback segment number XX with name YYY to small". my idea is to use
> fresh table and load data in the same transaction like:
> begin tran
> truncate table MyTable
> insert into MyTable
> select * from openquery (sapr3_prd, '....')
> if @.@.error <> 0
> rollback tran
> else
> commit tran
> is it possible to achieve this with dtsexecui? it's strange but dtsexecui
> never get this error, so my idea this is because import/export wizard
> doesn't use rollback segment in oracle. you said that possible to import
> data in one transaction, what you mean? because when I use dtsexecui, and
> try to load 100 mln of rows and if import fails after 80 mln, so these 80
> mln of rows will be added to the table.
>
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...
> could
>

copying data oracle2sql

Hi,
I discovered that copying data from oracle using "openquery" with linked
server is sevaral time slower that using "dtsexecui" (dts import/export
wzard). I used the same query for both methods. Does anybode has expirience
solving slowly openquery behaviour? My linked server has "allow in process"
enabled.
Seems dts import/export wizzard doesn't use rollback segment? May I could
rewrite my query?
select *
from openquery (sapr3_prd, '
select MATNR, WERKS, LGORT, LFGJA, LFMON, LABST
, UMLME ,INSME, EINME, SPEME, RETME, VKLAB
, VKUML
from sapr3.mardh
where mandt = ''600''
')
Ramunas
openquery is not designed to move large amount of data.
the import/export wizard use the bulk insert feature of SQL Server which
result on the highest performance you can for a loading.
"Seems dts import/export wizzard doesn't use rollback segment?"
what do you mean by this?
you can import data in 1 transaction or multiple transactions
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174662643.833343@.loger.vpmarket.int...
> Hi,
> I discovered that copying data from oracle using "openquery" with linked
> server is sevaral time slower that using "dtsexecui" (dts import/export
> wzard). I used the same query for both methods. Does anybode has
> expirience
> solving slowly openquery behaviour? My linked server has "allow in
> process"
> enabled.
> Seems dts import/export wizzard doesn't use rollback segment? May I could
> rewrite my query?
> select *
> from openquery (sapr3_prd, '
> select MATNR, WERKS, LGORT, LFGJA, LFMON, LABST
> , UMLME ,INSME, EINME, SPEME, RETME, VKLAB
> , VKUML
> from sapr3.mardh
> where mandt = ''600''
> ')
> Ramunas
>
|||you think the bottleneck is the insert part into sql, not select from
oracle? i think different - sql server is able to select data from oracle in
different way.
the openquery from oracle always fails with message: "ora-01555 snapshot too
old: rollback segment number XX with name YYY to small". my idea is to use
fresh table and load data in the same transaction like:
begin tran
truncate table MyTable
insert into MyTable
select * from openquery (sapr3_prd, '....')
if @.@.error <> 0
rollback tran
else
commit tran
is it possible to achieve this with dtsexecui? it's strange but dtsexecui
never get this error, so my idea this is because import/export wizard
doesn't use rollback segment in oracle. you said that possible to import
data in one transaction, what you mean? because when I use dtsexecui, and
try to load 100 mln of rows and if import fails after 80 mln, so these 80
mln of rows will be added to the table.
"Jeje" <willgart@.hotmail.com> wrote in message
news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> openquery is not designed to move large amount of data.
> the import/export wizard use the bulk insert feature of SQL Server which
> result on the highest performance you can for a loading.
> "Seems dts import/export wizzard doesn't use rollback segment?"
> what do you mean by this?
> you can import data in 1 transaction or multiple transactions
> "Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
> news:1174662643.833343@.loger.vpmarket.int...
could[vbcol=seagreen]
|||no, the insert into sql server is not the problem.
the problem is the loading part from the Oracle driver, and the overhead of
the openquery syntax.
using a bulk insert command through an SSIS package will provide far better
performance.
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174888638.520904@.loger.vpmarket.int...
> you think the bottleneck is the insert part into sql, not select from
> oracle? i think different - sql server is able to select data from oracle
> in
> different way.
> the openquery from oracle always fails with message: "ora-01555 snapshot
> too
> old: rollback segment number XX with name YYY to small". my idea is to use
> fresh table and load data in the same transaction like:
> begin tran
> truncate table MyTable
> insert into MyTable
> select * from openquery (sapr3_prd, '....')
> if @.@.error <> 0
> rollback tran
> else
> commit tran
> is it possible to achieve this with dtsexecui? it's strange but dtsexecui
> never get this error, so my idea this is because import/export wizard
> doesn't use rollback segment in oracle. you said that possible to import
> data in one transaction, what you mean? because when I use dtsexecui, and
> try to load 100 mln of rows and if import fails after 80 mln, so these 80
> mln of rows will be added to the table.
>
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...
> could
>