Copy 2000 database to 2005 - cannot detach/attach

[dpatel75]

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.

[Roy Harvey (SQL Server MVP)]

I have not tried this, but Books Online says it is possible.

In SQL Server 2005 Books Online (September 2007), on the page titled
How to: Upgrade a Database Using Detach and Attach (Transact-SQL), it
says in part: "In SQL Server 2005, you can use detach and attach to
upgrade a user database from SQL Server version 7.0 or SQL Server
2000."

Note that there are restrictions so you should read the whole article.
It includes step by step instructions.

Roy Harvey
Beacon Falls, CT

On Wed, 8 Oct 2008 03:08:14 -0700 (PDT), dp******@gmail.com wrote:

>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.

[dpatel75]

Thanks, I consulted the BOL article you mention and followed the
instructions.
Again I receive the error when trying to attach onto the 2005
database:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\MSSQL\DATA\IMKB_Data.MDF".
Operating system error 5: "5(error not found)".
I tried this using both the Management Studio and T-SQL (i.e. CREATE
DATABASE...FOR ATTACH).

I think there is something I am missing, e.g. permissions but can't
see it. For the moment I will use backup/restore.

[Tom van Stiphout]

On Wed, 8 Oct 2008 06:14:53 -0700 (PDT), dp******@gmail.com wrote:

Error 5 means file not found. You *are* attaching both the MDF and the
LDF in the same statement, right?

-Tom.

>Thanks, I consulted the BOL article you mention and followed the
instructions.
Again I receive the error when trying to attach onto the 2005
database:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\MSSQL\DATA\IMKB_Data.MDF".
Operating system error 5: "5(error not found)".
I tried this using both the Management Studio and T-SQL (i.e. CREATE
DATABASE...FOR ATTACH).

I think there is something I am missing, e.g. permissions but can't
see it. For the moment I will use backup/restore.

[dpatel75]

Yes I am attaching both files in the same statement:
CREATE DATABASE IMKB ON
(FILENAME = 'E:\MSSQL\DATA\IMKB_Data.MDF')
LOG ON (FILENAME = 'l:\mssql\logs\imkb_log.ldf')
FOR ATTACH

I have tried only attaching the mdf (because apparently a log file is
automatically created) but still get the error.
Btw none of the restrictions which are mentioned in the BOL article
above apply to my case.

[Roy Harvey (SQL Server MVP)]

On Wed, 08 Oct 2008 07:03:22 -0700, Tom van Stiphout
<to*************@cox.netwrote:

>On Wed, 8 Oct 2008 06:14:53 -0700 (PDT), dp******@gmail.com wrote:

Error 5 means file not found. You *are* attaching both the MDF and the
LDF in the same statement, right?

And if E: is not a physical drive on the server it will not work.

Roy Harvey
Beacon Falls, CT

[dpatel75]

Both the data and log filenames are correct, and both E and L drives
are local on the server.
I assume this is what you mean?

[Roy Harvey (SQL Server MVP)]

On Wed, 8 Oct 2008 08:04:07 -0700 (PDT), dp******@gmail.com wrote:

>Both the data and log filenames are correct, and both E and L drives
are local on the server.
I assume this is what you mean?

Yes that is what I meant. I think you have to get into permissions at
this point. Remember the account needing the permissions is the one
under which the SQL Server service runs.

Roy Harvey
Beacon Falls, CT

[Erland Sommarskog]

Tom van Stiphout (to*************@cox.net) writes:

Error 5 means file not found. You *are* attaching both the MDF and the
LDF in the same statement, right?

No. NET HELPMSG 5 will tell you: "Access is denied". File not found is
error 2. (And 3 means that the folder is wrong.)

So dpatel75 needs to look into permissions. I will have to admit that
I'm a little foggy, but I believe that:

* If you log in as sa, it is the permissions of the service account
that applies.
* If you log in with Windows Authentication, your own permissions applies.

But I could wrong there. It could also depend on whether you have
sysadmin rights or not.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

No. NET HELPMSG 5 will tell you: "Access is denied".

And "Access is denied" can mean either that that the file is exclusively
locked by another process or may also indicate a permissions issue. One
easy way to rule out another process is to move the file to another folder.
If that succeeds, it must be service account permissions.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

Tom van Stiphout (to*************@cox.net) writes:

>Error 5 means file not found. You *are* attaching both the MDF and the
LDF in the same statement, right?

No. NET HELPMSG 5 will tell you: "Access is denied". File not found is
error 2. (And 3 means that the folder is wrong.)

So dpatel75 needs to look into permissions. I will have to admit that
I'm a little foggy, but I believe that:

* If you log in as sa, it is the permissions of the service account
that applies.
* If you log in with Windows Authentication, your own permissions
applies.

But I could wrong there. It could also depend on whether you have
sysadmin rights or not.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinf...ons/books.mspx