December 11, 2008 at 4:37 pm
Hi all,
I am attempting to restore a number of SQL Server 2000 databases to SQL Server 2005 as part of an upgrade. Both versions of SQL Server are on different physical servers. I perform the backup on the 2000 server and copy the backup files to the 2005 server. I pre-built the databases on the 2005 server and attempt to restore to them. I use the database option and from device, I selected the 'Overwrite existing database option' and set the correct directory path in the option tab. I start the restore and it runs for awhile when it reaches 100% I get the following message:
System.Data.SQLClient.SQLError: Restore could not start database 'CO20'
The database is then put in 'suspect' mode. Not sure what I am doing wrong, has anyone run into this problem before? I know I can do a detach and attach to go from 2000 to 2005 but I cannot stop the donner databases, I have to do this with a restore (if possible) Any help will be greatly appreciated.
Ken
December 11, 2008 at 9:35 pm
have you tried to do it through TSQL using WITH REPLACE?
You might also want to look at RESTORE VERIFYONLY AND RESTORE FILELISTONLY.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
December 11, 2008 at 9:40 pm
[font="Verdana"]
If your databases are not too big, use the copy database wizard from 2005 instance and instead of attach/detach method, select the 'SQL-DMO' method, on the next screen you can select the objects to be copied including logins. It will create the database on the 2005 instance and copy the database and selected objects, while the 2000 db is still online.
HTH 🙂
Regards,
[/font]
December 11, 2008 at 10:53 pm
If restore is not working do detach and attach database.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
December 11, 2008 at 10:56 pm
I mean you can restore database in Development SQL Server 2000 and detach it from there and attach to new server SQL 2005.
I know this route is longer but as you do have limitation of downtime this is alternate. 😉
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
December 12, 2008 at 1:09 am
I'd suggest running an integrity check on the SQL 2000 database (on SQL 2000)
Run CheckDB with No_infomsgs and all_errormsgs and also run CheckCatalog with no_infomsgs and all_errormsgs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2008 at 5:14 am
can you post the complete error message?
aside Gails recommendation, why pre create the databases before restoring the backups.
detach, copy and attach would be my preference. Drop the existing databases you created and try restoring again (ensure you set the db file paths correctly too)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 17, 2008 at 12:32 pm
Thank you for all your replys and suggestions sorry it has taken me so long to get back. I attempted HTH's suggestion of using Database copy, but keep running into a problem, the copy fails, in the event viewer I get a message saying the following:
"The process cannot access the file 'E:\Data\InterchangeBTM.mdf because it is being used by another process". There is more but this is the pertenant part. So I set the destination database to single user mode and attempted the copy, and again it failed for the same reason. Not sure what I am doing wrong. I also attempted Perry' suggestion to drop the pre_created database and do the restore, which also failed for the reason I stated in my original post. I was asked for the full error, after looking through the event logs I found the following:"Warning User "sys" [principal_is=1641] in database AM20 has been renamed to "_ID_0x000000A4_RENAMED_FROM_SYS". "sys" is a reserved user or schema name in this version of SQL Server" This is just one message in the stack, but is the one which ultimately causes the restore to fail. By the way I don't have a user "SYS" in this database, so I don't know what is going on here. Again thanks for the suggestions and help.
Ken
December 17, 2008 at 12:54 pm
kwitzell (12/17/2008)
Thank you for all your replys and suggestions sorry it has taken me so long to get back. I attempted HTH's suggestion of using Database copy, but keep running into a problem, the copy fails, in the event viewer I get a message saying the following:The process cannot access the file 'E:\Data\InterchangeBTM.mdf because it is being used by another process".
How are you copying the database?
(by the way, HTH stands for "Hope That Helps". The poster in question's name is Hemanth)
I was asked for the full error, after looking through the event logs I found the following:"Warning User "sys" [principal_is=1641] in database AM20 has been renamed to "_ID_0x000000A4_RENAMED_FROM_SYS". "sys" is a reserved user or schema name in this version of SQL Server" This is just one message in the stack, but is the one which ultimately causes the restore to fail. By the way I don't have a user "SYS" in this database, so I don't know what is going on here.
Please connect to the DB in SQL 2000 and run the following query. What do you get?
select name from sysusers where uid = 1641
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2008 at 1:40 pm
Gail,
I ran the script and received no output (0 rows). Thanks for setting me straight about HTH (I guess duh!)
Ken
December 17, 2008 at 3:03 pm
This may sound convoluted but it's only to protect your production data....
Take the same DB "AM20" and restore over a SQL 2000 server as a different name "AM20_Convert". Fix the issue of using the reserved object name (sounds like a user SID) of "sys". Delete it if necessary. Then backup that DB "AM20_Convert" and take that .bak file and restore it on your SQL 2005 server. If that works then that's the process you need to take for a proper migration. Someone, a developer, needs to be told that they can no longer use that account "sys".
December 18, 2008 at 12:04 am
kwitzell (12/17/2008)
Gail,I ran the script and received no output (0 rows).
Ken
Is that in the AM20 database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2008 at 7:32 am
Gail,
Yes, that is for the AM20 database. I think I need to add some back ground; The AM20 database is one of about twelve databases used by a financial application, each database is used by a function of the financial application. I ran your script against all the other databases including master and received the same result. I am not sure why it is referencing a user named SYS. The only thing I can think of is, this application used to run on an Oracle database, and in Oracle, SYS is the primary admin user (much like SA in SQL Server, forgive me if you know this already!). I know it is a big stretch but perhaps somehow, someway this is from the old Oracle db? I really don't see how that would be possible but then one never knows! Anyway, thanks again for you help and I hope this helps.
Ken
December 18, 2008 at 4:01 pm
Gail,
I found the problem, seems when we moved the databases from Oracle to SQL Server, the SYS and another user SYSTEM came over but since they were not used were never created as logins on SQL Server. I have a script which finds orphan users and when I ran the script against the databases SYS and SYSTEM showed up as orhpaned! I deleted them using my script and the databases now attach in 2005 just fine (the databases would not attach in 2005 due to the same problem I encountered when attempting to do a restore). I am willing to bet I could do a restore now as well and will test that theory when I can. So I think I have this fixed. Thanks again for your help.
Ken
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply