May 14, 2009 at 7:59 am
So basically, I had to rebuild a server to change collation which basically means I couldn't restore master. I found out after the fact that I needed an extended stored procedure. So being brilliant I restored my master database as master_old and low and behold ... nothing. I can't get any code out of this database. Now I have heard of rumors of a resource database, but I don't know how to use that get data out or back it up. Any insight on how to get objects out of a master database would be great like user credentials (like in 2000) I can't do that either unless I know before the server blows up and run a script to script them out.
May 15, 2009 at 6:26 pm
resource db is a read only DB and it doesnt hold any data other than the schema defination.
i am quite surprised that you said that you ar enot able to script after attaching as a user DB .
are you getting any error
send a screenshot . that will help
May 17, 2009 at 3:57 am
Could you have just set the collation at a database level?
Changing the default collation for an instance of SQL Server 2005 can be a complex operation and involves the following steps:
1. Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
2. Export all your data using a tool such as bulk copy.
3. Drop all the user databases.
4. Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
5. Create all the databases and all the objects in them.
6. Import all your data.
May 17, 2009 at 10:23 am
Actually, the databases that were currently on the system were the correct collation, however, the system was at a different collation (this was due to a restore of the databases from another server.) However, the person that reinstalled SQL didn't know to use something other than default collation thus making me go through the process of reinstalling. So I got everything out that I thought I needed, however I missed one XP that is in master. So I though I will be super smart and restore the old master database as another name script it out and script it back in to the current master database, done and over with. Yeah XP don't show up when you browse for them and I didn't think to see if I could see it, but since it is a master type code, I doubt it would have shown up anyway. So the real question is where is that so I can script it out in the future?
May 17, 2009 at 10:36 am
XPs call DLLs. What you would do is copy the DLL to the system and then register it as an extended stored procedure. There's no scripting to do here.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 18, 2009 at 6:05 am
whats is the name of the xp which is missing? is it msshipped XP?
May 18, 2009 at 3:11 pm
No it wasn't a MS XP, it was a user created one. This would also assume the DBA in charge would have knowledge of all dll's being added to the system and knowledge of what parameters would be passed to the dll through the XP. While I get that you all assume this is a simple (which it is) there is more to it than just copying the dll and creating a simple xp to call it. So the question still remains how the heck to do you retrieve this info once you have restored master under a different name.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply