January 30, 2009 at 7:41 pm
Hi,
We are rebuilding the system databases in clustered sql server 2005 to change the collation settings to Latin1_General_CI_AS_KS_WS to match with the sharepoint database collation settings.
I followed the link in BOL http://msdn.microsoft.com/en-us/library/ms144259(SQL.90).aspx#rebuilddatabase
Steps
1.start /wait
2.The installation went well and the collation has been changed to Latin1_General_CI_AS_KS_WS for system databases.
3.From the link, next step is to restore master,model and msdb databases.
4.Restoring master db:
.I put the sql server in single user mode(http://msdn.microsoft.com/en-us/library/ms345416(SQL.90).aspx)
.Used the sqlcmd command as below
.Open command prompt, it took me to C:\document and settings\admin>
.Then I entered sqlcmd -S sqldbavs1\ins1. But here Iam getting an error:msg 18461,level14,state1,server sqldbavs1\ins1.Login failed for the user abc\admin.Reason server is in single user mode.Only administrator can connect at this time.
5. C:\> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak' WITH REPLACE;
2> GO
The login Admin is box admin, has privileges.
Could you plz tell me what should I do at step4 to make it work.
January 31, 2009 at 4:12 pm
am i right in thinking you have rebuilt the system databases to a different collation but then trying to restore the original databases with the original collation?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 31, 2009 at 5:46 pm
Madhu,
Check out this URL:
It has a few recommendations for getting access to the single user connection that is allowed.
Two of the suggestions are:
1. Make sure SQL Agent is not running.
2. Make sure no SSMS windows are connected to the system.
Please let us know if that helps you out at all.
Thanks,
Steve
January 31, 2009 at 9:02 pm
As BOL says that to change the collation, first take the backups of current system dbs(with collation x) and rebuild the system dbs with the new collation(y) and restore the system dbs from the backups we took earlier.This what I understood, correct me if iam wrong
February 1, 2009 at 3:34 am
dude madhu
BOL makes some assumptions here. Generally rebuild is used for corrupt installations of sql server and the restore of the system databases is standard routine, even then you may not want to do this.
Say your master database was corrupt and you rebuild then restore a backup which happens to be a backup of the corrupt master?
When changing collation you will need to re apply any fixes, updates, etc and if you moved the system databases (as most do) you will need to perform this operation as well. Rebuild puts your instance back to a virgin state. The system collation settings are held in the system databases so you shouldnt restore backups of the previous system
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply