ive got a sql 2000 db running on a remotely hosted server. im trying to take this db and export it and import it to my new web host. my problem is that I go to the db, select export, then select, copy objects to export. i keep getting an error that the export failed due to the fact that the user doesnt exist (the existing db has a username the webhost assigned me, the new db has a different username which the new webhost assigned me). I simply want to export all the data, the tables, primary and foreign keys. help!

thanks, lance

Hi,

Best option is :-

1. Backup the database from source server (See BACKUP database command in
books online)
2. COpy the backup file to destination server
3. Restore the database to the server (See Restore database command in books
online)

4. After the restore , correct the orphan users by running
sp_change_users_login (see books line for usage)

--
Thanks
Hari
MCDBA
"ljp997" <ljp997 (AT) discussions (DOT) microsoft.com> wrote

that I go to the db, select export, then select, copy objects to export. i
keep getting an error that the export failed due to the fact that the user
doesnt exist (the existing db has a username the webhost assigned me, the
new db has a different username which the new webhost assigned me). I simply
want to export all the data, the tables, primary and foreign keys. help!

You are probably trying to copy the local logins from the remote server.
Personally I think this task has its uses but your requirement is not one
of them. I would look to do

BACKUP, at the remote site and then RESTORE, at your local site.

Much quicker, cleaner and easier

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"ljp997" <ljp997 (AT) discussions (DOT) microsoft.com> wrote

that I go to the db, select export, then select, copy objects to export. i
keep getting an error that the export failed due to the fact that the user
doesnt exist (the existing db has a username the webhost assigned me, the
new db has a different username which the new webhost assigned me). I simply
want to export all the data, the tables, primary and foreign keys. help!

Both the solutions posted regarding this problem advise that I backup the database. This is a remotely hosted db on a shared server. Most shared hosts, including mine, do not give you rights to perform a backup. Therefore, backing up is not an option. So back to my original question, how can I export the db, or perhaps the first question is, is it possible to export and import all the tables and data? If so, can someone please tell me how (see original comments on problems performing the export as a result of different db usernames).

Thanks,
lance

"ljp997" wrote:

Both the solutions posted regarding this problem advise that I backup the database. This is a remotely hosted db on a shared server. Most shared hosts, including mine, do not give you rights to perform a backup. Therefore, backing up is not an option. So back to my original question, how can I export the db, or perhaps the first question is, is it possible to export and import all the tables and data? If so, can someone please tell me how (see original comments on problems performing the export as a result of different db usernames).

Thanks,
lance

"ljp997" wrote:

Both the solutions posted regarding this problem advise that I backup the database. This is a remotely hosted db on a shared server. Most shared hosts, including mine, do not give you rights to perform a backup. Therefore, backing up is not an option. So back to my original question, how can I export the db, or perhaps the first question is, is it possible to export and import all the tables and data? If so, can someone please tell me how (see original comments on problems performing the export as a result of different db usernames).

Thanks,
lance

"Allan Mitchell" wrote:

Both the solutions posted regarding this problem advise that I backup the database. This is a remotely hosted db on a shared server. Most shared hosts, including mine, do not give you rights to perform a backup. Therefore, backing up is not an option. So back to my original question, how can I export the db, or perhaps the first question is, is it possible to export and import all the tables and data? If so, can someone please tell me how (see original comments on problems performing the export as a result of different db usernames).

Thanks,
lance

"ljp997" wrote:

If you cannot use BACKUP / RESTORE then

1. Use the Transfer SQL Server Objects Task and in th options specify to
not take the logins.
2. Script out the database, apply the script locally (Except constraints),
Pump the data, Apply the constraints.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"ljp099" <ljp099 (AT) discussions (DOT) microsoft.com> wrote

hosts, including mine, do not give you rights to perform a backup.
Therefore, backing up is not an option. So back to my original question, how
can I export the db, or perhaps the first question is, is it possible to
export and import all the tables and data? If so, can someone please tell me
how (see original comments on problems performing the export as a result of
different db usernames).