Copy database with full-text index

Hi,

Can anyone please explain the proper precedure for copying a SQL Express database between two instances?

I am accessing the database without problems from a local web application. And I want to copy the database to a SQL Express instance on another server, running the same web application.

I run into two problems every time I copy:

1) Orphaned users. I have to drop the database users and the re-map the server users to database users.

2) The full-text indexes are not available after copy, so I have to drop and re-create the indexes and the catalog.

And I suspect there's an easier way..

Regards,
Jens Erik

Hi Jens,

The behavior with users is expected. Users and Logins are indelibly linked and only the Users are stored in the database. When you move a database to production, you have to create the appropriate Logins on the new server and then link them to the Users in your database.

Full-text indexes are another odd case. These indexes are stored outside of the database as they are created by an external process. Again, just copying the mdf file will not copy and move the Full-text index. Backup/Resore does handle the Full-text index, so you should consider that option. There is more information about this in BOL. Check out http://msdn2.microsoft.com/en-us/library/ms190436.aspx.

Mike

Copy database with full-text index

Hi,

Can anyone please explain the proper precedure for copying a SQL Express database between two instances?

I am accessing the database without problems from a local web application. And I want to copy the database to a SQL Express instance on another server, running the same web application.

I run into two problems every time I copy:

1) Orphaned users. I have to drop the database users and the re-map the server users to database users.

2) The full-text indexes are not available after copy, so I have to drop and re-create the indexes and the catalog.

And I suspect there's an easier way..

Regards,
Jens Erik

Hi Jens,

The behavior with users is expected. Users and Logins are indelibly linked and only the Users are stored in the database. When you move a database to production, you have to create the appropriate Logins on the new server and then link them to the Users in your database.

Full-text indexes are another odd case. These indexes are stored outside of the database as they are created by an external process. Again, just copying the mdf file will not copy and move the Full-text index. Backup/Resore does handle the Full-text index, so you should consider that option. There is more information about this in BOL. Check out http://msdn2.microsoft.com/en-us/library/ms190436.aspx.

Mike

Converting visual foxpro to sql 2000

I am trying to convert a foxpro database to sql 2000 using the wizard within sql 2000 but it keeps giving me an odbc error when accessing the foxpro database? I am stuck and don't know where to turn next. Any ideas? Thanks
rick,
Please share your error message and the version of Visual FoxPro you are
using. It sounds like a problem in defining your
ODBC data source, but need more info.
Russell Fields
"rick" <anonymous@.discussions.microsoft.com> wrote in message
news:DCEDB7E2-CC4A-4661-8E8F-19169A3D35AD@.microsoft.com...
> I am trying to convert a foxpro database to sql 2000 using the wizard
within sql 2000 but it keeps giving me an odbc error when accessing the
foxpro database? I am stuck and don't know where to turn next. Any ideas?
Thanks
|||In news: DCEDB7E2-CC4A-4661-8E8F-19169A3D35AD@.microsoft.com,
rick <anonymous@.discussions.microsoft.com> wrote:
> I am trying to convert a foxpro database to sql 2000 using the wizard
> within sql 2000 but it keeps giving me an odbc error when accessing
> the foxpro database?
Hi Rick,
FoxPro tables can come in either of two formats. They can be "free" tables,
meaning that each table is independent of the others, or they can be part of
a "database container" which holds metadata and allows additional features
for the tables. If you see a file with a DBC extension then you've got a
database.
Also, tables using any of the new features that were introduced in VFP7 and
VFP8 can only be accessed via the FoxPro and Visual FoxPro OLE DB data
provider.
Both the ODBC driver and OLE DB data provider are downloadable from
http://msdn.microsoft.com/vfoxpro/do...s/default.aspx .
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@.mvps.org www.cindywinegarden.com