Restor Database with smo | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restor Database with smo

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…
Check: http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx http://msdn2.microsoft.com/en-us/library/ms220048.aspx Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
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
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….
]]>