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 MySQL data into SQL2005 using wizard?

Hello everybody,

I'm a new SQL2005 user.
I'm

trying to import data from MySQL version 5x into my SQL2005 by wizard.

I created a DNS file and tested successfully using MySQL Connector/ODBC

v5.
Everthing seems fine but at the last step selecting data

source. The SQL2005 wizard forced me to choose using SQL command option

instead of selecting tables/views from a list. Anyone can tell me why?

My collegues faced the same case as they help me to find the reason.

I'm sure that there are a few source objetcts in MySQL source.

Any help will be appreciated!!!

khanhmy

Hi,

Unfortunately this is the limitation of the Import/Export Wizard in SQL 2005. It can't get table metadata for ADO.NET and ODBC sources, so the only way to use it with ADO.NET or ODBC sources is to type the SQL command.

We are looking at fixing this for next version.

Regards,

Michael.

How to import DBF file in SQL2005

Hi,
It is easy to import .DBF file In Sql 2000.
But, How to import DBF file in SQL2005?
thanks.
billCan you explain how you do it in 2000?
> It is easy to import .DBF file In Sql 2000.
> But, How to import DBF file in SQL2005?
> thanks.
> bill
>|||Hi Bill,
You can try this
How to: Connect to a dBASE or Other DBF File
http://msdn2.microsoft.com/en-us/library/aa337084.aspx
If you want to use the Import and Export Wizard maybe you want to import the
data to SQL Server 2000 first and then import this data from SQL Server 2000
to 2005. Or maybe import from DBF to Excel or Access and then use the wizard
to import to SQL Server 2005.
Hope this helps,
Ben Nevarez
Senior Database Administrator
"bill" wrote:
> Hi,
> It is easy to import .DBF file In Sql 2000.
> But, How to import DBF file in SQL2005?
> thanks.
> bill
>
>|||> If you want to use the Import and Export Wizard maybe you want to import
> the
> data to SQL Server 2000 first and then import this data from SQL Server
> 2000
> to 2005. Or maybe import from DBF to Excel or Access and then use the
> wizard
> to import to SQL Server 2005.
Ben,
Do you think there might be drivers out there that work with DBF files in
2005?
There is a similar situation going on with SQL Server 2008, where Office
2007 is being ignored. You will need to install ACE OLEDB drivers after the
fact if you want the wizards to be able to understand Office 2007 file
formats.
Aaron|||The link is from BOL for SQL Server 2005 and it is using the Microsoft Jet
4.0 OLE DB Provider on SSIS. At this moment I do not have any DBF file to
test it.
I also mentioned that the Import and Export Wizard can be used in a two step
process from DBF to SQL Server 2000 or Access or Excel and from there to SQL
Server 2005.
Regards,
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> > If you want to use the Import and Export Wizard maybe you want to import
> > the
> > data to SQL Server 2000 first and then import this data from SQL Server
> > 2000
> > to 2005. Or maybe import from DBF to Excel or Access and then use the
> > wizard
> > to import to SQL Server 2005.
> Ben,
> Do you think there might be drivers out there that work with DBF files in
> 2005?
> There is a similar situation going on with SQL Server 2008, where Office
> 2007 is being ignored. You will need to install ACE OLEDB drivers after the
> fact if you want the wizards to be able to understand Office 2007 file
> formats.
> Aaron
>|||OK!
Thank you very much!
bill
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:37B67CC1-2786-4E9C-AB6D-212BAEC69FA3@.microsoft.com...
> Hi Bill,
> You can try this
> How to: Connect to a dBASE or Other DBF File
> http://msdn2.microsoft.com/en-us/library/aa337084.aspx
> If you want to use the Import and Export Wizard maybe you want to import
> the
> data to SQL Server 2000 first and then import this data from SQL Server
> 2000
> to 2005. Or maybe import from DBF to Excel or Access and then use the
> wizard
> to import to SQL Server 2005.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
>
> "bill" wrote:
>> Hi,
>> It is easy to import .DBF file In Sql 2000.
>> But, How to import DBF file in SQL2005?
>> thanks.
>> bill
>>

How to import DBF file in SQL2005

Hi,
It is easy to import .DBF file In Sql 2000.
But, How to import DBF file in SQL2005?
thanks.
bill
Can you explain how you do it in 2000?

> It is easy to import .DBF file In Sql 2000.
> But, How to import DBF file in SQL2005?
> thanks.
> bill
>
|||Hi Bill,
You can try this
How to: Connect to a dBASE or Other DBF File
http://msdn2.microsoft.com/en-us/library/aa337084.aspx
If you want to use the Import and Export Wizard maybe you want to import the
data to SQL Server 2000 first and then import this data from SQL Server 2000
to 2005. Or maybe import from DBF to Excel or Access and then use the wizard
to import to SQL Server 2005.
Hope this helps,
Ben Nevarez
Senior Database Administrator
"bill" wrote:

> Hi,
> It is easy to import .DBF file In Sql 2000.
> But, How to import DBF file in SQL2005?
> thanks.
> bill
>
>
|||> If you want to use the Import and Export Wizard maybe you want to import
> the
> data to SQL Server 2000 first and then import this data from SQL Server
> 2000
> to 2005. Or maybe import from DBF to Excel or Access and then use the
> wizard
> to import to SQL Server 2005.
Ben,
Do you think there might be drivers out there that work with DBF files in
2005?
There is a similar situation going on with SQL Server 2008, where Office
2007 is being ignored. You will need to install ACE OLEDB drivers after the
fact if you want the wizards to be able to understand Office 2007 file
formats.
Aaron
|||The link is from BOL for SQL Server 2005 and it is using the Microsoft Jet
4.0 OLE DB Provider on SSIS. At this moment I do not have any DBF file to
test it.
I also mentioned that the Import and Export Wizard can be used in a two step
process from DBF to SQL Server 2000 or Access or Excel and from there to SQL
Server 2005.
Regards,
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:

> Ben,
> Do you think there might be drivers out there that work with DBF files in
> 2005?
> There is a similar situation going on with SQL Server 2008, where Office
> 2007 is being ignored. You will need to install ACE OLEDB drivers after the
> fact if you want the wizards to be able to understand Office 2007 file
> formats.
> Aaron
>
|||OK!
Thank you very much!
bill
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> glsD:37B67CC1-2786-4E9C-AB6D-212BAEC69FA3@.microsoft.com...[vbcol=seagreen]
> Hi Bill,
> You can try this
> How to: Connect to a dBASE or Other DBF File
> http://msdn2.microsoft.com/en-us/library/aa337084.aspx
> If you want to use the Import and Export Wizard maybe you want to import
> the
> data to SQL Server 2000 first and then import this data from SQL Server
> 2000
> to 2005. Or maybe import from DBF to Excel or Access and then use the
> wizard
> to import to SQL Server 2005.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
>
> "bill" wrote:

How to Import Data from AS400 files to Sql 2005 tables?

Hi All,

I want to Import data from AS400 to Sql2005. From the Sql Management studio I invoke the Import Data wizard. For the source I connect to the ISereis system and for the Destination I select the Sql2005 Database , When I go to the next step i.e select source tables I get the error
"An error has occured which the SQL Server Integration Wizard was not prepared to handle
No error message available,result codeB_E_CANTCANCEL(0*80040E15).(System.Data)" .After this I am unable to proceed further. I am using client Access tool to connect to ISereis

IBM DB2 UDB for isereis IBMDA400 OLE DB Provider

Regds,

Anu

If this is going to be a regular task, might as well dump your AS400 data in a delimited text file and import from there|||Moving to the SSIS forums