How to Import an MS SQL Server Database Into Plesk For Web Application Connection
Here Are The Steps To Import Your MS SQL Server Database Into Your Plesk Windows Hosting Plan
For those of you that have an MS SQL Server Database inside Plesk already it is easy to do an export dump (file) and import dump of the same file. The difficulty comes when the MS SQL Server Database you have is currently in your MS SQL Studio Management Tool on your desktop or laptop. Also when the administration security settings in Plesk has been turned on to force a naming convention for the database and username in your Plesk screen, this can mean your new Plesk database name will likely not match to your database name in your MS SQL Studio Management Tool. You have to have the MS SQL Server Database names match up exactly the same or you will get an error when importing your MS SQL Server Database into Plesk.
Let’s say your database on your MS SQL Studio Management Tool is called: TestDB. The first step is to login to your Plesk hosting account and click on the Database menu and click on the Add Database button. It will ask for the new database name, username and password and the server connection. The server connection will be .\MSSQLSERVER2012 or .\MSSQLSERVER2014 or .\MSSQLSERVER2016 etc. depending on what your hosting provider has set up for you for MS SQL Server Database choices for your hosting plan. (Note for Plesk administrators: more than one instance of MS SQL Server can be set up in Plesk administration but you can only assign one MS SQL Server instance to one Plesk hosting plan in the Web Hosting Parameters tab.)
If security is not turned on for naming conventions you simply add the new Plesk database name of TestDB matching exactly to your MS SQL Server Database name you want to import. However, most hosting providers have security turned on meaning the name of the database has to start with your username such as usernam1_ so this means a mismatch in database names which we will address a few paragraphs down.
When adding a new database shell in Plesk, record your DB name, username, password and server connection such as .\MSSQLSERVER2016 as you will need that for your DB connection string for your web application. Look closely at the server connection name: .\MSSQLSERVER2016 name as you will need the correct version of the MS SQL Studio Management Tool that will create the right level of .bak file (MS SQL DB Backup File) for MS SQL Server Database imports into Plesk. For example, if your server connection is .\MSSQLSERVER2012, you cannot use the MS SQL 2014 Studio Management Tool or the MS SQL 2016 Studio Management Tool to create the MS SQL Server Database .bak backup file or you will get an error when importing it into Plesk. On the other hand, if your server connection is .\MSSQLSERVER2016 then you can use the MS SQL 2012 Studio Management Tool or the MS SQL 2014 Studio Management Tool or the MS SQL 2016 Studio Management Tool to create the MS SQL DB .bak backup file.
Does the Size of your MS SQL Server Database Log File Matter?
If your log file of your MS SQL Server database is over 200 MB your will get an error importing it into Plesk as Plesk allows a maximum of 200 MB for the size of your log file. (Remember Plesk is using the MS SQL Express version of MS SQL Server). You can shrink your log file on your TestDB database with this SQL command in your MS SQL Studio Management Tool:
DBCC SHRINKFILE(TestDB database_log, 1)
which shrinks the log file to 1 MB.
Creating An MS SQL Server Database Name that Matches your Plesk MS SQL Database Name
The next step in MS SQL Studio Management Tool if your database names are not matching, is to create a new database called: usernam1_TestDB meaning the same name of the new database shell you created in Plesk. Then right click on that usernam1_TestDB database in your MS SQL Studio Management Tool and do a restore from your TestDB database as the source and set the username_TestDB database as the destination for the restore. Make sure when restoring that you set the filename to match to usernam1_TestDB for the mdb and log files by clicking on the Files menu options in the restore pop up window.
Creating an MS SQL Server Database for Plesk Import(with correct Tool and a log file under 200 MB)
Depending on if your database name in Plesk is TestDB or the secure naming convention of usernam1_TestDB, right click on one of those databases in your MS SQL Studio Management Tool and select the backup option and create a .bak file on your laptop or desktop. Then open up your Plesk browser to the database section you were on in the above steps. Click on the Import Dump icon and it will open up a new dialog pop up. Click on the Browse button and point it to your .bak file (MS SQL DB backup file) on your desktop or laptop and click OK to start the import. On the lower right of your screen you will get an import success or failure message. Read the failure message as it could be issues mentioned above.
How to View your tables on your Imported MS SQL Server Database in Plesk
Your service provider should have turned on a tool called My Little Admin and if so, you will see that icon in the same Plesk database screen we have been talking about above. Click on that My Little Admin icon and it will open up a new browser tab. At the left of the My Little Admin screen, click on the USER_DATABASES menu option and then click on your database to see if all your tables and objects have been imported correctly. In the lower part of the left menu is a bar called TOOLS. Click on TOOLS and then click on the New_query link to get an SQL browse window to view and run SQL commands.
Can you Import an MS SQL Server Database from an SQL Script you created in the MS SQL Studio Management Tool?
Yes you can create an SQL script in MS SQL Studio Management Tool using the Generate script option and remembering to turn on the DATA AND SCHEMA options for the data and structure of your MS SQL database. However you still need to know how to massage this SQL script output taking out the create DB calls at the top and renaming the MS SQL Server Database to match the new MS SQL Server Database shell you created in Plesk. After massaging the SQL script, copy and paste the script into a New_query window explained above using the My Little Admin tool and execute the SQL script. We have found for large MS SQL Server database imports, this SQL scripting importing technique does not work very well so we recommend using the MS SQL DB import from .bak file technique mentioned above.
How to Connect your ASP.NET Web application to your Imported MS SQL Server Database in Plesk
In Plesk in the same database screens we have been taking about above, there is a connection Info icon so click on that icon and you will get the 4 pieces of info that need to be in your ASP.NET DB connection string. In the steps above we told you to record the database password so add that DB password and the info in the Plesk database info pop up screen to your ASP.NET DB connection string. One catch is the server name needs an extra \. For example, if your server name in your DB connection string is “.\MSSQLSERVER2016” your DB connection will fail. If you change that DB connection string section to “.\\MSSQLSERVER2016” then it should work.
How We Can Help
Sozo Hosting has been a leader in hosting solutions since 2001 and can assist you in your windows shared hosting needs when you sign up on this link https://sozohosting.com/shared-hosting/windows-hosting/ . For those that need a windows server please click on this link: Windows Cloud Server. For larger operations, many of our clients run Microsoft SQL Server databases on a Windows Cloud Server or even have a Windows Cloud Server for their for their front-end applications which communicates to a separate Windows Cloud Server running their Microsoft SQL Server databases. Please click on this contact us link for more information.