none
restoring bak file into SQL server DB

    Question

  • I building a simple utility to restore bak file into DB

     

    1.       I  Referenced Microsoft.SqlServer.Smo.dll v10 and trying to create a Server object.

    Compile error was: 'Microsoft.SqlServer.Management.Sdk.Sfc.ISfcPropertyProvider' is defined in an assembly that is not referenced. You must add a reference to assembly 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

     

    2.       Although MSDN indicates not to reference this dll I added a reference to this assembly and now Server is created

     

    3.       Now I am trying to create Restore object – but this type could not be found in the Microsoft.SqlServer.Management.Smo namespace

     

    4.       Referencing version 9.0.242.0 of Microsoft.SqlServer.Smo.dll (which was not in the VS2008 assembly list and could be referenced only by manually edit the project file) solved all compilation errors

     

    5.       I am using a VS 2008 SP1 on a machine but the target framework of the project  is .NET Framework 2.0. No SQL server installed on the development machine.

     

     

    6.       After compiling I deployed it (simply copied the exe) to a machine with SQLexpress DB and .NET 2.0. Running the program caused an error: Could not load file or assembly 'Microsoft.SqlServer.SqlClrProvider, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

     

    7.       What am I missing?

     

    Tx

     

    Oren

    • Moved by Bob BeaucheminMVP Friday, December 17, 2010 5:43 PM Originally posted on SQLCLR forum, ignore first few answers (From:.NET Framework inside SQL Server)
    Wednesday, December 15, 2010 6:35 PM

Answers

All replies

  • SQLCLR doesn't support SMO, either directly or through any of the usual workarounds (http://www.sqlskills.com/BLOGS/BOBB/post/Two-things-you-cant-do-in-SQLCLR.aspx). I've heard various reasons for this, but it turns out the behavior is on purpose. Someone once wrote a SQLCLR proc that writes to a service broker service that uses external activation to invoke the SMO (I can't find it now), but that seemed like too much of a kludge to me. There's even a bug/enhancement request on Connect (https://connect.microsoft.com/SQLServer/feedback/details/126386/sql-clr-does-not-support-smo) for it.

    Hope this helps, Bob Beauchemin, SQLskills

    Wednesday, December 15, 2010 8:37 PM
  • Hi,

    Thanks for your answer but I am not sure I fully understood which question you were referring too.

    So maybe I will start from the end - isn't it possible to restore a bak file using c#?

    I specifically doesn't understand what you first said: "SQLCLR doesn't support SMO"...if this is the case - what is the purpose of the SMO? as I understand it this is what it is all about. Am I wrong?

    tx

    Oren

    Thursday, December 16, 2010 8:45 AM
  • SMO is a client-side API, rather than an in-server API. One of the major uses of SMO is to program SQL Server Management Studio (a client-side utility).

    This forum is about SQLCLR, that is, coding database objects (like stored procedures or user-defined aggregates) that run in the server, so that's how I thought you were trying to use SMO. But SQLCLR doesn't support SMO.

    If you are looking to use SMO for client-side operations, the appropriate forum is the "SQL Server SMO/DMO" forum. Let me know if client-side is your intent and I can move the question over there.

    Cheers, Bob Beauchemin, SQLskills

     

    Thursday, December 16, 2010 6:26 PM
  • Hi Bob

    Yes - intersted in client side ;-)

    thanks

    Oren

    Thursday, December 16, 2010 9:47 PM
  • So maybe I will start from the end - isn't it possible to restore a bak file using c#?

    Oren

    Hi Oren,

    In .NET, we can restore a SQL Server database using SMO, please refer to the following links:

    Getting Started with SMO in SQL 2005 - Restores
    http://www.sqldbatips.com/showarticle.asp?ID=40

    Restore Class
    http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx

    Alternatively, we can use ADO.NET objects to execute a T-SQL statement or stored procedure to restore SQL Server databases. Please see:

    RESTORE (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms186858.aspx

    SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
    http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Thursday, January 06, 2011 8:02 AM
    Friday, December 17, 2010 5:57 AM
  • Hello Jammusi,

    Please, could you have a look here ?

    http://msdn.microsoft.com/en-us/library/dd206977(v=SQL.100).aspx

    You will see

    "If your code uses the Transfer object directly, you will have to link to the Microsoft.SqlServer.Management.SmoExtended namespace"

    This remark is also concerning for the 2 classes Backup and Restore

    You will find an alert from Thomas Lee in the Community Content of the pages related to the Restore and Backup classes for SQL Server 2008 (version 10). But there is no remark about this problem in the corresponding documentation related to SQL Server 2008 R2 (version 10.5).A little omission, i hope it will be repaired with the next update of the BOL.

    For you, you have to add 4 using and their related dll

    - Microsoft.SQL Server.Smo

    - Microsoft.SqlServer.SmoExtended

    - Microsoft.SqlServer.ConnectionInfo

    - Microsoft.SQLServer.Common

    - Microsoft.SqlServer.Management.Sdk.Sfc

    It is a problem that many SMO developer faced when they begin or update from SQL Server 2005 (9) to SQL Server 2008 (10),2008 R2 (10.5)or Denali (11). In brackets, i have put the version major.

    Don't hesitate to post again for more help or explanations

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Saturday, December 18, 2010 11:42 AM
  • Hi Guys,

    Sorry for the late response.

    maybe I am still missing something ...can the application be develop on a machine without sql server installed?

    I added the namespaces and I am referencing

    Microsoft.SQLServer.ConnectionInfo (v10)

    Microsoft.SQLServer.Smo (v9.0.242)

    but none of them is actually familiar.

    tx

    Oren

    Tuesday, December 21, 2010 9:22 AM
  • Yes. Please download and install Microsoft® SQL Server® 2008 R2 Shared Management Objects from the following link

    Microsoft® SQL Server® 2008 R2 Feature Pack
    http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52&displaylang=en

    But I think you will still need SQL Server (local or remote) for test.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, December 21, 2010 9:32 AM
  • Hello Jammusi,

    Please, could you explain why you referencing ConnectionInfo (v10) and Smo (v9) ? You should use dll with the same version (10 or 9 ) not a mix.

    Please, Jian Kang, could you explain me in which folder the Smo pack is installing the .dll ? I hope in a different folder like :

    - C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies for Smo download 2008

    - C:\Program Files\Microsoft SQL Server\105\SDK\Assemblies for Smo download 2008 R2

    My question is personal as i had a problem with that, and i had to uninstall My SQL Server 2008 Dev and my SQL Server Express 2008 R2

    If you could answer, you would really help me

    Have a nice day

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Tuesday, December 21, 2010 10:08 AM
  • Hello Jammusi,

    Please, could you explain why you referencing ConnectionInfo (v10) and Smo (v9) ? You should use dll with the same version (10 or 9 ) not a mix.

    Please, Jian Kang, could you explain me in which folder the Smo pack is installing the .dll ? I hope in a different folder like :

    - C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies for Smo download 2008

    - C:\Program Files\Microsoft SQL Server\105\SDK\Assemblies for Smo download 2008 R2

    My question is personal as i had a problem with that, and i had to uninstall My SQL Server 2008 Dev and my SQL Server Express 2008 R2

    If you could answer, you would really help me

    Have a nice day

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Hi,

    For SQL Server 2008 R2 SMO, the assemblies are still installed in the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ directory.

    Please see: Installing SMO

    Based on my tests, the existing assemblies will not be replaced if we uninstall SMO 2008 and install SMO 2008 R2. I am not sure if uninstall the existing SQL Server 2008 is the only solution. You may consider submitting a feedback on http://connect.microsoft.com/sql.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, December 21, 2010 11:41 AM
  • Hi Jian,

    1. So I guess maybe this is what is missing on the dev machine...but the question is whether the Microsoft® SQL Server® 2008 R2 Feature Pack supports all SQL server various versions (2005, 2005 express,2008, 2008 express)?

    2. Yes - of course I need sql server install for testing ;-)

    3. Papi - The reason is written in the begining of the thread ...this is the only way i have managed to compile the project tx Oren


    Tx Oren

    Tuesday, December 21, 2010 12:22 PM
  • Hello Jian Kang,

    I am really sorry but , because my poor english, i am not sure to have understood plainly your quick answer so i prefer to post again before creating a feedback with MicrosoftConnect ( i have created feedback for some problems with extended properties for SQL Server 2005 and 2008 but they are not rectified in Denali, bad luck... ).

    If you think that it is outside the scope of the original post, please, could you split this post in a new thread ? ( thanks beforehand)

    For SMO, the assemblies for SQL Server 2008 are installed in a directory during the install of my SQL Server Dev 2008. If i install SQL Server Express 2008 R2, the new SMO assemblies are installed in the same directory, but are the SMO 2008 assemblies replaced by the SMO 2008 R2 assemblies ( this would explain the problems i faced with my Dev 2008 SSMS ) or it is an install side by side ( in this case, how can i do the difference between Microsoft.SqlServer.Smo.dll assembly file for 2008 and Microsoft.SqlServer.Smo.dll assembly file for 2008 R2 ?

    The last question is important as i have VS Standard 2008 and i am wandering with the class AffinityInfo new in SQL Server 2008 R2. I suppose that i have to trap an UnknownPropertyException or UnsupportedFeatureException or UnsupportedVersionException if the same program is executing versus a 2008 or 2008 R2 instance.

    Thanks beforehand for all the kindness of your previous answer

    Have a nice day

    PS: i have also studied this very interesting link but i am not sure to have understood it fully ( it is the why of this post )

    http://msdn.microsoft.com/en-us/library/ee210714.aspx


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Tuesday, December 21, 2010 11:30 PM
  • Hi Jian,

    1. So I guess maybe this is what is missing on the dev machine...but the question is whether the Microsoft® SQL Server® 2008 R2 Feature Pack supports all SQL server various versions (2005, 2005 express,2008, 2008 express)?

    Yes. Please see:

    Microsoft® SQL Server® 2008 R2 Shared Management Objects

    The SQL Server Management Objects (SMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services. This object model will work with SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 22, 2010 6:22 AM
  • Sorry for the previous reply. By default, the SMO 2008 R2 assemblies will be installed in the same directories as the SMO 2008 assemblies.

    X64 Package: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\

    X86 Package: C:\Program Files (x86) \Microsoft SQL Server\100\SDK\Assemblies\

    If we install the SMO 2008 R2 feature pack, the SMO 2008 assemblies will be replaced. We can right-click on one of the assemblies, click Properties and check File version on the Details tab like 10.0.2531.0 or 10.50.1600.1.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 22, 2010 7:03 AM
  • Hi,

    The download page of Microsoft® SQL Server® 2008 R2 Shared Management Objects downloads just an htm file - not the all package.

    Is this temporary or is it deliberate?

    I am also thinking of another direction...

    I need the ability to simply restore a dababase programitically. Either with SMO, command line or any other available tool.

    I need to consider the posibility that the machine I am restoring the DB from, does not have all required files for that purpose, thus I need to be able to deploy it myself.

    Is that allowed to deploy the SMO myself? Can I deploy the sqlcmd myself?


    Tx Oren
    Sunday, December 26, 2010 1:44 PM
  • The download page of Microsoft® SQL Server® 2008 R2 Shared Management Objects downloads just an htm file - not the all package.

    Is this temporary or is it deliberate?

    I can download all three packages: X86, 64 and IA64. Please check the browser settings or try another browser.

    I am also thinking of another direction...

    I need the ability to simply restore a dababase programitically. Either with SMO, command line or any other available tool.

    I need to consider the posibility that the machine I am restoring the DB from, does not have all required files for that purpose, thus I need to be able to deploy it myself.

    As mentioned in my first reply, we can also use ADO.NET objects to execute a T-SQL statement or stored procedure to restore SQL Server databases.

    Is that allowed to deploy the SMO myself? Can I deploy the sqlcmd myself?

    I think you can refer to the following link to add the assemblies to the setup project.

    http://www.codeproject.com/KB/dotnet/Win_App_Setup_Project.aspx

    Alternatively, we can use the Bootstrapper Manifest Generator to include the SharedManagementObjects.msi into prerequisite list and then add the prerequisite. Please see:

    http://www.codeproject.com/KB/aspnet/Add_Custom_Prerequisite.aspx

    You can also go to ClickOnce and Setup & Deployment Projects for more help.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, December 28, 2010 2:33 AM
  • Tx

    solved


    Tx Oren
    • Marked as answer by Jammusi Thursday, January 27, 2011 12:15 PM
    Thursday, January 27, 2011 12:15 PM