console app to back up database - problems with SQLDMO

[supersal666]

Hi people, I have created a console app which is supposed to backup a database. I wanted to use SQLDMO as alot of the examples are in this but get an error when running my code like below:

COMException was unhandled
Retrieving the COM class factory for component with CLSID {10021F00-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80040154.

System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2147221164
Message="Retrieving the COM class factory for component with CLSID {10021F00-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80040154."
Source="SQLNightlyBackup"
StackTrace:
at SQLNightlyBackup.Module1.Main() in C:\Users\sally\AppData\Local\Temporary Projects\SQLNightlyBackup\Module1.vb:line 9
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

i basically just downloaded the interop.SQLDMO.dll to my machine then added it as a reference from the references tab. I'm using sql server express 2008 and vb 2008 express edition. Is this the correct way to do it and does anyone know why i am getting the error?

Here is my code although it crashes on the first line

Imports Microsoft.SqlServer
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.IO

Module Module1
    Sub Main()
        Dim backup As SQLDMO.Backup = New SQLDMO.BackupClass() ' crashes here
        Dim sqlserver As SQLDMO.SQLServer = New SQLDMO.SQLServerClass()
        sqlserver.LoginSecure = False
        sqlserver.Connect("sql.ima-group.co.uk", "sa", "password")
        backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
        backup.Database = "CrackerDrinks"

        backup.Files = "C:\SQLBackups\National\TestDataBase.bak"
        backup.BackupSetName = "Test01"
        backup.BackupSetDescription = "Database backup description"
        backup.Initialize = True
        backup.SQLBackup(sqlserver)
    End Sub
End Module

Any help would be gratefully recieved!
Thanks
Sally

 

[JohnH]

Interop files are not actual libraries, they are .Net interfaces to the actual libraries which must be present at runtime to work.

.Net has SMO support included, so you don't need that old DMO library. Just use the Add Reference dialog and in .Net tab find Microsoft.SqlServer.Smo. You can probably search 'vb.net smo' to find examples and MSDN has reference documentation. Here's one that appeared for backup: Getting Started with SMO in SQL 2005 - Backups

 

[supersal666]

Hi John, I did notice the SMO reference and tried to add that as a reference as well but it doesnt show up even when i search for it in the object browser? Do you think its because I am using the express editions of vb.net and sql server?
Thanks
Sally

 

[JohnH]

There is no limitation of using these libraries with VB Express. If you have added reference you can browse with Object Browser by filter 'My Solution' and locating the root namespace. It is also possible browsing them (if not referenced) by adding to and using the 'Custom Component Set' in Object Browser. Otherwise what do you mean by 'show up'? A reference only shows up in the projects references list, and appears most notably by allowing you to use the namespaces and classes it contains. From Solution Explorer in VB you can also 'show all files' and expand the References node, and from there use the context menu option 'view in Object Browser'.

A correction from what I said in previous post: These libraries are actually not part of .Net Framework, but are separates from Sql Server SDK, they require minimum .Net 2.0 to be used. According to documentation they are added during installation of Sql Server from 'Client Tools' option, though I'm fairly certain I got them when installing SSMSE (Sql Server Management Studio Express) a good while ago. From my current research it looks like they are also available from 'SQL Server Management Objects' that is a separate Feature Pack download.