none
Copy a database schema into another database in SQL Server using VB.NET

    Question

  • Using SQL Server 2014.

    I want to copy a database (say d1) schema (without data) to another database (say d2) using vb.net.

    Such that on a click of a button, the database is created/copied.

    Searched many things, but not helpful.

    Searched Microsoft TechNet Library, followed every step from the link but something not working...

    How to: Transfer Schema and Data from One Database to Another in Visual Basic .NET

    Possible errors I got from the code available in above link are.

    From the line

    Dim db As DataBase

    I get an error 'Type Expected'

    I added all possible references, imported described namespaces but not working.

    Some problems faced while following the link code are :

    In C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies, there is no SDK and further folders in 90 folder. Taken Assemblies/references from C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder

    I added all the following Assemblies from C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ folder.

    Microsoft.SqlServer.SmoExtended.dll , Microsoft.SqlServer.Smo.dll , Microsoft.SqlServer.Connectioninfo.dll , Microsoft.SqlServer.SqlEnum.dll , Microsoft.SqlServer.Management.Sdk.Sfc.dll

    Imported the following namespaces

    Imports Microsoft.SqlServer.Management.Smo
    Imports Microsoft.SqlServer.Management.Common

    But still getting error. Searched a lot, nothing helped.. 
    Thanx


    Contact me at urvik_shroff@live.com

    Monday, April 17, 2017 7:17 AM

All replies

  • Bonny,

    It is probably easier to walk on your hands from NY to LA. 


    SQL Server has its own management tools, while what you ask is a one time operation. 

    Be aware that you can create with SQL management studio a script of the schema which you can run on that other database. 

    That script you can also run with VB using SQLExecuteNonQuery.

    However, maybe can you explain why it must be done with VB, I see not any reason for it.


    Success
    Cor


    Monday, April 17, 2017 10:35 AM
  • Try fully qualifying the Database class:

            'Reference the AdventureWorks database
            Dim db As Microsoft.SqlServer.Management.Smo.Database
            db = srv.Databases("AdventureWorks")
            'Create a new database that is to be destination database.
            Dim dbCopy As Microsoft.SqlServer.Management.Smo.Database
            dbCopy = New Microsoft.SqlServer.Management.Smo.Database(srv, "AdventureWorksCopy")
    


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 17, 2017 12:55 PM
  • I know that @Cor Ligthert, but my database is quite big and lots of procedures/views i created from base tables. So it'll be time consuming and moreover increase in file size if I write the script..

    Putting the whole script in a string and running the command SqlExecuteNonQuery gives some sql errors like view/ procedures should be in first batch (not sure if I read same error) which I can sort out (don't know about that right now.) later but I want the convenient way first.

    Contact me at urvik_shroff@live.com

    Monday, April 17, 2017 12:57 PM
  • Doing this at runtime, can be problematic and best left to use a tool. Without depending on SQL references this can be done but lots of coding would be needed. For instance just to create a raw script we need to do something like this (which I have not done anything with in vb code).

    SELECT  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
    FROM    sysobjects so
    cross apply
        (SELECT 
            '  ['+column_name+'] ' + 
            data_type + case data_type
                WHEN 'sql_variant' then ''
                WHEN 'text' then ''
                WHEN 'ntext' then ''
                WHEN 'xml' then ''
                WHEN 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
                ELSE coalesce('('+case when character_maximum_length = -1 
    			THEN 'MAX' else cast(character_maximum_length as varchar) end +')','') END + ' ' +
            CASE WHEN EXISTS ( 
            SELECT id FROM syscolumns
            WHERE object_name(id)=so.name
            AND name=column_name
            AND columnproperty(id,name,'IsIdentity') = 1 
            ) THEN
            'IDENTITY(' + 
            cast(ident_seed(so.name) as varchar) + ',' + 
            cast(ident_incr(so.name) as varchar) + ')'
            else ''
            end + ' ' +
             (CASE WHEN IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
              CASE WHEN information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 
    
         FROM information_schema.columns WHERE table_name = so.name
         ORDER BY ordinal_position
        FOR XML PATH('')) o (list)
    left join
        information_schema.table_constraints tc
    on  tc.Table_name       = so.Name
    AND tc.Constraint_Type  = 'PRIMARY KEY'
    CROSS APPLY
        (SELECT '[' + Column_Name + '], '
         FROM   information_schema.key_column_usage kcu
         WHERE  kcu.Constraint_Name = tc.Constraint_Name
         ORDER BY
            ORDINAL_POSITION
         FOR XML PATH('')) j (list)
    WHERE   xtype = 'U'
    AND name    NOT IN ('dtproperties')
    
    From there you would need to loop through results and dynamically generate tables and keys. If there are stored procedures and triggers they need to be handled also.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, April 17, 2017 1:04 PM
    Moderator
  • Okay this worked for me but its just creating a database but no tables/views/procedures inside it.. Thanx for this little help though..! @Paul P Clement IV
    • Edited by Bonny Shroff Monday, April 17, 2017 2:27 PM Inserted name whom I referring to.
    Monday, April 17, 2017 2:14 PM
  • Yea Karen, it is problematic to run script at run-time but I need to run from VB code only, I have some clients who don't have any knowledge (some bought computer just to use my software) about SQL management studio , so I need to do this step... I created tables by taking script from management studio, I need to handle Views/procedures for further but first I am seeking for a convenient way/ small way to do it.. Your option is 2nd for me, if nothing works, I'll do in this way but have to run at run-time only.
    Thank you for your reply.

    Contact me at urvik_shroff@live.com

    Monday, April 17, 2017 2:24 PM
  • Bony, 

    I wrote that you could use executenonquery. 

    Look here for some samples on our website.

    http://www.vb-tips.com/CreateSqlDatabase.aspx


    Success
    Cor

    Monday, April 17, 2017 4:33 PM
  • @Cor Lightert, See I got this , just 1 line needed to copy 1 table

    ""select * into DestDb.dbo.Table from SourceDb.dbo.Table""

    This copies whole table without writing stuff like creating script and all.

    Contact me at urvik_shroff@live.com

    Tuesday, April 18, 2017 6:29 AM
  • Bonny, 

    * is in your sample all data from Source, but id supposes an empty  Dest does exist then. 

    You have to create somehow the scheme and simply creating that as script with Management studio can do your job. (That in the case you want to add it silently to many other database servers from your customer). 

    (And the script is simply a piece of SQL transact code in this case).


    Success
    Cor


    Tuesday, April 18, 2017 10:38 AM
  • Okay this worked for me but its just creating a database but no tables/views/procedures inside it.. Thanx for this little help though..! @Paul P Clement IV

    You might want to take a look at the project at the below link. It's written in C# but you can either convert the code or easily verify that your options are set correctly:

    https://www.codeproject.com/Articles/994806/SQL-Server-Database-Cloning-Tool-using-Csharp


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 18, 2017 12:33 PM