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 relations with DTSWizard?

Hello,

I have installed the recent version of MSSQLExpress 2005 with the DTSWizard. I was able to import my Access Database successfully into MSSQL. However the keys and relations are not copied over. Is there a way to do that? or Do I have to set everything manually?

Thanks
KaveCheck this KBA http://support.microsoft.com/kb/237980 about upsizing the access database and that will talk about your need. Review http://support.microsoft.com/kb/285829 too.|||Many thanks that helped me out.

Regards
Kave

How to import data into development database from productions database

I have two different setup, one for development and other for production.
Two different SQL server 2000 installed on two different Windows 2000
server. I want to import all data of selected tables from production server
to development server. Databases name on both seerver are different. Before
I import, I want to remove all information from selected tables from
development database. Can anyone suggest me TSQL code?
Thanks.You might want to script the tables out en masse and build them that way,
rather than "import" anything (since you're not really importing any data).
See http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Would it be easer to backup and restore production database on the
development server under different name?
"Sunny" wrote:
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Or you can do something like this, with is not the best solution:
DECLARE @.name SYSNAME
DECLARE rs CURSOR
READ_ONLY
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--
OPEN rs
--
FETCH NEXT FROM rs INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- find your tables you need
IF @.name = 'Table1'
BEGIN
PRINT @.name
--
DELETE
FROM OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
--
INSERT INTO OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
SELECT * FROM TABLE1
--
END
END
FETCH NEXT FROM rs INTO @.name
END
--
CLOSE rs
DEALLOCATE rs
GO
But before that you have to add linked server and linked server login
"Sunny" wrote:
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Thanks Aaron and Sergey.
Backup restore works, but sometime I just want to few tables and not all
tables from production data. what I would like to do is store all tables
name in array and for each array element first delete all records from table
(name in array) of development server and copy all records from production
server. Any idea? or any better solution?
Thanks.
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||DTS?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks Aaron and Sergey.
> Backup restore works, but sometime I just want to few tables and not all
> tables from production data. what I would like to do is store all tables
> name in array and for each array element first delete all records from
table
> (name in array) of development server and copy all records from production
> server. Any idea? or any better solution?
> Thanks.
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > I have two different setup, one for development and other for
production.
> > Two different SQL server 2000 installed on two different Windows 2000
> > server. I want to import all data of selected tables from production
> server
> > to development server. Databases name on both seerver are different.
> Before
> > I import, I want to remove all information from selected tables from
> > development database. Can anyone suggest me TSQL code?
> >
> > Thanks.
> >
> >
> >
>|||Yes, I think that would be the best option. I have not used DTS before and
SQL server is new to me, do you have any suggestion where to start?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
> DTS?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > Thanks Aaron and Sergey.
> >
> > Backup restore works, but sometime I just want to few tables and not all
> > tables from production data. what I would like to do is store all tables
> > name in array and for each array element first delete all records from
> table
> > (name in array) of development server and copy all records from
production
> > server. Any idea? or any better solution?
> >
> > Thanks.
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > > I have two different setup, one for development and other for
> production.
> > > Two different SQL server 2000 installed on two different Windows 2000
> > > server. I want to import all data of selected tables from production
> > server
> > > to development server. Databases name on both seerver are different.
> > Before
> > > I import, I want to remove all information from selected tables from
> > > development database. Can anyone suggest me TSQL code?
> > >
> > > Thanks.
> > >
> > >
> > >
> >
> >
>|||www.sqldts.com
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OfQ3oCvqEHA.3244@.tk2msftngp13.phx.gbl...
> Yes, I think that would be the best option. I have not used DTS before and
> SQL server is new to me, do you have any suggestion where to start?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
> > DTS?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > > Thanks Aaron and Sergey.
> > >
> > > Backup restore works, but sometime I just want to few tables and not
all
> > > tables from production data. what I would like to do is store all
tables
> > > name in array and for each array element first delete all records from
> > table
> > > (name in array) of development server and copy all records from
> production
> > > server. Any idea? or any better solution?
> > >
> > > Thanks.
> > >
> > >
> > > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > > news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > > > I have two different setup, one for development and other for
> > production.
> > > > Two different SQL server 2000 installed on two different Windows
2000
> > > > server. I want to import all data of selected tables from production
> > > server
> > > > to development server. Databases name on both seerver are different.
> > > Before
> > > > I import, I want to remove all information from selected tables from
> > > > development database. Can anyone suggest me TSQL code?
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

How to import data into development database from productions database

I have two different setup, one for development and other for production.
Two different SQL server 2000 installed on two different Windows 2000
server. I want to import all data of selected tables from production server
to development server. Databases name on both seerver are different. Before
I import, I want to remove all information from selected tables from
development database. Can anyone suggest me TSQL code?
Thanks.
You might want to script the tables out en masse and build them that way,
rather than "import" anything (since you're not really importing any data).
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>
|||Thanks Aaron and Sergey.
Backup restore works, but sometime I just want to few tables and not all
tables from production data. what I would like to do is store all tables
name in array and for each array element first delete all records from table
(name in array) of development server and copy all records from production
server. Any idea? or any better solution?
Thanks.
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>
|||DTS?
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks Aaron and Sergey.
> Backup restore works, but sometime I just want to few tables and not all
> tables from production data. what I would like to do is store all tables
> name in array and for each array element first delete all records from
table[vbcol=seagreen]
> (name in array) of development server and copy all records from production
> server. Any idea? or any better solution?
> Thanks.
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
production.
> server
> Before
>
|||Yes, I think that would be the best option. I have not used DTS before and
SQL server is new to me, do you have any suggestion where to start?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> DTS?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> table
production
> production.
>
|||www.sqldts.com
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OfQ3oCvqEHA.3244@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Yes, I think that would be the best option. I have not used DTS before and
> SQL server is new to me, do you have any suggestion where to start?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
all[vbcol=seagreen]
tables[vbcol=seagreen]
> production
2000
>

How to import data from MSDE to Sql Server?

I previously used MSDE as my web database, but now I've installed SQL Server Enterprise 2000 into my server. My question is how I can import data from MSDE to my new SQL server? I don't wanna lose all the data.
Thank you!Backup the database on MSDE. Restore them on the new SQL Server installation. After that, you'll want to sync up the logins. Search this site or SQLTeam.com for a script called sp_help_revlogin. You need to run that on the MSDE version, then apply the permissions to the new installation. After that you should be good to go.|||backup and restore is one of the options, but you can also just sp_detach_db/sp_attach_db

How to import an mdf-file

Hi,on my developement PC i have installed MS Sql server. A customer with my program using msde sent me his .mdf file so that i can check his data. How do i import or connect to his database file for debugging?
Regards,
Olav
Try attaching the file using sp_attach_single_file_db. You
can find more information on using this stored procedure in
books online.
-Sue
On Tue, 25 May 2004 04:26:04 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,on my developement PC i have installed MS Sql server. A customer with my program using msde sent me his .mdf file so that i can check his data. How do i import or connect to his database file for debugging?
>Regards,
>Olav
|||You can try the stored procedure sp_attach_single_file_db (see Books Online
for usage and syntax). Without a log file, if the database wasn't cleanly
detached, it's not a guaranteed move. You should try to get the user to
send you a synchronized and properly detached MDF+LDF pair.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Olav" <anonymous@.discussions.microsoft.com> wrote in message
news:E7DC9CD6-1952-4896-9C62-5D9C311674A0@.microsoft.com...
> Hi,on my developement PC i have installed MS Sql server. A customer with
> my program using msde sent me his .mdf file so that i can check his data.
> How do i import or connect to his database file for debugging?
> Regards,
> Olav
|||Thanks a lot, it worked!
Is there a better way to connect a customer database?
Regards,
Olav
|||If all you have is an mdf, it seems the only choice you'd
have would be sp_attach_single_file_db.
The customer should make sure to use sp_detach_db if you are
going to attach a database using sp_attach_db or
sp_attach_single_file_db.
Another option would be for the customer to give you a
backup file. You can restore this backup to your server.
-Sue
On Tue, 25 May 2004 05:21:05 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:

>Thanks a lot, it worked!
>Is there a better way to connect a customer database?
>Regards,
>Olav

How to import an mdf-file

Hi,on my developement PC i have installed MS Sql server. A customer with my program using msde sent me his .mdf file so that i can check his data. How do i import or connect to his database file for debugging
Regards
OlavTry attaching the file using sp_attach_single_file_db. You
can find more information on using this stored procedure in
books online.
-Sue
On Tue, 25 May 2004 04:26:04 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,on my developement PC i have installed MS Sql server. A customer with my program using msde sent me his .mdf file so that i can check his data. How do i import or connect to his database file for debugging?
>Regards,
>Olav|||You can try the stored procedure sp_attach_single_file_db (see Books Online
for usage and syntax). Without a log file, if the database wasn't cleanly
detached, it's not a guaranteed move. You should try to get the user to
send you a synchronized and properly detached MDF+LDF pair.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Olav" <anonymous@.discussions.microsoft.com> wrote in message
news:E7DC9CD6-1952-4896-9C62-5D9C311674A0@.microsoft.com...
> Hi,on my developement PC i have installed MS Sql server. A customer with
> my program using msde sent me his .mdf file so that i can check his data.
> How do i import or connect to his database file for debugging?
> Regards,
> Olav|||Thanks a lot, it worked
Is there a better way to connect a customer database
Regards,
Olav|||If all you have is an mdf, it seems the only choice you'd
have would be sp_attach_single_file_db.
The customer should make sure to use sp_detach_db if you are
going to attach a database using sp_attach_db or
sp_attach_single_file_db.
Another option would be for the customer to give you a
backup file. You can restore this backup to your server.
-Sue
On Tue, 25 May 2004 05:21:05 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks a lot, it worked!
>Is there a better way to connect a customer database?
>Regards,
>Olav

How to import an mdf-file

Hi,on my developement PC i have installed MS Sql server. A customer with my
program using msde sent me his .mdf file so that i can check his data. How
do i import or connect to his database file for debugging?
Regards,
OlavTry attaching the file using sp_attach_single_file_db. You
can find more information on using this stored procedure in
books online.
-Sue
On Tue, 25 May 2004 04:26:04 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,on my developement PC i have installed MS Sql server. A customer with m
y program using msde sent me his .mdf file so that i can check his data. How
do i import or connect to his database file for debugging?
>Regards,
>Olav|||You can try the stored procedure sp_attach_single_file_db (see Books Online
for usage and syntax). Without a log file, if the database wasn't cleanly
detached, it's not a guaranteed move. You should try to get the user to
send you a synchronized and properly detached MDF+LDF pair.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Olav" <anonymous@.discussions.microsoft.com> wrote in message
news:E7DC9CD6-1952-4896-9C62-5D9C311674A0@.microsoft.com...
> Hi,on my developement PC i have installed MS Sql server. A customer with
> my program using msde sent me his .mdf file so that i can check his data.
> How do i import or connect to his database file for debugging?
> Regards,
> Olav|||Thanks a lot, it worked!
Is there a better way to connect a customer database?
Regards,
Olav|||If all you have is an mdf, it seems the only choice you'd
have would be sp_attach_single_file_db.
The customer should make sure to use sp_detach_db if you are
going to attach a database using sp_attach_db or
sp_attach_single_file_db.
Another option would be for the customer to give you a
backup file. You can restore this backup to your server.
-Sue
On Tue, 25 May 2004 05:21:05 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:

>Thanks a lot, it worked!
>Is there a better way to connect a customer database?
>Regards,
>Olav

how to identify the fixpack level?

hi,
for sql server 2000, how can we find the fixpack(service pack) level installed on this sql server?
is there any command, or any gui tool to identify the level?
tnksSET NOCOUNT ON
SELECT CONVERT(CHAR(25),@.@.SERVERNAME) AS 'SQL SERVER',
SUBSTRING(@.@.VERSION,23,4) AS 'PRODUCT VERSION',
SUBSTRING(@.@.VERSION,35,3) AS 'BUILD NUMBER',
CASE SUBSTRING(@.@.VERSION,35, 3)
WHEN '194' THEN 'NO SP'
WHEN '384' THEN 'SP1'
WHEN '534' THEN 'SP2'
WHEN '760' THEN 'SP3'

ELSE 'Unknown - may be a Hot-Fix version or script out of date'
END AS 'SERVICE PACK'
set nocount off|||thanks philio,
how about service pack 3a, is there an id also to distinguish it from sp3??|||SP3 and SP3a have identical build numbers (760).

Also, you can use this:

SELECT
cast(@.@.microsoftversion / power(2, 24) as varchar(2)) + '.00.' + cast(@.@.microsoftversion & 0xffff as varchar(4)) as VERSION

To derive version info...

hmscott
thanks philio,
how about service pack 3a, is there an id also to distinguish it from sp3??

How to identify one sql server installation to another

Hi All,
How can we identify one sql server isntallation to another installation? For
example, installed sql server into a station, later remove it, then
installed again. How can we differential this 2 different installations?
Best Regards,
EdwardThe old one isn't there anymore? Is this like playing twister with a
blindfold?
Explain what you are wanting a little clearer. Give us a specific example.
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Edward Low" wrote:

> Hi All,
> How can we identify one sql server isntallation to another installation? F
or
> example, installed sql server into a station, later remove it, then
> installed again. How can we differential this 2 different installations?
>
> Best Regards,
> Edward
>
>|||Edward
If you did uninstall of SQL Server , all keys,data will be removed from the
registry, actually i did not check it by myself , but I'm pretty sure. Hav
you check it by using REGEDIT command
"Edward Low" <wc_low@.hotmail.com> wrote in message
news:ukS$qs6fGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> How can we identify one sql server isntallation to another installation?
> For example, installed sql server into a station, later remove it, then
> installed again. How can we differential this 2 different installations?
>
> Best Regards,
> Edward
>|||Hi,
Thanks for reply. In fact, i want to know the unique key of the sql server
installation. For example, we know every hard drive have a volumn serial no
which is assigned each time the partition is formatted. So, is the sql
server have such unique key/serial no?
Regards,
Edward
"MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
news:959C73C3-0577-4128-B167-0433BC9C1541@.microsoft.com...
> The old one isn't there anymore? Is this like playing twister with a
> blindfold?
> Explain what you are wanting a little clearer. Give us a specific
> example.
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Edward Low" wrote:
>