Migrating SQL 2000 identity's 2

[multiplex77]

Hi,

I often have to migrate my SQL 2000 database tables between my Development database and Production database (both SQL 2000). But I find that when I do so, those tables with columns with Identity=Yes end up as Identity=No in the new database. Why is the identity not migrated over? Is there any way make it migrated over as well? What else is not migrated over?

Thanks for the help.

 

[SQLSister]

You don't mention what method you are using to migrate.

 

[tlbroadbent]

If you use one of the standard methods to move/copy tables from one database to another, the Identity should be set properly. DTS will create the identiy column properly or you can script the table on one server and create it on another. This will also handle the Identity column properly. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[multiplex77]

Hi,

Thanks for your replies. I have discovered that, in addition to the IDENTITY flags not transferring over, the Default Values (like 0 for bit columns) are also not transferred over.

I am using SQL 2000's "DTS Import/Export Wizard" to migrate my database tables.

After specifying the source and destination database/tables, I got to the question "Specify Table Copy or Query". I chose "Copy table(s) and view(s) from the source database", selected the tables to copy and then checked "Run immediately" then executed the export.

It shows copied successfully. I don't expect the identity column values to be identical as the original table, but then the column in the destination table is marked as Identity = No.

Is there some procedure I'm missing here?

Thanks for the help.

 

[multiplex77]

Anyone with any experience dealing with this problem?

Thanks.

 

[osjohnm]

Hi

When you choose "Copy table(s) and view(s) from the source database", SQL Server copies the tables and data but doesn't copy all the database objects. egforeign keys, primary keys )

To dts all the identities, defaults, primary keys, foreign keys, indexes etc choose "Copy database objects".

Then you also have more options available to you, you can customize which objects you wish to move such as stored procedures, user-defined functions, default values, tables and views.

You may also select which options you would like to use, eg:
copy database users, copy full-text indexes, indexes, primary and foreign key constraints.

This is probably the better option to use, but when adding new tables to a production database it is better to script them from your dev database but the method above will copy everything that you specify.

Hope that answers all your questions

John

 

[multiplex77]

Thanks for the very useful help, John. But in the "Select objects to copy", is it the "Include extended objects" that copies over the identities and default values? Everything else is checked. I keep getting an error "Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server". But it seems to work fine when I just copy tables.

 

[osjohnm]

Hi

Sorry for the late response (on leave today). Have you chosen to drop the destination objects or not. Occasionally that causes problems if a table you are trying to change already exists. Make sure you don't overwrite your live data.

Another thing is, I don't copy database users or roles or object level permissions under the options because often a database user might not exist on the destination server etc.

It could be the extended properties but by default that isn't selected so I usually leave it alone but since all you would be moving is default properties it shouldn't be that.

When I'm back in the office I'll have a look at this properly and see if I can test a scenario by selecting the extended properties.

Glad you came right with your tables

John

 

[osjohnm]

Hi Multiplex

I miss read your last post, I thought you were asking if the extended properties would cause the error. My apologies for that.

The create destination objects task will create the tables, views, defaults etc.

If you deselect Copy All Objects and open up "select objects" you will have the option to select everything or only certain tables, views, stored procs or even defaults.

If you uncheck "Use default options" in the main tab you will then be in the "Advanced Copy Options" where you can select to copy indexes and primary keys and foreign keys. By default all the options are selected.

I ran a test where I copied one table which had an identity on it, i only chose the table under "select objects" and didn't copy the users, roles, pk + fk under default options but did take the indexes across.

It copied without errors, created the identity but didn't move the PK since I told it not to, so as mentioned above the "Create destination objects" will create the identities.

Here is an extract from BOL about each of the tasks/options when copying all database objects (be warned its a bit long)

Copy SQL Server Objects Task (Copy Tab)
Use this tab to specify which objects to transfer from one Microsoft® SQL Server™ database to another. You can transfer only from one instance of SQL Server version 7.0 to another, from an instance of SQL Server 7.0 to an instance of SQL Server 2000, and from one instance of SQL Server 2000 to another.

Create destination objects

Create destination objects for all objects to be transferred (tables, views, stored procedures, defaults, rules, constraints, user-defined data types, logins, users, roles, and indexes). Specify the following copy options:

Drop destination objects first
Drop all corresponding destination objects before creating new ones.

Include all dependent objects
Include all dependent objects, such as the tables supporting a view, in the transfer of data.
Copy data

Enable the copying of SQL Server data from source to destination. Specify the following copy options:

Replace existing data
Overwrite existing data in the destination objects with the new data from the specified source.

Append data
Retain existing data in the destination object, and append new data from the specified source.
Use Collation

Enable the copying of data between different collations. For more information on using different collations with Data Transformation Services (DTS), see Data Conversion and Transformation Considerations.

Copy all objects

Transfer all objects associated with the specified data source.

Select objects

Display the Select Objects dialog box, where you can select or remove objects from the transfer process. Clear the Transfer all objects check box to enable this option.

Use default options

Set the advanced transfer options to their defaults.

Options

Display the Advanced Copy Options dialog box, where you can select or remove the specific objects to be transferred. Clear the Use default options check box to enable this option.

Script file directory

Specify the directory to which the script file and log files are written. The script file directory must exist on the computer on which the task runs.

Hope that clears up your question in your last post.

About the error, once you have clicked OK, click on the DTS windows on the line that failed and it will tell you why it failed.

John

 

[wheresaldo]

Hi,

I'm having a similar problem... I'm coping a database using DTS Export Wizard, but the Identily field are set to 1, but when I do an insert, the Identiy field start from 0 (zero) not 1. I have manually reseed the field, but still starts from 0.

If I insert a recored using Enterprice Manager, then starts from 1, but the same insert through ASP will start from 0.

Any ideas?