August 7, 2010 at 8:31 pm
Hi there,
I have database restoring (Attaching) issues after I upgraded the DB from SQL 2000 to SQL 2005.
Before upgrading I ran SQL upgrade advisor and solved all issues.I used in-place upgrade .
I did backup before upgrade (backup of user DB with SQL 2000) and completed all points that need to be done before upgrade including detaching user database.
The upgrade went successfully and I could restore (Attach) some databases but one database can't be attached (restored) and this is a critical one that I really need.I can't attach this DB as I can't find the MDF file
I checked the MDF and LDF files and to my surprise I found that there is a .DAT file which should be MDF file and a LDF file which is LOG file.
I googled and couldn't find any solution to restore my DB.Is there any way I can recover this Database?
August 7, 2010 at 10:54 pm
Do i understand this correctly?
You
1. backed up the databases in SQL 2000
2. Detached the databases
3. Upgraded to SQL 2005
4. Are now trying to attach the databases
Do you still have the backups created? You can restore those backups (not attach).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 8, 2010 at 5:06 am
When you say you can't attach, did they give errors > If so what exactly was the error?
re the DAT and LOG files, the MDF and LDF extensions are not enforced. You can create a database with files with other extensions.
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
August 11, 2010 at 1:11 pm
Try and restore the backups on that machine. If that doesn't work, do you have another machine you can try and restore the backup onto?
Don't forget to change the compatibility level afterwards so the databases are actually 2005 ones since 2000 databases can exist on a 2005 instance, but still be 2000 databases. 🙂
August 11, 2010 at 1:36 pm
blackbird (8/11/2010)
Don't forget to change the compatibility level afterwards so the databases are actually 2005 ones since 2000 databases can exist on a 2005 instance, but still be 2000 databases. 🙂
All the compatibility level controls is how some T-SQL constructs are interpreted. A database attached to SQL 2005 is a SQL 2005 database in structure, regardless of compat level. It is possible to create a database on SQL 2005 and then change it's compatibility level down to 80 or even 70.
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
August 11, 2010 at 1:49 pm
I'm confused about what you did here. An attach is not a restore, so when you put them in parenthesis, it's not clear what you are trying to do.
Also, what files do you have? Do you have a backup file of this database from before the upgrade?
August 11, 2010 at 2:06 pm
Either this has been resolved or is not a pressing issue.
An update from the OP would be nice.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply