How to import/export databases from remote server to local SQL 2005 Express

I've successfully installed SQL2005 express edition and SQL Server Management Studio Express. All seems well except there does not appear to be any way to import databases from other servers to my local server. The remote servers are running SQL Server 7.

In the Management Studio Express I can connect to the remote databases and work with them, but I can neither import them into my local machine, nor can I export from remote to local (the option doesn't seem to exist). By contrast, using the Enterprise Manager in SQL 7 there has always been the option to do this under the All Tasks menu.

If this cannot be done, is there a way to backup a remote database to my local disk and then restore it into my local DB?

Is this by design? Can anyone help with this?

Thanks in advance.

HD

First, you can always detach the databases on the old server using sp_detachdb, move the files to the new server, and attatch them using sp_attachdb. Check BOL for detailed documentation on these stored procedures.

Second, I've moved this thread to the tools forum where experts can tell you if there's a way to do this through Management Studio Express (I'm guessing not).

Paul

How to import bulk data

Hi friends,
How can I import data from remote database. My local DB is Sql
Server 2005 and remote DB is Oracle. The data to be imported is very
large data(in GB).
How can I import faster? The table structures in both the DBs are
same.
Can you give a clear idea.
Thanks in advanceHi,
You can use SSIS. In Management Studio, right click your source database
and choose "Tasks > Import Data..." Then for your data source choose OLE DB
for Oracle. Fill in all the blanks in the Wizard and check "Save SSIS
Package" at the end if you want to run it more than once.
One thing to watch out for transferring Oracle to SQL Server. I was
transferring a table with a couple hundred rows on a daily basis over a WAN
VPN, and it would take about 45 minutes. I finally figured out why it was
so friggin' slow. There were three or four Oracle columns defined as
VARCHAR2(4000). Even though each one has only 3 to 8 characters of data,
they were getting padded out to 4000 characters. That was about 2.4 GB of
padding. I got the Oracle DBA to redefine them to VARCHAR2(10), and my
transfers dropped to 4 1/2 min!
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<rajkm42@.googlemail.com> wrote in message
news:1190061613.152877.107680@.o80g2000hse.googlegroups.com...
> Hi friends,
> How can I import data from remote database. My local DB is Sql
> Server 2005 and remote DB is Oracle. The data to be imported is very
> large data(in GB).
> How can I import faster? The table structures in both the DBs are
> same.
> Can you give a clear idea.
> Thanks in advance
>

How to I run a DTS package from a stored procedure? thank

I created a DTS package to transfer data from a remote database into my local database, I want to run this DTS in my stored procedure, can I do that?

Please help me, thanks a lot

Hi,

you have to call it via dtsrun on the command prompt, ousing xp_cmdshell.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens Suessmeyer,

could you be more specific? can you give me a example?

thanks

|||Sure, look the the DTSRUN syntax, you can start the dtsrun either with a GUID naming the package which is stored in SQL Server or by a structured storage file, using the XP_CMDSHELL 'DTSRUN SomePackage' will get you the package run.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Another method would be to use the system OLE automation SPs and use the DTS object model to invoke the package. Please search the web for several examples. Btw, this question is more suited for the SQL Server Integration Services newsgroup so I will move the thread there so someone there can point you to appropriate resources/links.|||

Please read through the article on 'Data Transformation Services (DTS)' @. http://www.databasejournal.com/features/mssql/article.php/1459181
The example in this article demostrates the use of OLE stored procedures and its benefits.

Btw - This forum majorly deals with SQL Server 2005 - Integration Services.

For DTS (SQL Server 2000) related questions post @. http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.dts&cat=en_US_2b8e81a3-be64-42fa-bd81-c6d41de5a219&lang=en&cr=US

Thanks,
Loonysan

|||I would keep away from using OLE stored procedures - it is a convinient way to do it, but you are running inside SQL Server process. Thus any problems with DTS package may affect the server. Running the package outside using DTSRUN utility is better, as the SQL Server and package run in isolated processes and don't affect each other.

If you mistyped DTS, but was really asking about SSIS (this is SSIS forum after all) - the recommended way is to create Agent Job with the package step, don't assing any schedule to this job, and then start this job from your SQL stored procedure by calling Agent's SP. This provides the isolation as with DTSRUN, but additionally you may specify user context for the SSIS package - so the package does not have to run under the same user as SQL Server.

how to hook into sql server with vbscript or C# ?

How can I do this with vbscript, or C# ?
- Copy backup files down from a network share, into the data directory of my
local sql 2005 instance
- perform a restore using the files copied from above
- Execute a dts package
More info:
Our databases are scripted and exist on the typical development, and testing
enviroments. So as I get ready to start a new application, I want my local
sql instance to be updated based on structure changes as well as data. So I
have to apply the changes from the scripted sources and pull over the data.
I would naturally like to automate this.
ideas / suggestions welcomeHello bitshift,
Three thoughts:
a. Use Visual Studio Team Edition for the Database Developer (aka Data Dude)
since it has this "in the box"
b. Look at tools like Red-Gate's SqlCompare.
c. If you insist on rolling your own (which is doable, but a lot of work),
investigate the Server Management Objects namespace in BOL (see "Getting
Started in SMO").
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb127c088c9d58e6fc9fc70@.news.microsoft.com...
> a. Use Visual Studio Team Edition for the Database Developer (aka Data
> Dude) since it has this "in the box"
this isn't in my MSDN subscription except as a trial; do you know if
there's any way to get hold of it for less than the $3700 or so Programmer's
Paradise wants ?
is it an add-on that I could put on top of VS2005 or a completely separate
product? how does it compare to the Redgate tools ? where there's a
comparision of course ..|||Are you trying to do a custom log shipping? If you are, check out my blog
entry (this of course doesnt include the DTS portion) ;-)
http://bassplayerdoc.blogspot.com/2007/10/your-poor-mans-sql-server-log-shipping.html
"bitshift" <jobob@.aol.com> wrote in message
news:Obd7Kj5BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> How can I do this with vbscript, or C# ?
> - Copy backup files down from a network share, into the data directory of
> my local sql 2005 instance
> - perform a restore using the files copied from above
> - Execute a dts package
>
> More info:
> Our databases are scripted and exist on the typical development, and
> testing enviroments. So as I get ready to start a new application, I want
> my local sql instance to be updated based on structure changes as well as
> data. So I have to apply the changes from the scripted sources and pull
> over the data. I would naturally like to automate this.
> ideas / suggestions welcome
>