October 7, 2008 at 3:41 am
Hi, 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 have scoured the internet for answers on this and they ask to check the things I've already checked.
Anyone copied a database using attach and detach from 2000 to 2005?
I know I can use backup and restore but attach detach is simpler for me as I am copying some large databases so with attach detach the backup/restore time is eliminated. I experience the same error when trying to detach attach a small database too.
October 7, 2008 at 4:05 am
Are you sure the path for the attached files is also available on the 2005 server? It seems it's using the original filelocation when attaching the file, which is a directory on your 2000 server
Wilfred
The best things in life are the simple things
October 7, 2008 at 10:51 am
why dont you just take a full backup and restore it in 2005.
October 8, 2008 at 3:37 am
Ok I looked further into this and heres some info (which will also answer your replies, thanks for those btw):
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 as I stated previously 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.
October 8, 2008 at 4:37 am
Hi,
I've done this lots of times and it works fine, so I was puzzled by the KB link/quote in your post. I've read it (twice) now and I think it's saying you can't move system databases between 2K and 2K5 with detach/attach - which would make sense considering how different they are. Apart from rechecking things you've checked, I don't know why it's failing for you - just wanted you to know it is possible.
Cath
October 8, 2008 at 4:52 am
Thanks Cath, interesting that you can detach/attach from 2K to 2K5.
I'll have to manage with backup/restore for the moment..
October 8, 2008 at 9:09 am
I would suggest checking the permissions of the SQL Server service startup account against the folders where the database files are located, and the file permissions itself. The description for operating system error 5 is 'access is denied'.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
October 8, 2008 at 1:10 pm
To move a database from 2000 to 2005 you CANNOT detach/attach. You must backup/restore.
This in not true for user databases (read the article, it's true for system databases). Upgrading to 2K5 is possible with detach/attach. When you attach a 2K db to a 2K5 server, a database upgrade is done automatically.
Wilfred
The best things in life are the simple things
October 9, 2008 at 2:48 am
Thanks to all for the replies.
This may indeed work, though I'll try again in future. Its probably a permissions thing as suggested.
October 9, 2008 at 7:06 am
Others experiencing your error found it to be a permissions issue
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=309967&SiteID=1
Attaching the MDF and LOG files from a 2000 instance to a 2005 instance can be done with the "for attach" option of "Create Database," something like this:
CREATE DATABASE DBName ON PRIMARY
(NAME = DBName, FILENAME = 'MDF_FileAndPath'),
(FILENAME = 'LDF_FileAndPath')
FOR ATTACH http://msdn.microsoft.com/en-us/library/ms176061(SQL.90).aspx
You may want to follow up with
DBCC UPDATEUSAGE ('DBName') WITH NO_INFOMSGS, COUNT_ROWS
EXEC SP_DBCMPTLEVEL DBName, 90
ALTER DATABASE DBName SET AUTO_CLOSE OFF
October 10, 2008 at 9:33 am
If you made a backup in sql 2000 you cannot restore it on sql 2005, only detach a sql 2000 and attach it on sql 2005
October 10, 2008 at 7:01 pm
If you made a backup in sql 2000 you cannot restore it on sql 2005...
Have you ever tried doing this for real, to validate that statement?
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
October 11, 2008 at 4:50 am
dp75 (10/8/2008)
To move a database from 2000 to 2005 you CANNOT detach/attach.
this is only true for the system databases. I use detach and restore all the time. When attaching the databases pay close attention to the location of the files and ensure the permissions are correct.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 11, 2008 at 1:02 pm
Perry Whittle (10/11/2008)
dp75 (10/8/2008)
To move a database from 2000 to 2005 you CANNOT detach/attach.this is only true for the system databases. I use detach and restore all the time. When attaching the databases pay close attention to the location of the files and ensure the permissions are correct.
yes, can you use this method.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply