473,373 Members | 1,085 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,373 software developers and data experts.

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

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 ?

Apr 25 '07 #1
7 6468
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Olegus" <ol*********@gmail.comwrote in message
news:11**********************@t38g2000prd.googlegr oups.com...
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 ?

Apr 25 '07 #2
"Olegus" <ol*********@gmail.comwrote in message
news:11**********************@t38g2000prd.googlegr oups.com...
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.

Apr 25 '07 #3
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?

On Apr 25, 11:24 am, "Willy Denoyette [MVP]"
<willy.denoye...@telenet.bewrote:
"Olegus" <oleg.gus...@gmail.comwrote in message

news:11**********************@t38g2000prd.googlegr oups.com...
Hello,
in order to performbackup/restore MSSQL database using SMO, one needs
to reference several namespaces in abackupclass :
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.

Apr 27 '07 #4
"Olegus" <ol*********@gmail.comwrote in message
news:11*********************@c18g2000prb.googlegro ups.com...
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.

Apr 27 '07 #5
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

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.

Apr 27 '07 #6
"Olegus" <ol*********@gmail.comwrote in message
news:11*********************@r35g2000prh.googlegro ups.com...
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.

Apr 27 '07 #7
Thanks, now I've got it!


Apr 30 '07 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Igor Solodovnikov | last post by:
Hi. I am trying to automatically backup transaction log when error 9002 happened. So i have created appropriate job and alert to catch this error. I have two instances of sql server under Windows...
5
by: TThai | last post by:
Hi, Appreciated anyone who can help us with our problem. Our backup job for ROUTT database has been failing for the last 4 days. It was working fine for almost a year now. The only thing...
4
by: deprins | last post by:
Hello, I have run into some problems with logfiles and backup logfiles (MS SQL server). I have read much about them but uptil now I dont seem to grasp how it works. Specially the part of working...
3
by: JIMMIE WHITAKER | last post by:
I'm using ms access 2000 .adp file, and tables on sql server / msde. On the menu there's a backup option. It creates a file with a .Dat extension. How do I take the Dat file and restore data? ...
3
by: anuke | last post by:
Hi there, I use shared space MSSQL server in my hosting server. And I can't backup my DB to my remote server. Please help how can I do it. Thank you
1
by: Najm | last post by:
Hi All, I am facing this issue very frist time. I add a logical device as following: USE master GO EXEC sp_addumpdevice 'disk', 'AdvWorksData',
1
by: wolphie | last post by:
The IT shop I work in installed MS-SQL Server last week for the sole purpose of converting a large MS-SQL Server database to a format that Informix (our main database engine) can read. MSSQL...
1
by: Steve F | last post by:
i have a copy of a SQL Server 2000 database on an external hardrive. I have SQL Enterprise Manager 2005 on my laptop and was wondering if it is possible to view the backup on my laptop when...
4
by: phirestorm | last post by:
Hopefully someone can help me with an issue i am having. First off let me say that i have little to no experience with MS SQL or SQL in general. We have a Windows 2003 server running MSSQL (i...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.