TABLE OF CONTENTS
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.
- 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.
- Ask all users to close ManagementStudio client
- Stop the ManagementStudio IIS website
- Take a full backup of the ManagementStudio database
- Right click on the database to be transferred and click "Tasks\Export Data-tier Application...."
- Click Next on the Introduction Screen
- Select "Save to Local Disk" and browse to the location you wish to save this file to. Name the file ManagementStudio.Bacpac
- Click Next and make sure the Summary screen is correct
- Click Finish and wait for the Bacpac file to be created
- If there are any errors, make sure you are using the latest version of SQL Server Management Studio
- Navigate to the downloaded "ManagementStudio.bacpac" file and open it with WinZip or 7-Zip (do not extract)
- Copy the file "model.xml" to the same folder as the bacpac file
- Edit the "model.xml" file with the desired collation (i.e the same as the target server):
- 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:
- Using CMD, CD to the directory C:\Program Files\Microsoft SQL Server\150\DAC\bin
- 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
- 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
- Once complete, give the ManagementStudio Service Account dbOwner priviledges to the database
- 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
- This completes the move to a new SQL server with a different collation
If you require further support, please visit ManagementStudio's Service Desk to search the knowledge base or create a new support ticket.