To prepare a backup script that can easily be “replayed”, start SQL Server Enterprise manager.

Expand your databases node under your SQL Server computer name, select the database, right mouse click>All Tasks>Generate SQL Script…

From the dialog box, you have the option to script a single object or multiple objects.You can run the following steps for stored procedures, then again for Views.

Click Show All, then ‘All views’ and ‘All stored procedures’. There are a couple of other options I recommend, one to allow scripting any custom permissions that have been set on any object and to drop any object to allow re-creation. Both steps will save you time, should you ever need this script. Click the Scripting tab, options should default to the following settings:

Now, check the Options tab:

Choose ‘Script object-level permissions’, click OK, the wizard will now prompt you for a file name to create the file. Choose a file name that is self-documenting, along with a date that indicates the date run (e.g. XYZ_SMS-2002-05-20.SQL). Archive this to a save location!

To recreate any of your stored procedures or views, start an instance of Query Analyzer, load the script and run the appropriate portion of the script.

If you would like to migrate the data in a SQL Server Database from development server to production server or vice versa , there is a tool called DTSWizard which comes with SQL Server installation that import and export data between a Data Source and Destination Source.

These instructions below will show step by step on How to export data from a database to another database with SQL Server 2008.

Note: steps below are for the export data process, for import data process, the steps are the same, you just reverse the source and destination.

1. Open the SQL Server Import and Export Wizard.

Right-Click on a Database => Task => Export Data

– Or –

From Start Menu => Microsoft SQL Server 2008 R2 => Import and Export data (64-bit)

– Or –

Open the wizard by finding the DTSWizard.exe executable file located on your SQL Server installation folder. It should be available at:

* C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn
* C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn

2. Click Next on the Welcome screen.

3. Assign database information for source includes: Server name, Username and Password if use SQL Server Authentication mode or Use Windows Authentication mode.

After that, Click Next

4. Assign database information for destination includes: Server name, Username and Password if use SQL Server Authentication mode or Use Windows Authentication mode.

After that, click Next

5. Select the first option: Copy data from one or more tables or views.

Click Next

6. The next step will ensure we migrate the appropriate table data. Select on which tables you want to export.

Please make sure all desired tables are included and mapped accordingly.

Click Edit Mappings … for all tables:

Select “Delete rows in destination table”
And select “Enable identity insert”

Click OK

7. Repeat those steps for all tables in the transfer. Once complete click the Next button.

8. You need to select “Execute immediately”

Click the Next button.

The final stage of this transfer/export process will perform the task.

Connection String for PHP-MSSQL

<?
/*
PHP MSSQL Example

Replace data_source_name with the name of your data source.
Replace database_username and database_password
with the SQL Server database username and password.
*/

$data_source=’mssql.dnsracks.com,1533′;
$user=’database_user_name’;
$password=’database_password’;

// Connect to the data source and get a handle for that connection.
$conn=mssql_connect($data_source,$user,$password);
if (!$conn){
if (phpversion() < ’4.0′){
exit(“Connection Failed: . $php_errormsg” );
}
else{
exit(“Connection Failed:” . errormsg() );
}
}

?>

***********************************************************************************************************

ODBC

<?
/*
PHP MSSQL Example

Replace data_source_name with the name of your data source.
Replace database_username and database_password
with the SQL Server database username and password.
*/

$data_source=’dns_name’;
$user=’database_user_name’;
$password=’database_password’;

// Connect to the data source and get a handle for that connection.
$conn=odbc_connect($data_source,$user,$password);
if (!$conn){
if (phpversion() < ’4.0′){
exit(“Connection Failed: . $php_errormsg” );
}
else{
exit(“Connection Failed:” . odbc_errormsg() );
}
}

// Disconnect the database from the database handle.
odbc_close($conn);
?>

For MSSQL 2005
data_source = 74.86.116.10,1433

For MSSQL 2008
data_source = 72.18.135.197,1433

Connection Strings For MSSQL 2008 Database

Data Source=72.18.135.197,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

SQL Server Management Studio Express 2008

Download and Install any edition of SQL Server 2008 (As per your Requirement)

• SQL Server 2008 Express
• SQL Server 2008 Express with Tools
• SQL Server 2008 Express with Advanced Services

Which edition of SQL Server 2008 Express is right for you?
SQL Server 2008 Express is available in the following 3 editions

SQL Server 2008 Express

• SQL Server database engine – create, store, update and retrieve your data

SQL Server 2008 Express with Tools

• SQL Server database engine – create, store, update and retrieve your data
• SQL Server Management Studio Basic – visual database management tool for creating, editing and managing databases

SQL Server 2008 Express with Advanced Services

• SQL Server database engine – create, store, update and retrieve your data
• SQL Server Management Studio Basic – visual database management tool for creating, editing and managing databases
• Full-text Search – powerful, high-speed engine for searching text-intensive data
• Reporting Services – integrated report creation and design environment to create reports

Make sure you download the SQL Server 2008 Express with Tools

Minimum Requirements:

• Download and install Microsoft .Net Framework 3.5 SP1.
• Download and install Windows Installer 4.5 [important if you have pre-Vista OS].
• Download and install Windows PowerShell 1.0.

Note: If you have non-SP1 version of Visual Studio 2008, you will be stopped at the very last step before the install begins! So if you have VS2008 or any 2008 Express products, upgrade them to SP1 to install the Tools version to get your Management Studio Express.

How to connect MSSQL 2008 Database

* Login into DNP
* Click on User Spaces name
* Click on Databases -> MSSQL 2008
* Click on The Browse database Link

Connection Strings (MSSQL 2000, MSSQL 2005, MSSQL 2008, MySQL4, MySQL5, MS Access Database)

MSSQL2000
Set conn = Server.CreateObject(“ADODB.Connection”)
conn.Open “Provider=SQLOLEDB; Data Source =mssql.dnsracks.com,1533; Initial Catalog = Yourdatabase; User Id = USER; Password=PASSWORD; Network Library=dbmssocn;”
If conn.errors.count = 0 Then
Response.Write “Connected OK”

MSSQL2005
Set conn = Server.CreateObject(“ADODB.Connection”)
conn.Open “Provider=SQLOLEDB; Data Source =74.86.116.10; Initial Catalog = Yourdatabase; User Id = USER; Password=PASSWORD; Network Library=dbmssocn;”
If conn.errors.count = 0 Then
Response.Write “Connected OK”

MSSQL 2008
Data Source=72.18.135.197,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

MySQL4
<%
dim CN
set Cn = Server.CreateObject(“ADODB.connection”)
CN.Open “DSN=dsn_name; Server=localhost;database=database_name; Uid=user_id; Pwd=password;”
%>

MySQL5
<%
dim CN
set Cn = Server.CreateObject(“ADODB.connection”)
CN.Open “DSN=dsn_name; Server=localhost:3307;database=database_name; Uid=user_id; Pwd=password;”
%>

MS Access

DSN-Less

<%
set conn=Server.CreateObject(“ADODB.Connection”)
conn.Provider=”Microsoft.Jet.OLEDB.4.0″
conn.Open “c:/webdata/northwind.mdb”
%>
Connected !!

DSN

1) You must first create the DSN in Helm under your domain.
2) Then use the following connection string – with your own DSN
name of course :

<%
Set connectionToDatabase=Server.CreateObject(“ADODB.Connection”)
connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open”DSN=DSN_Name”
%>
Connected !!

To Know more about Connection Strings open the following link
http://www.connectionstrings.com/

SQL Server error ’80004005′ (TDS buffer length too large)

Error: SQL Server error ’80004005′

TDS buffer length too large

Solution:

SET NO_BROWSETABLE OFF

SET NOCOUNT ON

If you are querying system tables, use a forward-only cursor (to be safest, avoid ADODB.Recordset altogether and update us if this resolves the problem or not.