DB Dublicate

I need to create a dublicate for a databaseprogramatically
meaning if i have db2004, I need to create db2005programatically same as db2004(tables and procedures, only structure)

Is that possible??

thank you

The backup and restore wizard will let you change the name of the new one and you can generate the create statement and run it through ADO.NET but you have to manually move the data. In the backup and restore wizard choose restore from device. Try the link below for ADO.NET create database code sample. Hope this helps.

http://www.functionx.com/csharp/adonet/Lesson04.htm

|||Thank you for this fast reply

Is there any way to backup restore programatically?|||

I have thought of that if it is there it is in SMO(sql server management object). I did not see any code so you will need a book because Microsoft wait for us to write the code most of the time. Try the link below for detail.

http://msdn2.microsoft.com/en-us/library/ms162175.aspx

|||

Thanks alot..

Is there any other way I can clone my current db to a new named db??|||

You have two options generate the create statement to create it in ADO.NET and use SSIS(sql server integration service) to move your data later or dettach and attache the database programmatically. Hope this helps.

http://www.sqlis.com

http://forums.asp.net/thread/977493.aspx

|||

sorry for these too much questions, please be patient

I forgot to tell you that im using SQL Server 2000

I have an Idea but i need some help

1- I can easily create a database and name it whtever I want
2- I can generate sql script for all tables and procedure of my current DB

Is there any way that I can run the .sql file from my code?

Thank you soo much in advanced

|||

That means you need DMO(data management object) code and I have found two try the links below for the code. Hope this helps.

http://www.aspfree.com/c/a/VB.NET/Simple-BackupRestore-Utility-With-SQLDMO-Using-VBNET/

http://www.codersource.net/csharp_sqldmo_sqlserver.aspx

|||hello again

sorry but I meant in my last post to execute an sql file from my vb.net code

Dim cmdNameAsString = File.OpenText("tables.sql").ReadToEnd()
Dim cmdAsNew SqlCommand(cmdName, con)
cmd.CommandType = CommandType.Text

con.Open()
cmd.ExecuteNonQuery()
con.Close()

-----------------------
I have a problem here...
tables.sql is execured with no problems

but when im trying to execute "procedures.sql" (file containing SqlScript of all stored procedures)
errors are raised

Please help this is the last step

Thank you sooooooooooooooooo much

|||

Multiple stored proc can be called with a system stored procedure sp_executesql so you will need to wrap your code with it. Try the link below for details. The other option is to call each stored proc separately but that is just time consuming. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms188001.aspx

|||You are amaaaazingggggggggggggggggggggggg

Thank you sooo much....

It worked finally|||

JRICE:

You are amaaaazingggggggggggggggggggggggg

Thank you sooo much....

It worked finally

I am glad I could help.