Newest KB Articles in Databases

How to transfer your local SQL database to a WinHost SQL database

Sat, 31 Mar 2012 00:47:26 GMT

To transfer your local SQL database to a WinHost database, you will need to:

1. Backup your local database with Management Studio
2. Upload the backup of your database with .BAK extension to your web site via ftp
3. Use restore tools in WinHost control panel to restore the backup to your WinHost database

Please use Microsoft Book Online for the instructions on how to backup your local database or see this community forum post for instructions.

Important notes: 1) Microsoft SQL databases cannot be attached or restored to previous versions of SQL Servers. That means that SQL Server 2008 R2 database can only be attached/restored to R2 (or later), etc. 2) SQL 2000 databases cannot be attached/restored to a SQL 2012 database. If you need to attach/restore a SQL 2000 database to SQL 2012, you will first have to convert the SQL 2000 database to SQL 2005 or later. 3) Restore backup is available on all WinHost plans, but the attach tool is a premium feature that is only available on the WinHost Ultimate plan.

After you have backed up your local database and uploaded the backup file with .BAK extension to your web site, please follow the instructions below to restore the backup to your database on the WinHost SQL Server:

Upload the backup file with .bak extension you want to restore to your site via ftp
Log in to the WinHost Control Panel at https://cp.winhost.com
In the navigation menu click the "Sites" tab
In "Your Sites" section click "Manage" link next to the site you'd like to manage
In the "Site Tools" section click on "MS SQL Manager" icon
Click the "Manage" link next to the database you'd like restore your backup to
Click the "Restore" button next to the "Tools" label
Click the "Browse" link next to the "Backup File to Restore" text box and locate your backup file on your site
When the .bak file is located, click on it and click the "Select" button
Click the "Restore Backup" button

Getting started with an MS SQL database

Sat, 31 Mar 2012 00:04:46 GMT

1. Access the Manage Site Info page.

2. Click the MS SQL Manager icon.

3. If no database is present, click the "Add" button.

3. Enter the database name and quota assignment and click "Create."

I get a "Filegroup is full" error when attempting to run a SQL query

Fri, 30 Mar 2012 23:42:29 GMT

When attempting to run an SQL query, an error similar to the following is returned:

Could not allocate space for object 'xxxxx' in database 'SQL2012_xxxxx_xxx' because the 'PRIMARY' filegroup is full. The statement has been terminated.

This error indicates that your database size has exceeded the allocated amount. To increase the amount of space allocated to your database:

Go to the MS SQL tool in Control Panel
Click the "Manage" link for the appropriate database
Click "Edit" in the Assigned Quota line
Enter a value larger than the Assigned Quota in the New Quota field
Click the "Update" button

NOTE: If you are on the WinHost Basic plan, you have a maximum database quota of 200mb. In order to use more database space you must upgrade to he WinHost Max plan, which provides 1000mb of database space (and up to 10 SQL databases).

How do I generate (or restore) a backup of my MS SQL database?

Fri, 30 Mar 2012 23:37:36 GMT

You can generate a backup of your database that will be placed into your account and made available for download via FTP. The file created will have a .bak extension, and can be used for maintaining safety copies of your database or for use in your local development environment. Note however that the backup files are intended for restoration to our servers, and we cannot provide support for using the database backup on your local system.

To create a database backup

Go to the MS SQL section in Control Panel
Click the "Manage" link for the database you wish to back up
Click the "Backup" link

The backup file is created in the following format: [DatabaseName]_backup.bak
Backup will be placed in the /App_data directory (if /App_data does not exist, it will automatically be created)
The backup process will fail if a backup file with the same name already exists in the /App_data directory. You must rename or download any existing backups before creating a new backup.

How do I restore a backup file to my WinHost database?

Make sure the backup file is in the /App_data directory
and the name is in the following format: [DatabaseName]_backup.bak (restoration will fail if you attempt to restore a database that does not conform to this naming convention)

Go to the MS SQL section in Control Panel
Click the "Manage" link for the database you wish to back up
Click the "Restore" link

Note that the existing database will be overwritten and all existing data will be replaced!

The database restoration tool is intended to be used with backups generated in Control Panel only. If you create a backup on your local system and attempt to restore it to the live database the restoration may fail.

Attach Database Feature

NOTE: The Attach Database feature is only available if your account is on the Ultimate plan.

To use the Attach Database feature please refer to this Knowledge Base article.

Attach Database Tool

How to connect to SQL Server using SQL Server Management Studio

Tue, 27 Mar 2012 23:18:30 GMT

Before you attempt to connect, make sure you have created a database in Control Panel using the MS SQL Manager tool.

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

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 can be found in the Control Panel by navigating to Sites -> MS SQL Manager -> Manage]
    * Authentication: SQL Server Authentication
    * Login: [SQL database Login name]
    * Password: [SQL database Password]

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

Note: If you are planning to use one of the Contained Database users (SQL 2012 only) to log into your database you will need to specify the database.

Go to Options/Connection Properties
Go to Connect to database and input your database name.

How to change the collation of an MS SQL database

Wed, 31 Mar 2010 18:09:56 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 a single-user mode, change the collation, and set it back to a 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()

Do you support MS SQL Express databases?

Fri, 14 Aug 2009 22:02:28 GMT

SQL Express is designed for desktop database development purposes and is not suitable for a shared hosting environment due to security and performance issues. Microsoft does not recommend enabling this service on shared hosting platforms.