Newest KB Articles in MS SQL

How to Backup Your MS SQL Database

Sun, 19 Mar 2023 21:50:18 GMT

Log into the DiscountASP.NET Control Panel
On the menu to your left under Database Management, click on the MS SQL Manager link.
Select your database and then click on the Manage button.
Select the SQL Tool Suite tab, choose either .zip or .bak format, then click on the Create Backup button.
You can either wait a while or check back later. When the backup is complete, it will show Completed as seen below.

If it shows an error instead, you can open up a support ticket to have us help you troubleshoot the issue. Most errors are due to insufficient web disk space. Make sure you have enough free space to hold the backup. A .zip file will consume less space than a .bak file. The database will also automatically be saved to the /_database or /App_Data folder if one or the other exists. If neither exists, the /_database folder will be created for you, and the backup will be placed in there.

Did you know that our Website Cloud Backup service can also back up your database? You have full control over backup retention and schedule. Automate your backups now with Website Cloud Backup.

How to Order a MS SQL Database

Sun, 06 Nov 2022 02:26:34 GMT

Log into the DiscountASP.NET Control Panel
On the menu to your left under Account Management, click on the Order Addons link.
On your right, a list of addon features will appear. Scroll down to where it says MS SQL. The version number is to right of it. Click on the Order MS SQL ####: Click Here link to start the ordering process.
The price is based on the amount of database disk space you want. Select the amount of disk space you want in the dropdown menu, give the database a name, and then click the Add to my Account button.
In most cases, your database service should be provisioned immediately. If it's not provisioned after 24 hours, please open a support ticket to have us investigate the issue. After it has been provisioned, click on the MS SQL Manager link under Database Management to manage your database. It's on the menu to your left in the Control Panel if you scroll down a bit.
If you have more than 1 database, you will be able to select the one you want to manage in the dropdown menu. Select the database you want to manage and then click on the Manage button.

The Management interface contains your database connection information, allows you to create database users (up to 3), and allows you to backup, restore, attach, shrink, and reset your database. These operations can only be done through the Control Panel and not through SQL Server Management Studio or any other management tool you decide to use as a client.

Did you know that our Website Cloud Backup service can also back up your database? You have full control over backup retention and schedule. Automate your backups now with Website Cloud Backup.

How to Restore an Azure Database (.bacpac file)

Sat, 14 May 2022 23:35:44 GMT

Azure databases (.bacpac files) cannot be restored from the Control Panel directly. In order to restore your Azure database, you'll need to perform the restore locally first. Make sure you've downloaded and installed the latest version of SQL Server and SQL Server Management Studio.

Launch SQL Server Management Studio and connect to your local SQL Server instance.
In the Object Explorer window, right click on Databases and select Import Data-tier Application...
Follow the wizard to completion.
Once the database has been restored locally, right click on the newly created database, select Tasks and then Back Up... to create a normal SQL backup (.bak file).
Upload the .bak file to your hosting account using an FTP client and then use the Control Panel to perform the restore.

Did you know that our Website Cloud Backup service can also back up your database? You have full control over backup retention and schedule. Automate your backups now with Website Cloud Backup.

How to Assign Additional SQL Logins to a SQL Database

Fri, 10 Jun 2016 22:36:52 GMT

How to assign an SQL login to a database:

Use the SQL Login Manager to create a new login in the MS SQL Manager section of the Control Panel.
Connect to the database using SQL Server Management Studio with your primary login (see this Knowledge Base article).
In Object Explorer, expand the Databases node.
Locate the database to which you want to add the new SQL Login and expand the node.
Expand the Security Node under your Database node.
Right click on the Users node and select "New User..."
In the Database User Properties
- Choose SQL user with login in the User Type drop down box.
- Type the username you want to assign to this login in the "User name" text box.
- Type the login you want to assign to the database in the "Login name" text box.
- Click 'Membership' in the 'Select page' column.
- Choose 'db_owner', for 'Role Members'.
Click OK.

Did you know that our Website Cloud Backup service can also back up your database? You have full control over backup retention and schedule. Automate your backups now with Website Cloud Backup.

How to Configure SQL Server Management Studio to Connect to a SQL Server

Fri, 10 Jun 2016 20:04:30 GMT

To connect to your SQL database using SQL Server Management Studio, follow the steps listed below.

You can find your database information on the Database Manager page of Control Panel.

Open SQL Server Management Studio, the "Connect to Server" window will open. Select/enter the following:

Server Type: Database Engine
Server Name: [The SQL server name]
Authentication: SQL Server Authentication
Login: [SQL Login name]
Password: [SQL Password]

Click "connect" and you will be connected to the SQL server.

Note: You will need to download and install SQL Server Management Studio to connect to a SQL database.

Did you know that our Website Cloud Backup service can also back up your database? You have full control over backup retention and schedule. Automate your backups now with Website Cloud Backup.

Changing the Default Database of a MS SQL Database Login

Fri, 10 Jun 2016 20:02:02 GMT

How to change the default database on a SQL Server login:

Grant the user with right to the database you want to set as the default database (see this Knowledge Base article)
Connect to the database using SQL Server Management Studio with your primary sql login
Click New Query Button
Execute the following command:

ALTER LOGIN [login name] WITH DEFAULT_DATABASE = [name of default database]

Replace [login name] and [name of default database] with the sql login name (primary or secondary) and database name.

Did you know that our Website Cloud Backup service can also back up your database? You have full control over backup retention and schedule. Automate your backups now with Website Cloud Backup.

Upgrading an MS SQL Database to a Newer Version

Wed, 15 Jul 2015 02:13:55 GMT

When you want to upgrade your SQL database to a newer version, we can migrate the database for you or you can do it yourself. Which option is best for you depends on your needs.

When we migrate a database for you:

We recommend that you first put your site into maintenance mode.
We will set the database to read only (to insure that no new data can be entered during migration) and make a backup.
Then we will create a new database on the SQL version of your choice and restore the database from the backup.
We will provide you with the new login information (database server name, login, etc.).
You will then update your application connection string to point to the new database server.
We will delete the old database, and the process will be complete.

When you migrate a database yourself, you have more control over the process of course, and you can keep both the old and new databases online concurrently during the migration which can be beneficial in certain circumstances.

The main drawback to self-migration is the need to order the new database, so you incur an additional cost during migration. Though you can get a credit applied to your account for the time remaining on the old database when you cancel it, you still essentially pay for two databases while both are active.

Typical steps of a self-migration:

Order a new database on the SQL version of your choice.
Make your old database read only (to insure that no new data can be entered during migration).
Make a backup of the old database and restore it to the new database.
Update your application connection string to point to the new database server.
Contact the billing department to cancel your old database.

We are still here to assist you with any help you need, even during a self-migration.

Databases from SQL 2008 and older cannot be directly restored to newer versions of SQL server. And so for those versions in particular we recommend having Support perform the restore / upgrade.

Did you know that our Website Cloud Backup service can also back up your database? You have full control over backup retention and schedule. Automate your backups now with Website Cloud Backup.

How to Import a CSV File into a Database using SQL Server Management Studio

Tue, 05 Nov 2013 19:15:22 GMT

While bulk copy and other bulk import options are not available on the SQL servers, you can import a CSV formatted file into your database using SQL Server Management Studio.

First, create a table in your database into which you will import the CSV file. After the table is created:

Log in to your database using SQL Server Management Studio.
Right click the database and select Tasks -> Import Data...
Click the Next > button.
For Data Source, select Flat File Source. Then use the Browse button to select the CSV file. Spend some time configuring the data import before clicking the Next > button.
For Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0). Enter the Server name, check Use SQL Server Authentication, enter the User name, Password, and Database before clicking the Next > button.
In the Select Source Tables and Views window, you can Edit Mappings before clicking the Next > button.
Check Run immediately and click the Next > button.
Click the Finish button to run the package.

How to Create Contained DB Users

Tue, 27 Mar 2012 04:49:17 GMT

These instructions are for MS SQL 2012 and later databases.

To create contained DB users:

Log in to the SQL server using SQL Server Management Studio. Use your primary SQL DB user.
Click "New Query" and run this SQL command against your database.

USE MASTER
GO
ALTER DATABASE [Database Name]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [Database Name]
SET CONTAINMENT  = PARTIAL
GO
ALTER DATABASE [Database Name]
SET MULTI_USER
GO

This will set the database containment type from "None" to "Partial". By default your database is set to "None."

Go to the Security folder under your database.
Right click the Security folder and choose "New User..."
Choose "SQL user with a password" for the User Type.
Enter the password for this contained DB User.
On the "Select a page" column click "Membership."
Click on the db_owner.

When logging into the SQL server using SQL Server Management Studio, you will need to ensure that default database connection is set to point to your database. If you leave it in its default state, SQL Server Management Studio will automatically try to authenticate to the Master database which will generate an error.

How to Change the Collation of a SQL Database

Tue, 30 Mar 2010 18:22:09 GMT

Note: While changing the collation, no users should be connected to the database.

In order to successfully change the database collation you will need to set your database to single-user mode, change the collation, and set it back to multi-user mode.

You can copy, paste, and run the following T-SQL script to achieve this:

SELECT name, collation_name FROM sys.databases; 
GO 
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
GO 
ALTER DATABASE [DatabaseName] COLLATE [CollationName]; 
GO 
ALTER DATABASE [DatabaseName] SET MULTI_USER; 
GO 
SELECT name, collation_name FROM sys.databases; 
GO

Replace [DatabaseName] with your actual database name and [CollationName] with your target collation name.

The query result window will display the collation names of your databases before and after the execution of the query.

You can see which collations are available on the server by executing the following statement:

SELECT * FROM fn_helpcollations()

Our Website Cloud Backup service can automate your database backups. You have full control over backup retention and schedule. DiscountASP.NET Website Cloud Backup - Everleap Website Cloud Backup