I have been using Microsoft Azure, SQL Virtual Machine (IaaS) for quite some time because few of my clients ask questions about them. My clients generally use images from gallery to deploy SQL Server. Once they realized that they want to change the SQL Server collation instance. I was contacted to know the steps to do that. I learned something new so sharing via the blog.
Caution: Rebuild of system databases is as good as the fresh installation of SQL Server. YOU WOULD LOSE ALL EXISTING INFORMATION so please backup the stuff, if needed.
Creating VM from gallery doesn’t provide the option to choose SQL collation. By default, they are installed with SQL_Latin1_General_CP1_CI_AS collation. If collation change is needed, we need to do that as the first thing after provisioning VM. This is to make sure the collation matches with your planned use.
As you may know that the changing collation of SQL Server instance needs REBUILD of system databases. System Databases should be rebuilt from the command prompt. We CANNOT specify one system databases (master, model, msdb) to be rebuilt. We can use either use
- Installation media
- From the folder “Setup Bootstrap” located with installation logs.
%programfiles%\Microsoft SQL Server\<Build>\Setup Bootstrap\<SQLVersionfolder>
For SQL Server 2014 server, it is
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014
On Azure VM the SQL installation media is located on C Drive. My VM is SQL Server 2016.
And there is the command
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=”BUILTIN\Administrators” /SAPWD= A@$tr0ngP@$$w0rd /SQLCOLLATION=Japanese_Bushu_Kakusu_100_CS_AI
You need to replace parameter values based on your requirement. I have run that from cached folder not media and that also worked.
In short, you don’t need to have a media for rebuilding the system database. It takes the files from Template folder and re-deploy SQL Server with desired instance.
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
Hello Pinal sir,
I am fetching problem when restoring database using script, there i found that issue is with logical file name.
I created database with name TEST_1 and restoring database using bak file name of TEST_0.bak file,
It is bak of database TEST_0.
First i restored it with manual process in database TEST_1 it was successfully restored,but TEST_1 logical file name changed with TEST_0 and TEST_0_log.
Second I have bak file of database TEST_2 (TEST_2.bak), i try to restore in database TEST_1 with script,
here logical file name is different it’s show me below error
Msg 3234, Level 16, State 2, Line 3
Logical file ‘TEST_2’ is not part of database ‘TEST_2’. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Thank you once again, Pinal!
You’re the best!
Thank you – Worked first time against a new VM instance