Changing SQL Server System Collation without reinstalling

today we're going to look at probably the most common mistake that's made with sql server and that's when you do an install and you realize oh my god i've selected the wrong collision now there's a couple different options available to you um one is to completely reinstall the instance i do not recommend that as an option if you can avoid it and the others are to tell sql to change the collation now there are basically two methods i'm going to show you here so first one is going to require us to stop the instance in fact second one's also going to require us to stop the instance so first of all we stop the instance uh find the installation uh folder now this is something that exists primarily in 2016 i know for certain and onwards but i believe you can also do it in 2005 and 2012 which is you can basically use the setup.exe to rebuild the system databases so what we're going to do is say okay we need the setup.xe and we're going to tell it that we would like to change the following so we're going to tell it quiet action rebuild and the name of the instance then who we want to be the local administrator uh what else we're also going to add in the collation that we want to switch it to and we have the options here to also set the local sa password now since i prefer to use windows authentication rather than sa i'm going to take that switch out so i'm just going to get rid of that and then we can run that as a command and what this will do is just rebuild system databases so the master the model the msdb and tempdb although technically the tempdb you don't need to really rebuild it because once the system databases or other system databases have changed the temp db will get recreated on restarting the instance so it's kind of a the only one that doesn't really matter but it also will get changed anyway so uh this takes several minutes to run obviously and when it's done it will spit out the other end that the system databases have been recreated now there is one or two little downsides to this um obviously anything that you have in the master db like what system uh other than system databases i should say so what user databases are attached will no longer be attached simply because it doesn't remember them because it's a new systemdb uh same goes for sql logins any sql logins will be lost now keep in mind sql logins not ad logins so if you've got an id login an id login is set on the database you just need to add the ad login back to the instance and you can connect perfectly normally you've lost no user permissions because it'll be remembered at the database level but the from the point of view of sql logins because they're stored in the master db and you just told it to rebuild uh they're gone bye-bye and so unless you've created a backup of them first that's that's gonna be a really painful exercise but a you shouldn't be using sql logins anymore and b even if you were um you do have the option to always export them before you'd run this process so you can just re-import them and there's a couple of scripts out there on the internet that'll let you do that so we're just going to go ahead and manually start this instance by the way i noticed during the course of making this video that there is actually a little bit of a bug in management studio so the reconnect doesn't work very well but if i disconnect and reconnect within management studio it does actually work i just doesn't refresh for whatever reason maybe it's the buggy version of which i'm using here but this instance does start up normally now what we should be able to see is that the system collision now has changed to the one that we asked it and ta-da we have first way of changing system correlation now this took me what about three minutes three minutes thirty roughly to do so it's not as bad as reinstalling which can take 20 minutes minimum so that's a quick method let's say it's a quicker not quick but definitely quicker method now there's a second method which we're going to explore here so in this case we're going to go to the sql executable itself so this is stored in a slightly different place and so you've got the serv dot xe so we're just going to find it just to prove that it's there and what we're going to do here is run from that directory a different set of commands so here we're just going to close this out because we don't need this right now and we're going to stop the service before we do that so again same principle you need to stop the service now this time the difference is instead of rebuilding the system databases from scratch we're going to tell it to change the collation of the system databases and this is done using a couple of switches and this can be done from command line so if we go ahead and look for open a command prompt so we're just going to copy the path so i don't have to manually type it out i'm going to run this as administrator so i'm just going to cd to this directory and preferably i'm going to boost the size of the font here because obviously i don't think you'll be able to read it otherwise right so we have a nice big command prompt here so what we're going to do is we're going to say sql serve dash m dash t 40 22 t 36 59 then dash s and give it provided the instance name and then dash q and give it the collation so what this does is basically start sql server up in a single mode and allows it to then rebuild the system databases or change the system database collaboration i should say to what you've specified now in this method if you do have any user databases i recommend detaching them first to save yourself some time simply because it will try to reset all of the attached databases not just the system ones all of them so user databases too so if you don't want your user databases changed and or you just want to save some time detach them first that's the recommendation here so here we're just going to go ahead and run this now this is a relatively straightforward process and everything's going to output to the console and you can see it finished relatively quickly because we're just doing the systemdbs so i can now uh press the ctrl c to exit it and finish the process but just want to scroll up so you can see that the starting process went through and then it changed the the indexes and for basically all of the system dbs so there's no impact on anything else just those headsets so quick do you wish to shut down yes i do thank you very much so that's now stopped as a command line and we can go back to starting it as a service we can also then go back to management studio see that bug is still there so we're going to disconnect and reconnect lovely and yeah get another bug i wasn't using say anyway so that's fine but hey um what we can do is quickly connect and check what the status of those system dbs are now so no biggie here we will just go ahead now as you can see uh our instance is up and running and if we check the collation oh look we have the one we specified now the most common case which i probably should have mentioned at the beginning for doing this is when you install a non-case sensitive correlation and then suddenly go oh my god my application requires collation with case sensitive or occasionally you have some weird a wonderful app that uses some lovely binary correlation or something else that you just didn't realize when you started and go oh damn and you don't want to reinstall so hopefully you found this useful if you did you know what to do if not well you also know what to do

Channel: Tips For IT Pros

Views: 7,006

Rating: 5 out of 5

Keywords: sql server collation change, SQL Server System Collation, SQL Server default collation, Tip For IT Pros, sql server tutorial, SQL Server system database rebuild, dba tips, Sql server

Id: 1TTBkZ10JDQ

Length: 8min 8sec (488 seconds)

Published: Mon Aug 03 2020