|
Home > Archive > MS SQL Server DTS > November 2005 > Copying db from 2000 to 2005 loses identity property
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Copying db from 2000 to 2005 loses identity property
|
|
| Jaret Brower 2005-11-17, 11:23 am |
| I'm trying to do a copy database from 2005. The source server is sql server
2000 and the destination is a sql server 2005. I can copy the db over
without a problem, but every column that I have setup as Identity=true on the
source comes over to the destination as Identity=false.
Any ideas if there is anyway to fix this?
Thanks
| |
| Sophie Guo [MSFT] 2005-11-19, 3:23 am |
| Hello,
I have tested the issue on my side but I didn't reproduce the issue. I use
the copy database wizard in SQL server 2005 to copy a SQL server 2000
database into SQL server 2005. The Identity property of the column is set
to Yes after copying. To narrow down the issue, please post here the
detailed steps to reproduce the issue. Please let me know the version of
SQL server 2005. Let me know if it is a RTM version. Thanks.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| |
| Jaret Brower 2005-11-21, 9:23 am |
| "Sophie Guo [MSFT]" wrote:
> Hello,
>
> I have tested the issue on my side but I didn't reproduce the issue. I use
> the copy database wizard in SQL server 2005 to copy a SQL server 2000
> database into SQL server 2005. The Identity property of the column is set
> to Yes after copying. To narrow down the issue, please post here the
> detailed steps to reproduce the issue. Please let me know the version of
> SQL server 2005. Let me know if it is a RTM version. Thanks.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
I'm using RTM of 2005. I'm also using the SQL management Object method, and
not the detach/attach method. I've tried the detach/attach method, but can't
seem to access the shares properly and get the following error in the event
log:
Event Name: OnError
Message: Access to the path '\\SERVER\Data\datab
ase.mdf' is denied.
StackTrace: at System.IO.__Error.WinIOError(Int32 errorCode, String
maybeFullPath)
at System.IO.File.InternalCopy(String sourceFileName, String
destFileName, Boolean overwrite)
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CopyFile(String sourceFileName, String destinationFileName,
Boolean overwriteOnExist)
| |
| Sophie Guo [MSFT] 2005-11-22, 7:23 am |
| Hello,
Please make sure you have the required permissions to run the copy database
wizard successfully:
http://msdn2.microsoft.com/en-us/library/ms188664.aspx
*Required permissions
For the Copy Database Wizard to work, you must have the correct
permissions, depending on how you are copying the database, as follows:
For the Detach and Attach Method, you must be a member of the sysadmin
fixed server role on the both source and destination servers.
For the SMO Transfer method, you need to be a database owner for the source
database, and on the destination server you must either have been granted
the CREATE DATABASE permission or be a member of the dbcreator fixed server
role. *
For more information, please refer to above web site.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| |
| Allan Mitchell 2005-11-22, 8:24 pm |
| Sophie.
The issue is not with permissions. The original poster said he can txfr
the table fine but the default property on a column was not transferred
with the table.
I have also tried to take the default property with me on a txfr and I
am a sysadmin and couldn't
Allan
"Sophie Guo [MSFT]" <v-sguo@online.microsoft.com> wrote in message
news:vRpovt17FHA.1240@TK2MSFTNGXA02.phx.gbl:
> Hello,
>
> Please make sure you have the required permissions to run the copy
> database
> wizard successfully:
>
> http://msdn2.microsoft.com/en-us/library/ms188664.aspx
>
> *Required permissions
>
> For the Copy Database Wizard to work, you must have the correct
> permissions, depending on how you are copying the database, as follows:
>
> For the Detach and Attach Method, you must be a member of the sysadmin
> fixed server role on the both source and destination servers.
>
> For the SMO Transfer method, you need to be a database owner for the
> source
> database, and on the destination server you must either have been
> granted
> the CREATE DATABASE permission or be a member of the dbcreator fixed
> server
> role. *
>
> For more information, please refer to above web site.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> ====================
====================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
| |
| Sophie Guo [MSFT] 2005-11-23, 7:24 am |
| Hello,
I have tested the issue on my side again by using the SQL Management Object
method in the Copy Database wizard. Based on my test, when using the SQL
Management Object method to copy database, the Identity property of the
column is set to NO after copying. However, if using the Detach and Attach
Method, the Identity property of the column is set to Yes after copying.
I have forwarded this issue to the appropriate folks to catch their
immediate attention. They will investigate this issue. Thank you for all
your feedback. I believe that all customers' feedback will enhance our
products.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| |
| Eric Peterson 2005-11-30, 8:24 pm |
| If it means anything to you, I have experienced the exact same thing.
However, this is not an enhancement, this is a bug.
-Eric
"Sophie Guo [MSFT]" wrote:
> Hello,
>
> I have tested the issue on my side again by using the SQL Management Object
> method in the Copy Database wizard. Based on my test, when using the SQL
> Management Object method to copy database, the Identity property of the
> column is set to NO after copying. However, if using the Detach and Attach
> Method, the Identity property of the column is set to Yes after copying.
>
> I have forwarded this issue to the appropriate folks to catch their
> immediate attention. They will investigate this issue. Thank you for all
> your feedback. I believe that all customers' feedback will enhance our
> products.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> ====================
====================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>
|
|
|
|
|