Database creation using VB.NET Code

Hello.

I think its silly question but i expect some solution .

Is there any way to create sql sever database by using VB.NET code ? I have to create database,tables,sps and also transfer the data from one database to newly created database.

Please, if somebody have some solution then guide me in detail.

Thanks in adv.If the database you need to create is a copy of some fixed database, you can create SSIS package, and then run it from VB.NET code.
If you just need to create a database from scratch, use SMO. See Books Online, and if you need help consult SMO/DMO forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1
|||

Hi,

I am trying to create a copy of some fixed database. I am wondering how creating a SSIS package will help.Is there any code for making this database replication?

Thank You!

|||

Partially it depends on exactly what your problem is. If you just want to take a database from one server and move it to another, why not issue a backup, copy, and restore using 3 SSIS tasks? You'll need to fix up any SQL Server users, but that could be scripted as well as adding any missing logins.

Another alternative if you can take the database offline, is to detach the database, copy the database files directly, and then reattach on both servers. Might be a bit faster but requires some down-time.

There's also a Transfer SQL Server Objects task if more approrpriate to your needs which might wrap most of the stuff up into one task.

If you just want to sync one database to another while they are both online, then hopefully your database is structured to do that or you'll have to truncate or delete the records from the tables in the destination and then create data flow tasks for each level of referential integrity to move the data.

You could also look at SQL Server replication or SQL Server Mirroring.

|||

Basically, what I am trying to do is within the same server, i want to create a copy of an existing database. This copy will be used for a different purpose.

|||

Backup and restore to a new name WITH MOVE sounds like the easiest. On a single server you could do it all in one SQL Task or break it into two, you won't have to fix users or add logins either.|||Is there any sample code where I can look at written in vb.net?

|||

Just standard .Net code. You'll likely have better luck over in the VB or C# forums for help with the code and in the Transact SQL forums for assistance with the SQL. Below is a fictionalized example. You might add a DROP DATABASE, you may want to kill off connections, etc. It would just be additional SQL in the code below. Change the CommandTimeout based on how long you expect it to restore (in seconds) and then maybe double it. Below is assuming it takes 30ish minutes to backup and restore.

Imports System.Text

Module Module1

Sub Main()

Using cn As New SqlClient.SqlConnection(My.Settings.Connection)

cn.Open()

Dim sql As New StringBuilder

sql.AppendLine("BACKUP DATABASE [myDatabase] TO DISK='C:\mypath\myfile.BAK';")

sql.AppendLine("RESTORE DATABASE [myDatabaseNEW] FROM DISK='C:\mypath\myfile.BAK WITH MOVE myDatabase_Data TO 'C:\mydatapath\myDatabaseNEW.MDF', MOVE myDatabase_Log TO 'C:\mydatapath\myDatabaseNEW.ldf'")

Using cmd As New SqlClient.SqlCommand(sql.ToString, cn)

cmd.CommandTimeout = 1800

cmd.ExecuteNonQuery()

End Using

End Using

End Sub

End Module

|||

Hi,

I get an error saying that 'myDatabase.mdf' cannot be overwritten because it us being used by database 'myDatabase'

|||

Your WITH MOVE isn't working, make sure the files listed are different than the files of the original database.

Such as myDatabaseNew.mdf not myDatabase.mdf.

|||

I found where the mistake was. Instead of using WITH MOVE 'myDatabase' I wrote it as WITH MOVE 'myDatabaseNew'. This was the reason for the error.

Its working well now.

Thanks !

|||Just an alternate suggestion - there is a Transfer Database task in SSIS that automates much of this.