TABLE OF CONTENTS

• Overview
• Prerequistites
• Process
• Further Support

Overview

When moving a ManagementStudio database to a new server, the SQL Collation of that server must be the same. If it is not, then ManagementStudio will not be able to update the database during a maintenance update.

The collation of the Database can be found by right clicking on the database in SQL Server ManagementStudio:

The collation of the Server can be found similarly by right clicking on the server at the root of SQL Server Management Studio and selecting properties.

Prerequistites

• Ensure that the SQL Server version used on the new host is same version or later than the current SQL server
• You will need SQL Server Management Studio with access to the ManagementStudio database
• You will need to know the name of the Management Studio Service Account
• You will need an account with full SQL privileges for making backups, restoring databases etc on both the current SQL server and the new one
• You will need access to the ManagementStudio web server with local admin privileges
• Ensure you have a modern version of sqlpackage (Microsoft SQL Server Data-Tier Application Framework) that support the /ModelFilePath parameter found here
• Sqlpackage installs to the C:\Program Files\Microsoft SQL Server\150\DAC\bin directory.

Process

1. Ask all users to close ManagementStudio client
2. Stop the ManagementStudio IIS website
3. Take a full backup of the ManagementStudio database
4. Right click on the database to be transferred and click "Tasks\Export Data-tier Application...."
5. Click Next on the Introduction Screen
6. Select "Save to Local Disk" and browse to the location you wish to save this file to. Name the file ManagementStudio.Bacpac
7. Click Next and make sure the Summary screen is correct
8. Click Finish and wait for the Bacpac file to be created
9. If there are any errors, make sure you are using the latest version of SQL Server Management Studio
10. Navigate to the downloaded "ManagementStudio.bacpac" file and open it with WinZip or 7-Zip (do not extract)
11. Copy the file "model.xml" to the same folder as the bacpac file
12. Edit the "model.xml" file with the desired collation (i.e the same as the target server):
13. Run the database import using sqlpackage.exe that you downloaded in the prerequisites. You wil need to use a high level account such as Sa or a windows account with enough access on the server:
14. Using CMD, CD to the directory C:\Program Files\Microsoft SQL Server\150\DAC\bin 
15. Run the following command: sqlpackage.exe /Action:Import /tsn:[server] /tdn:[database] /tu:[user] /tp:[password] /sf:"C:\Temp\ManagementStudio.bacpac" /ModelFilePath:C:\Temp\model.xml
16. the[server], [database], [user] and [password] will need to be changed. The assumption is that the [database] will be ManagementStudio the same as the old one. I've also assumed that the bacpac and model.xml files were save in c:\Temp
17. Once complete, give the ManagementStudio Service Account dbOwner priviledges to the database
18. The appsettings.json file on the IIS server will need modifying to point to the new database server. This should be undertaken by the ManagementStudio support staff
19. This completes the move to a new SQL server with a different collation

Further Support

If you require further support, please visit ManagementStudio's Service Desk to search the knowledge base or create a new support ticket.