Tech Insight !

SMO (Sql Server Management Object) can be used in .net application for dealing with database objects like table, stored procedure, views, and functions.

Basically you have to use “Microsoft.SqlServer.Management.Smo” namespace to use its classes in your application.

Additionally you need to use Microsoft.SqlServer.Management.Sdk.Sfc assembly.

I have used following assemblies for this sample code:

using System.Data;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using smo = Microsoft.SqlServer.Management.Smo;

Following code mentions use of SMO to copy tables and data.

/* SOURCE Database */
string myDBServer = “sourceDBServer”;
string myDBUsername = “testuser”;
string myDBPassword = “password”;
string myDBDatabase = “firstDB”;
//

/* DESTINATION Database */
string destServer = “destinationDBServer”;
string destLogin = “testuser”;
string destPassword = “password”;
string destDatabase = “targetDB”;
//

ServerConnection con = new ServerConnection(myDBServer, myDBUsername, myDBPassword);
smo.Server sqlServer = new smo.Server(con);
Database db = sqlServer.Databases[myDBDatabase];

Transfer transfer = new Transfer(db);
transfer.CopyAllUsers = true;
transfer.CreateTargetDatabase = false;
transfer.CopyAllObjects = false;
transfer.CopyAllTables = true;
transfer.CopyData = true;
transfer.Options.WithDependencies = true;
transfer.Options.DriAll = true;
transfer.Options.ContinueScriptingOnError = false;

//use following code if want to create destination databaes runtime
/*
ServerConnection destConnection = new ServerConnection(destServer, destLogin, destPassword);
Server destServerObj = new Server(destConnection);
Database newdb = new Database(destServerObj, destDatabase);
newdb.Create();
transfer.CreateTargetDatabase = true;
*/
transfer.CreateTargetDatabase = false;
transfer.DestinationLoginSecure = true;
transfer.DestinationServer = destServer;
transfer.DestinationLogin = destLogin;
transfer.DestinationPassword = destPassword;
transfer.DestinationDatabase = destDatabase;
transfer.ScriptTransfer();
transfer.TransferData();

Personally I think aobve programm is very usefull when we need to take backup SQL Server database.

NOTE : Also visit www.dhanashree.com

If you are in need of any Web Development feel free to Inquire us . Dhanashree Inc. Expertise in Asp.net Development, Php Development,
Website designing, Open Source customisation. Dhanashree Inc can be our offshore development company / outsourcing web development company, hire dedicated web programmers.

Above information is for knowledge sharing if you have problem / issue / suggestion please intimate us with details for proper and prompt action.

Normalization: is a process of organizing data and minimizing redundancy

De-normalization: is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Stored Procedure: is a named group of T-SQL statements which can be created and stored in Database as an object.

Primary Key: is a unique identifier of a row in a DB table, [it can’t be NULL]

Unique key: forces uniqueness to a respective table column, [it can be NULL]

Foreign Key: a foreign key in 1 table refers to the primary key in other table, Used to force referential integrity.

Inner join: exists in both tables

Left Outer join: all records from left side table + matched rows from right side table (totals number of rows will be same as left table)

Right Outer join: all records from right side table + matched rows from left side table (totals number of rows will be same as right table), it’s a mirror image of left outer join

Full Outer join: all records from left side table + all records from right side table, weather matched or not

Cross join: returns [left table rows * right table rows], a Cartesian product of both tables

Self join: when table joins to itself using diff aliases to avoid confusion

Union: selects only distinct records from both tables

Union all: selects all records from both tables

View: is a subset of a table, can be used to retrieve data, insert or Update data. Can contain multiple select statements inside

Trigger: A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.

Cursor: is a database object used to loop trough records on row by row bases.

Index: pointers to data records, represents structure of how data get stored physically in a table

Linked server: is a concept of adding other remote server to a group to query DB’s of both servers together

Collation: set of rules that determines how data stores & compares in database

Collation types: case sensitive, accent sensitive, kana sensitive, width sensitive

Data ware housing:

1. Record should Never delete from DB
2. All records must be linked
3. Once committed records should be read-only
4. All changes made must be tracked with time

User defined function (UDF): is a bunch of T-SQL statements which accepts 0 or more parameters and returns a scalar data value or table.

DDL: data definition language – e.g. TRUNCATE command is a DDL command

DML: data manipulation language – e.g. INSERT, UPDATE & DELETE are DML commands

NOTE :

If you are in need of any Web Development feel free to Inquire us . Dhanashree Inc. Expertise in Asp.net Development, Php Development, Website designing, Open Source customisation. Dhanashree Inc can be our offshore development company / outsourcing web development company, hire dedicated web programmers.

Above information is for knowledge sharing if you have problem / issue / suggestion please intimate us with details for proper and prompt action.