Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities