copy database tables

Hi!

Can someone tell me how I can copy my database which is made with SQLExpress or the tables to another SQL server. The servers could be SQL server 2000 and SQLExpress.

Thanks!

SQL Server Express --> SQL Server Express (or any other 2005er edition), just detach the mdf file (if possible) and copy it to the destination location, then attach the orginal one to the server again, and attach the copy to the remote server -done. If you have no maintainance window to do so, make a backup and a restore on the other server (could take a bit longer but keeps your database online during the whole time)

SQL Server Express --> downgade versions: Create scripts of your objects and data. Make sure that the scripts are compatible with the versions you want to deploy it to.

HTH, jens Suessmeyer.

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

At first I made backup file of my SQLExpress database and and copy it to another PC witch have SQLExpress also, but I didn't work.

Then I tryed the script and it did work. Now I have to test this also to SQL Server 2000.

The question, is what did I do wrong with the backup?

|||Doing a copy from SQL Server Express to SQL Server Express should work fine, which error message are you getting ?

If you have a SQL Server 2000 Dev/Std/Ent you can use the DTS Wizard to copy the database from SQl Server Express to SQL 2000 (Import).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Converting to use replication

I have a DB running under SQL 2005. I would like to enable the database for
replication to offline clients (SQLExpress) and SQL Server 2005 Compact
Edition.
Will converting the primary keys of the database to use GUID's be sufficient
to enable the replication to work property.? They are currently integers.
No, don't do this. Replication will add its own GUID key.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick Zdunic" <NickZdunic@.discussions.microsoft.com> wrote in message
news:A04F975C-AC8F-4FDB-A6D4-030C21392DFF@.microsoft.com...
>I have a DB running under SQL 2005. I would like to enable the database
>for
> replication to offline clients (SQLExpress) and SQL Server 2005 Compact
> Edition.
> Will converting the primary keys of the database to use GUID's be
> sufficient
> to enable the replication to work property.? They are currently integers.
|||I know that it does this, but my keys are autonumber fields, so wouldn't it
be better to convert them to GUIDs so that clashes don't occur later on.
Because the fields are GUIDs SQL server shouldn't need to add GUIDs - or
will this still occur even when GUIDs are already present?
"Hilary Cotter" wrote:

> No, don't do this. Replication will add its own GUID key.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nick Zdunic" <NickZdunic@.discussions.microsoft.com> wrote in message
> news:A04F975C-AC8F-4FDB-A6D4-030C21392DFF@.microsoft.com...
>
>
|||Either consider automatic identity range management or use different seeds
on either side, i.e. even on one side, odd on another.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick Zdunic" <NickZdunic@.discussions.microsoft.com> wrote in message
news:EEEEBE0A-22E9-432A-A2AF-98EF18806B7A@.microsoft.com...[vbcol=seagreen]
>I know that it does this, but my keys are autonumber fields, so wouldn't it
> be better to convert them to GUIDs so that clashes don't occur later on.
> Because the fields are GUIDs SQL server shouldn't need to add GUIDs - or
> will this still occur even when GUIDs are already present?
> "Hilary Cotter" wrote:
|||Might still go with GUIDs - as the number of clients could vary so it will be
easier to manage.
So will using GUIDs stop the extra GUID being added by SQL Server?
"Hilary Cotter" wrote:

> Either consider automatic identity range management or use different seeds
> on either side, i.e. even on one side, odd on another.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nick Zdunic" <NickZdunic@.discussions.microsoft.com> wrote in message
> news:EEEEBE0A-22E9-432A-A2AF-98EF18806B7A@.microsoft.com...
>
>
|||You can add your own GUID field and call it what you want. Replication
needs a uniqueidentifier field with the ROWGUIDCOL property set. If it
finds an existing one it will use that instead of creating a new one.
Just remember to set the field default to (newid()).
I agree with the advice not to set your primary key to a GUID. You will
run into performance problems when the table grows. Searching an integer
index is much faster than searching an GUID index. Auto identity
requires management, but it is definately the way to go.
Nick Zdunic wrote:[vbcol=seagreen]
> Might still go with GUIDs - as the number of clients could vary so it will be
> easier to manage.
> So will using GUIDs stop the extra GUID being added by SQL Server?
> "Hilary Cotter" wrote:
|||Int's will be faster, but with more than 5 replicas and maybe even 10 to 50
managing autonumber fields would be a nightmare.
"JE" wrote:

> You can add your own GUID field and call it what you want. Replication
> needs a uniqueidentifier field with the ROWGUIDCOL property set. If it
> finds an existing one it will use that instead of creating a new one.
> Just remember to set the field default to (newid()).
> I agree with the advice not to set your primary key to a GUID. You will
> run into performance problems when the table grows. Searching an integer
> index is much faster than searching an GUID index. Auto identity
> requires management, but it is definately the way to go.
>
> Nick Zdunic wrote:
>

Converting MS ACCESS db to SQLExpress

Is it possible to convert an ms access mdb file to an sqlexpress mdf file?
Any help appreciated,
Henk Feijt

You cannot convert MDB to MDF because MDF(microsoft data file) is only half of a SQL Server database because you also have the LDF(log data file). Create the database in Express and do a INSERT INTO or download the eval version of the full SQL Server 2005 and install the OLTP(online transaction processing) management studio which is a separate install under management tools and use integration services which is the new DTS to import the database from Access. BTW Access was not relational untill version 2000. Hope this helps.
http://www.microsoft.com/sql/downloads/trial-software.mspx|||Thanks for your info. But I find out that you can convert mdb to mdf. Access 2000 has an upsize wizard that convert the mdb to adf or mdf.
|||MDB to MDF
You will not belive that, but just trycopy(content of table from SHOW TABLE DATA)and paste(to prepared target table with same structure as the source)!
First you have to connect MDB ACCESS as datasource to the database explorer.
I Was spend almost two days on hard trying to conect and do SELECT INTO....
but it's so trivial to import data into the SQLExpress without sophisticated tools!