SQL Server Performance

Restor Database with smo

Discussion in 'SQL Server 2005 CLR' started by jn4u, Dec 22, 2005.

  1. jn4u Member

    Well started to play around with 2005 today after installing some test servers. We are using Log Shipping to central file server, offsite, different server rooms.

    Imports Microsoft.SqlServer.Management.Smo
    Module Module1

    Sub Main()

    Dim svr As Server = New Server("X.X.X.X")
    Dim res As Restore = New Restore()
    res.Database = "BBASDAT"
    res.Action = BackupActionType.Database
    res.Devices.AddDevice("\x58411491D$SEFWG-S0007BBASDATBBASDAT_db_200512201801.BAK", DeviceType.File)
    res.RelocateFiles.Add(New RelocateFile("bbasdat1", "E:MSSQLDataBBASDAT_Data.MDF"))
    res.RelocateFiles.Add(New RelocateFile("bbasdatlog1", "F:MSSQLTLogsBBASDAT_Log.LDF"))
    res.ReplaceDatabase = True
    res.RestrictedUser = True
    'Not possible at the moment compability issue...
    'EXEC dbo.sp_dbcmptlevel @dbname=N'BBASDAT', @new_cmptlevel=90
    res.StandbyFile = "F:MSSQLTLogsBBASDAT_Log_Endo.LDF"
    res.PercentCompleteNotification = 10
    AddHandler res.PercentComplete, AddressOf ProgressEventHandler
    res.SqlRestore(svr)

    End Sub

    Private Sub ProgressEventHandler(ByVal sender As Object, _
    ByVal e As PercentCompleteEventArgs)
    Console.WriteLine(e.Percent.ToString + "% restored")
    End Sub

    End Module

    I want to implement this simple console app inside the server. It tried to make a sp but I can not put a reference to the …Management.Smo assembly…

    Well it it possible to make a sp with sqlclr and smo call inside? And the call that sp from SQL Server Agent?

    Or is it better to do a Integration Services Packages and use the inside there?

    Well I#%92m playing with some simple homemade log shipping and standby server…



  2. Luis Martin Moderator

  3. jn4u Member

    The restorcode is working fine ...

    When I'm making a SQL Server Project and want add reference to the Microsoft.SQlServer.Smo.dll, I want to make a clr store procedure that uses smo calls inside. Not working ... can't put the reference...

    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server
    Imports Microsoft.SqlServer.Management.Smo <Not work can not put a reference to the smo dll...

    Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub StoredProcedure1()
    Dim svr As Server = New Server("194.17.115.93")
    Dim res As Restore = New Restore()
    res.Database = "BBASDAT"
    res.Action = BackupActionType.Database
    res.Devices.AddDevice("\x58411491D$SEFWG-S0007BBASDATBBASDAT_db_200512201801.BAK", DeviceType.File)
    res.RelocateFiles.Add(New RelocateFile("bbasdat1", "E:MSSQLDataBBASDAT_Data.MDF"))
    res.RelocateFiles.Add(New RelocateFile("bbasdatlog1", "F:MSSQLTLogsBBASDAT_Log.LDF"))
    res.ReplaceDatabase = True
    res.RestrictedUser = True
    res.StandbyFile = "F:MSSQLTLogsBBASDAT_Log_Endo.LDF"
    res.SqlRestore(svr)

    End Sub
    End Class

  4. cemuney New Member

    Hi

    In my opinion you cannot use CLR and SMO together.

    When you create SQL Server Project.
    You can only add referances about SQL Server Objects.
    If you click Add Referans....
    You can see a few objects to add as referance.

    if you want to use CLR, Create Sp using SQL Commands like
    RESTORE DATABASE BBASDAT etc....

    CHECK
    x:program FilesMicrosoft SQL Server90SamplesEngineProgrammabilityCLR & SMO
    you will see the difference....

Share This Page