Backup MSSQL db from C# code using SMO without knowing server version

[Olegus]

Hello,
in order to perform backup/restore MSSQL database using SMO, one needs
to reference several namespaces in a backup class :
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

Unfortunately, MSSQL 2005 and MSSQL Express keep them in different
place.
For MSSQL2005 they are located in C:\Program Files\Microsoft SQL Server
\90\SDK\Assemblies and for MSSQL Express you can find them in C:
\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE.

It means that if I build a project on PC that has MSSQL Express, it
(in theory) will not work on a PC with MSSQL2005.
It is unknown, what version of MSSQL will be installed on customer
server. We allow working with both versions.
Questions:
Can I use Express version of SMO and distribute needed dlls? Will
Express SMO work with full 2005 version?
If it won't, is it a way to dynamically change referenced dlls?
Does everything written above have any sense ?

 

[Nicholas Paldino [.NET/C# MVP]]

Olegus,

I imagine that the binaries that come with SQL Server 2005 will work
just fine with SQL 2000 (but not earlier). You should distribute those with
your application. Whether or not the SMO objects from express will work
with the full version of SQL Server, I really don't know, but I imagine that
they will, as the functionality that the SMO objects exposes is really
implemented by the server, and it is the server in the Express edition that
is restricted.

Hope this helps.

 

[Willy Denoyette [MVP]]

Hello,
in order to perform backup/restore MSSQL database using SMO, one needs
to reference several namespaces in a backup class :
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

Unfortunately, MSSQL 2005 and MSSQL Express keep them in different
place.
For MSSQL2005 they are located in C:\Program Files\Microsoft SQL Server
\90\SDK\Assemblies and for MSSQL Express you can find them in C:
\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE.

It means that if I build a project on PC that has MSSQL Express, it
(in theory) will not work on a PC with MSSQL2005.
It is unknown, what version of MSSQL will be installed on customer
server. We allow working with both versions.
Questions:
Can I use Express version of SMO and distribute needed dlls? Will
Express SMO work with full 2005 version?
If it won't, is it a way to dynamically change referenced dlls?
Does everything written above have any sense ?

The SMO components are part of the "Workstation components" of SQLServer
2005 (just like the native client components), what you really need to do is
install the Workstation components, you are not allowed to distribute the
SMO components alone, nor would it make any sense, you'll need a lot more
than the smo dll. The SMO components are perfectly usable to to access
Express version, in fact there is only one version of SMO.

Willy.


Willy.

 

[Olegus]

Thanks Willy,

how can I "install the Workstation components" ? Is it part of SQL
server install or VS or .NET?
I referenced Microsoft.SqlServer.Express.ConnectionInfo.dll and
Microsoft.SqlServer.Express..SMO.dll in my project , and a bunch of
other Microsoft.SqlServer.Express.* dlls appeared in my output folder.
You said I cannot distribute them alone, so what I understood from
your post, I need to use dlls on customer machine, but how can I find
them there, if customer has different server version or has it
installed in different location?

 

[Willy Denoyette [MVP]]

Thanks Willy,

how can I "install the Workstation components" ? Is it part of SQL
server install or VS or .NET?
I referenced Microsoft.SqlServer.Express.ConnectionInfo.dll and
Microsoft.SqlServer.Express..SMO.dll in my project , and a bunch of
other Microsoft.SqlServer.Express.* dlls appeared in my output folder.
You said I cannot distribute them alone, so what I understood from
your post, I need to use dlls on customer machine, but how can I find
them there, if customer has different server version or has it
installed in different location?

SQL20005 "Workstation components" (the SQL client components) are part of
the SQL Server 2005 SKU's (all versions), so this is what you need to
install.
The SQL2005 related assemblies are installed in "?:\Program Files\Microsoft
SQL Server\90\Assemblies" and are also installed in the GC when installing
SQL Server workstation components, these are the assemblies to refer at in
your projects, and they can be used for both SQL Express and the SQL2005
SKU's.

Willy.

 

[Olegus]

Willy,
does SQLExpress have those "Workstation components" or do I have to
install SQL2005 to get them?
Actually, this is probably the right question:
in order to use SMO in my project and deploy this functionality, do I
need to have SQL2005 and NOT Express?
Is it right? If it is so, what are the benefits of using SMO for
backup/restore instead of doing 'backup database XX to disk=' command?

Thanks,
Olegus

 

[Willy Denoyette [MVP]]

Willy,
does SQLExpress have those "Workstation components" or do I have to
install SQL2005 to get them?

You don't get it do you?
"SQL Server 2005" consists of two major components, a Client components and
a Server components. SQLExpress is a server only component, it's meant to be
used in a *desktop* context, not in a Client/Server context..
The "Workstation components" are part of "SQL Server 2005" distribution,
you don't have to install the "server" components though, just the "client"
components. These components are typically installed on MSSQL *management*
workstations, SMO is a management component part which is part of the
"Workstation component".

Actually, this is probably the right question:
in order to use SMO in my project and deploy this functionality, do I
need to have SQL2005 and NOT Express?

There is a difference between development and deployment, when you have to
develop code that targets both, SQL20005 and SQLExpresss, you have to
install the "Workstation component" on both your development station and on
the system that runs your code, no matter what version of SQL2005 (Standard
ed., Enterprise ed., SQLExpress etc...) it is running.

Is it right? If it is so, what are the benefits of using SMO for
backup/restore instead of doing 'backup database XX to disk=' command?

None, SMO is typically used in C/S contexts, that is from MSSQL management
workstations. Such contexts, can have several "MSSQL Servers" or instances
running and multiple SQLExpress instances spread over the organization. SMO
makes it easier to write management application running from a central
location.


Willy.

 

[Olegus]

Thanks, now I've got it!