Changing the Default Collation When No User Databases are Present
UPDATED 13 April 2009: The SAPWD parameter sets the sa password; use BLANKSAPWD if you don’t wish to set one. I’ve updated the seventh paragraph below accordingly.
One of our responsibilities in the ITOE IP Architects group (the artists formerly known as the Exchange, SQL Server, and Windows Centers of Excellence) is to review the case reports from Critical Situation support cases submitted by Microsoft customers and determine if any of their content can be leveraged into new intellectual property for our programs. If we see lots of customers encountering similar issues – or even the potential for that to occur – we can research best practices around it and build a solution worthy of inclusion in one of our deliverables.
I don’t get to spend nearly as much time on this portion of my job as I’d like, but today I found both a potentially new issue and resolution, as well as an additional piece of information for an existing one. These will likely be built into the next version of our SQLRAP product, which is pretty cool.
I also found something which was pretty interesting, but which didn’t fit the profile of any of the programs I’m supporting.
In other words, I found a blog post. *g*
After installing SQL Server 2005 Enterprise Edition but before creating or attaching any user databases, our customer had one of those moments we all dread: they realized they had installed the server with the wrong default collation for their needs. They wondered whether there was an alternative to uninstalling and reinstalling the instance, and indeed there was:
start /wait setup.exe /qb VS=clustername INSTANCENAME=instancename REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CS_AS
The /qb switch supplies a set of basic dialog boxes and error messages. You can omit the VS parameter for non-clustered installations. INSTANCENAME would be MSSQLSERVER for the default instance, or your instance name. We’re REINSTALLing the SQL_Engine, REBUILDDATABASE is true, SAPWD is the sa password (where you wish to set one; use BLANKSAPWD to omit), and SQLCOLLATION is the new collation.
Remember, as far as I can tell right now this approach is only supported when there are no user databases attached to the instance. So think of this as an installation troubleshooting tip.
If I hear more about this technique (such has how to run it over a trusted connection, or support for its usage under additional scenarios), I’ll post an update.
-wp
this copyrighted material was originally posted at https://blogs.technet.com/wardpond.
the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.
the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.