WH4L

To export your SQL database to a .bak file you can use Microsoft SQL Server Management Studio Express. This is found in the start menu in:

• Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express
  OR
• Programs > Microsoft SQL Server 2008 > SQL Server Management Studio Express

If you don’t already have mssms installed then you can get it from here:
http://www.microsoft.com/express/sql/download/

Follow these steps to perform a backup:

1. Open SQL Server Management Studio Express
2. Connect to your database server
3. Right click on the database you want to back up
4. Select the Tasks menu item
5. Select the Backup menu item
6. In the Destination section of the Back Up Database window make sure you have selected Disk
7. Click the Add button and type in a location for the backup
8. Make sure you type the .bak on the end of the filename as it will not automatically add this in for you
9. Click OK and the database will be backed up to the location you specified

Note

If you are connecting to a remote sql server the .bak file can only be saved to the file system of the sql server. I do not know of a way around this limitation so you will need to access to the file system via ftp or some other method in order to move your backup to a secure offsite location.

You may have the question "What is the app_data realy for?" if you just entered into the web developement world as a newbie. Below if a brief introduction of  app_data folder. Please keep on reading and  you’ll get the answer.

App_Data allows you to use a file-based database.That includes not only SQL Server 2005 express and Access, but also XML files and Excel worksheets, among others, i.e., *any* file-based data store.

The real advantage is that any file you place in APP_Data won’t be downloadable
if a direct request for it is made over the wire, i.e., your data can’t be hijacked.

The content in all the special ASP.NET folders other than App_Themes is not be accessible via http requests to the server.

Webhost4life supports ms access DB. Sometimes when you use ms access DB with webhost4life you may get error saying "…must be updateable query…"

This usually happens when your .MDB file does not have WRITE permission.  To fix the problem,please login your hosting account at webhost4life and then go to File Permission under Security to modify file permission on the ms access DB file.  You will need to grant the user  IUSR_MEMBERID read,write and execute permission on the .mdb file.

If you still get the same error you’ll have to grant the user iusr_memberid read, write and execute permission on the ENTIRE folder that contains the .MDB file.

Check webhost4lfie hosting plan details

Webhost4life sql servers are using sql authentication instead of windows authentication so you will need to pay attentication in your DB connection string about this. See difference of windows authentication and sql authentication here

Here is  one sample DB connection string:

"Data Source=sql-server-IP;Initial Catalog=DB-name;User

Id=DB-user;Password=DB-password;Integrated Security=True"

It will not work with webhost4life because they don’t support windows authentication. You’ll need to change the value of "Integrated Security" to False in the connection string.

So the correct one is:

"Data Source=sql-server-IP;Initial Catalog=DB-name;User

Id=DB-user;Password=DB-password;Integrated Security=False"

If you are new to the Microsoft SQL Server environment, you probably encountered the possibility to choose between Windows Authentication and SQL Authentication.

SQL Authentication is the typical authentication used for various database systems, composed of a username and a password. Obviously, an instance of SQL Server can have multiple such user accounts (using SQL authentication) with different usernames and passwords. In shared servers where different users should have access to different databases, SQL authentication should be used. Also, when a client (remote computer) connects to an instance of SQL Server on other computer than the one on which the client is running, SQL Server authentication is needed. Even if you don’t define any SQL Server user accounts, at the time of installation a root account – sa – is added with the password you provided. Just like any SQL Server account, this can be used to log-in localy or remotely, however if an application is the one that does the log in, and it should have access to only one database, it’s strongly recommended that you don’t use the sa account, but create a new one with limited access. Overall, SQL authentication is the main authentication method to be used while the one we review below – Windows Authentication – is more of a convenience.

When you are accessing SQL Server from the same computer it is installed on, you shouldn’t be prompted to type in an username and password. And you are not, if you’re using Windows Authentication. With Windows Authentication, the SQL Server service already knows that someone is logged in into the operating system with the correct credentials, and it uses these credentials to allow the user into its databases. Of course, this works as long as the client resides on the same computer as the SQL Server, or as long as the connecting client matches the Windows credentials of the server. Windows Authentication is often used as a more convenient way to log-in into a SQL Server instance without typing a username and a password, however when more users are envolved, or remote connections are being established with the SQL Server, SQL authentication should be used.

If you are  hosting your website with webhost4life you’ll not be able to run aspnet_regsql.exe on their DB server because it requires system administrator’s permission to do that. But don’t worry about that because you can still do that on your own computer. Here are the instructions on how to do that.

First, the following requirements need to be configured on your computer:

1. Your home computer must have .net 2.0 installed
2. The port 1433 is open on your computer and able to connect target SQL server
3. You can provide correct SQL authentication login/password

Then, go to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\, double click aspnet_regsql.exe. Follow the wizard and complete the setup. This program is to create some tables and stored procedures to allow the membership control feature (for ASP.net 2.0) work. For more details of membership control, you may consult an .net programmer.

And also you can get more details of aspnet_regsql.exe on this site http://msdn.microsoft.com/en-us/library/ms229862(VS.80).aspx

How to connect MySQL DB with MySQL .NET connector at webhost4life? Here are the instructions you can follow up to do that:

There is no installation required on server,  you only need to upload the Mysql.Data.dll to the site "bin" folder. You can download the MySQL .NET connector at ‘http://dev.mysql.com/downloads/connector/net/6.0.html’. The MySQL.Data.dll locates on the "Installation path\Assemblies". Default path is "C:\Program Files\MySQL\MySQL Connector Net 6.0.3\Assemblies".

********************************************************************
[Sample .aspx]
<%@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">

Sub Page_Load(sender As Object, e As EventArgs)

    Dim myConnection  As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataSet     As DataSet

    Dim strSQL        As String
    Dim iRecordCount  As Integer

        myConnection = New MySqlConnection("Server=mysqlxxx.mysite4now.com;Database=DBname;Uid=DB_ID;Pwd=DB_pw")

        strSQL = "SELECT * From [table name];"

    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
    myDataSet = New Dataset()
        myDataAdapter.Fill(myDataSet, "[table name]")

    MySQLDataGrid.DataSource = myDataSet
    MySQLDataGrid.DataBind()

End Sub

</script>

<html>
<head>
<title>MySQL test</title>
</head>
<body>

<form id="Form1" runat="server">

<asp:DataGrid id="MySQLDataGrid" runat="server" />

</form>

</body>
</html>
********************************************************************

In some application you are required to add the following lines in web.config file.

<system.web>
                <compilation debug="true" urlLinePragmas="true">
                        <assemblies>
                                <add assembly="MySql.Data, Version=6.0.3.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"/></assemblies></compilation>
                <authentication mode="Windows"/>
                <customErrors mode="Off"/>
        </system.web>
        <system.data>
    <DbProviderFactories>
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.0.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
    </DbProviderFactories>
</system.data>

Webhost4life supports several types of databases such as MSSQL, MySQL, Excel, Excel 2007 and Access in asp.net application. If you are creating an asp.net application but having no idea how to connect your database to your web application then please have a look at the DB connection samples below:

MSSQL

ASP.net connection
"Server=DB Server;Database=DB NAME;uid=DB ID;pwd=DB Password;" providerName="System.Data.SqlClient"

OLE DB connection string (DSNless Connection):
"Provider=SQLOLEDB;Data Source=DB Server;User Id=DB ID;Password=DB Password;Initial Catalog=DB NAME;"

ODBC connection string
"DSN=ODBC Name;User Id=DB ID;Password=DB Password;Initial Catalog=DB NAME;"

Note: Please replace the following infomation in the sample string with your own database information:

• ODBC Name : The name of ODBC. You created in control panel > database > ODBC
• DB Server : sql3xx.mysite4now.com
• DB Name : mydemodb_12345
• DB ID : mydemoid_12345
• DB Password : mydbpw

You can get the DB infomation in hosting control panel->database->mssql admin section

MySQL

OLE DB connection string (DSNless Connection):

"DRIVER={MySQL}; SERVER=DB Server; DATABASE=DB Name; USER=DB ID; PASSWORD=DB PASSWORD; OPTION=3;"

ODBC connection string (Requires a ODBC to be created first):

"DSN=ODBC Name;User=DB ID;Password=DB PASSWORD;Database=DB NAME;"

Note: Please replace the following information in the sample connection string with your own databasde information:

• DBC Name : The name of ODBC you created in control panel > database > ODBC
• IP Address : mysqlxxx.mysite4now.com
• DB Name : mysqldemo_12345
• DB ID : mysqldemo_12345
• DB Password : mysqlpw

Excel

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path of excel;Extended Properties=Excel 8.0"

Note: Please replace the path of excel by the actual path of your excel file.example:
d:\member\memberID\myexcel.xls

PS. If you need to write data into your excel database, please grant write permission to the excel file.

Excel 2007

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path of excel;Extended Properties=Excel 12.0"

Note: Please replace the path of excel by the actual path of your excel 2007 file.
Example : d:\hosting\member\memberID\myexcel2007.xlsx

PS. If you need to write data into your excel database, please grant write permission to the excel file.

Access

Access DB WITHOUT password protection:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path of access db"

Access DB WITH password protection:

"Provider=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=path of access db;password=password of db"

Note: Please replace path of access db by the actual path of your access DB file
Example : d:\hosting\member\memberID\myAccessDB.mdb

Replace password of db by the database password of your Access database.