SMO - Connecting to the SQL Server

When you want to connect to SQL Server 2005 (or later version), you can user SQL Server SMO. To get started with SMO, first you must add references in Visual Studio. In the Add Reference window, select:

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SmoExtended
• Microsoft.SqlServer.Management.Sdk.Sfc
• Mircorost.SqlServer.ConnectionInfo

When the references are added, you must add two using statements for these namespaces:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

Now you can start working with SMO. Before doing restore and backup tasks, you must connect to the server. This connection is provided by the ServerConnection object. It lets you connect to the server and assign that connection to the Server object. The Server object represents an instance of SQL Server. In SMO programming, the Server object determines the connection to a physical SQL Server installation.

ServerConnection conn = new ServerConnection("INSTANCE", 
                            "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Console.WriteLine("Server: " + srv.Name);
    Console.WriteLine("Edition: " + srv.Information.Edition);
    conn.Disconnect();
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

This example shows you how to connect to SQL Server using a user name and password. Alternatively, you can use Windows Integrated Authentication:

ServerConnection conn = new ServerConnection();
conn.ServerInstance = "INSTANCE";
try
{
    Server srv = new Server(conn);
    Console.WriteLine("Server: " + srv.Name);
    Console.WriteLine("Edition: " + srv.Information.Edition);
    conn.Disconnect();
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

For disconnecting from SQL Server, we use the function Disconnect() of the ServerConnection class. The ServerConnection object is represented by Server.ConnectionContext, and alternatively you can disconnect from SQL Server by calling:

Server.ConnectionContext.Disconnect();