March 8, 2002 at 3:57 pm
I accidentally delete all the stored procedures in the master database and I were not make any backup and copies of this database. Now it does not allow me to login.
How can I do?
March 8, 2002 at 4:07 pm
You deleted all, wow I didn't think you could destroy sp_login and such with turning on access to system objects. I don't believe you can properly readd those objects and will need to rebuild the master. Sorry I have never needed as of yet and cannot rememeber the details but there have been several recent post to have someone do this, just search for rebuild AND master.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 8, 2002 at 4:12 pm
How to do a search in the Discussion forum?
I did not see any search function in this site?
March 8, 2002 at 4:25 pm
It is the last item on the top blue menu bar. However just look for rebuildm.exe in SQL Books Online, you will want to be carefull and I suggest the following.
1) Stop current sql server
2) Copy all database files (.mdf, ndf, ldf) to a safe location exculding master and tempdb.
3) Follow the BOL statements on rebuildm.exe to rebuild the master.
4) Start the server when done and verify you can get in.
5) Stop the server and drop all the files copies you made back into the directory.
6) Restart and msdb and model should come up fine with all the DTS packages and jobs you orignally created.
7) Use sp_attach_db to reattach all the old databases to the new master.
8) Rebuild logins and you may need to run a few stored procedures for cleanup.
9) After you verify everything seems back in order backup you master DB and do this everyday rain or shine especially after changes that effect it.
10) Hopefully someone else will add in here anything I should correct as I have again never needed this (knock on wood because I have gone days without a master backup).
11) Also check other references before you jump here cause this means a lot can happen or be lost.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 8, 2002 at 4:31 pm
March 8, 2002 at 4:46 pm
One additional note, it's not necessary to copy the mdf/ldf files, just rename the folder. Once you've run rebuildm and are running, you can move all the files back into the data folder. I've got an article here on the site about how to reattach a LOT of db's, might save you some time.
Andy
March 8, 2002 at 5:08 pm
Gentlemen:
Thank you for the asistances.
I still have problem to identify the search function. Nothing is after "Contest" in the top navg. bar.
Andy, do you mind to send me the URL which you talk about reattaching a lot of dbs?
Thank you all.
March 8, 2002 at 7:13 pm
No problem at all, here it is:
http://www.sqlservercentral.com/columnists/awarren/reattachingdatabases-somecodeandacontest!.asp
Not sure why you're not seeing search, try this link:
http://www.sqlservercentral.com/Search/fulltextsearch.asp
Andy
March 11, 2002 at 1:02 pm
Anyone knows how long does the rebuild take?
I tried it last Friday. It only take 1 minutes for the rebuildm.exe to copy all the data files for master, msdb, tempdb, etc. But then it takes this process forever to re-configure the system. I just want to get a sense that if it is normal or something is wrong. Anyone knows how long does it take to rebuild? Thanks.
March 11, 2002 at 1:04 pm
Pretty quick I think. You can run into problems if the files you're trying to use are from the CD...are read only.
Andy
March 11, 2002 at 1:06 pm
Sorry I have never needed (knock on wood) to do this. I would think no too very long considering I believe it more or less just puts a new copy of master, msdb, model in place.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply