Asked by:
Copy a database schema into another database in SQL Server using VB.NET
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
SDKand further folders in 90 folder. Taken Assemblies/references from
C:\Program Files\Microsoft SQL Server\100\SDK\Assembliesfolder
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.dllImports Microsoft.SqlServer.Management.Smo
Imported the following namespaces
Imports Microsoft.SqlServer.Management.Common
But still getting error. Searched a lot, nothing helped..
Thanx
Contact me at urvik_shroff@live.com
Question
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
- Edited by Cor Ligthert 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)
- 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
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
- 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.
- 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
Bony,
I wrote that you could use executenonquery.
Look here for some samples on our website.
Success
Cor
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
- Edited by Cor Ligthert Tuesday, April 18, 2017 10:39 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:
Paul ~~~~ Microsoft MVP (Visual Basic)