June 24, 2008 at 1:55 pm
Hi. Been looking for an answer on this forum but couldnt get it, so im starting this new topic. Dont ask me why he did that, cuz i dont know 😛
A friend of mine did this with a SQL 2000 server
1. Burnd all the mdf and ldf files from SQL 2000 instalation folder
2. Burnd also all mdf and ldf files from instance folder
3. Formatd the server and resintall Windows Server 2003
4. Installd SQL 2005
We both(my friend and i) are just starting with this SQL things, and we are trying to restore all databases and instances with this mdf and ldf files. Is there a way to do it??? Thanks in advance 😀
June 24, 2008 at 2:01 pm
use sp_attach_db
June 24, 2008 at 2:30 pm
Or basic approach will be to use the Attach wizard available when you right click on +databases then click on Attach.
Look very closely to the File names and paths, they can get trickier..
After you attach the Database, make sure you change the compatibility level to 90 and run DBCC UPDATEUSAGE(DBNAME) then DBCC CHECKDB(DBNAME)
Maninder
www.dbanation.com
June 24, 2008 at 3:49 pm
use sp_attach_db
Or basic approach will be to use the Attach wizard available when you right click on +databases then click on Attach.
Look very closely to the File names and paths, they can get trickier..
After you attach the Database, make sure you change the compatibility level to 90 and run DBCC UPDATEUSAGE(DBNAME) then DBCC CHECKDB(DBNAME)
Tnx for the response, but i dont really now whats all that, so i better start reading. Tnx again
///Edit
Well... i think im getting it...
But here is another question.
What should i attach?
Instances??? the once took from Local?? both??
should i treat instances and the others as the same??
because as i said before, he took some mdf's from MSSQL/DATA folder and some others from Instance/DATA folder.
There is a master DB on instances and another on MSSQL/DATA.
Should i atach both?
Im so sorry if i askd so many questions but im trully lost :crying:
and thnx for your time 😀
June 25, 2008 at 7:03 am
Seems you had mutliple instances running on the same server (perfectly acceptable), the default and at least one named instance. Which master database to attach? Why not use the one from your clean install of SQL2005? (btw - you cannot attach both, at least not as master) Since you've got a fresh install, the only thing you may be missing is logins but as it seems you're "playing", so don't worry about them for now. Attach your user databases using sp_attach_db and create any needed logins. If the login already exists as users in the databases you will need to execute sp_change_users_login
'AUTO_FIX', 'username' to sync up orphaned users. Use BOL to lookup anything you need to know, it's a great tool. Have fun and learn all you can. There are tons of resources available.
-- You can't be late until you show up.
June 25, 2008 at 10:07 am
Ok.
i didnt Attachd the master file. i did attach all the user databases with no problems, there are not orphanes. i executed this
USE mibase
GO
EXEC sp_change_users_login 'report'
and no users shown as orphan, and i can now open all the databases, tnx to all the help guys. Do i still need to do this AUTO_FIX and sp_change_users_login thing??
but now, i have this Instances folder with a bounch of mdf files (Backup files from SQL 2000),
should i attach this files same way i attachd the user db's files???
should i place this instance mdf files in MSSQL/Data folder to attach them??
Tnx again
June 25, 2008 at 10:44 am
EXEC sp_change_users_login 'report'
and no users shown as orphan, and i can now open all the databases, tnx to all the help guys. Do i still need to do this AUTO_FIX and sp_change_users_login thing?? - Nope, you should be fine
but now, i have this Instances folder with a bounch of mdf files (Backup files from SQL 2000),
should i attach this files same way i attachd the user db's files??? - If you want or need them, yes
should i place this instance mdf files in MSSQL/Data folder to attach them?? - You can. All depends on what you want to do. If you have free physical disks on the machine, for a performance boost, maybe put them there. All in all, it sounds like you're on your way...
-- You can't be late until you show up.
June 25, 2008 at 11:34 am
OMG
thank you soo much, is there a way i can help you?? 😀
i really apreatiet your help. This is a great forum with awesome ppl.
Cheers for that.
And here is my last question about this topic (hopefully).
Just making sure...
As i said...
i backed up two folders from SQL 2000:
1. MSSQL/data
2. Instances/data
both with mdf's and ldf files.
Now with 2005 installed, i gotta merge this files into MSSQL.1\MSSQL\Data and attach them all the same way(all from instances and MSSQL1)??
June 25, 2008 at 11:47 am
As long as the mdf/ldf files all have unique names, you can merge the files into one folder and then attach the databases the same way you did the others. You know how you can help me? Learn as much as you can and come back and contribute here, wherever possible. It is a great site and many knowledgable, helpful people willing to lend a hand.
-- You can't be late until you show up.
June 25, 2008 at 12:00 pm
tosscrosby (6/25/2008)
You know how you can help me? Learn as much as you can and come back and contribute here, wherever possible.
Deal!
Ill try to learn as much as posible, and stay on forums. Tnx again for everything
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply