I am trying to copy a database from a SQL 2000 SP3 Windows 2000 server
to a 2005 SP2 Windows 2003 server.
I am trying to use detach and attach method (have tried both within
Management Studio and T-SQL) and experience an error when attaching to
the 2005 server:
"CREATE FILE encountered operating system error 5 (error not found)
while attempting to open or create the physical file
'xxxxxxxxxxxx' (Microsoft SQL Server, Error: 5123)"
I have specified the correct file, location, etc. Permissions look ok.
I looked further into this and heres the answer as far as I
understand:
To move a database from 2000 to 2005 you CANNOT detach/attach. You
must backup/restore.
Even ensuring the file locations are exactly the same on both source
and destination does not work for detach/attach.
You can only detach/attach within the same version itself, i.e. within
2005 itself.
The following KB states:
"If you are using SQL Server 2005, you can only attach databases of
SQL Server 2005 to an instance."
http://support.microsoft.com/kb/224071/
I know I can use backup/restore but I wanted to avoid this because for
large databases there is more overhead (i.e. the backup file size, and
time taken to backup and restore, compared to detaching/attaching
which takes seconds).
So as far as I understand the above is true, and in my opinion its a
bit limiting that this has not been allowed. Any further comments or
perhaps anyone who proves me wrong (!) are very welcome.
