Best way to generate script

Hi!

I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:

ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")

For Each uneTable In BDConfig.Tables

If Not uneTable.IsSystemObject Then

NouvelleBD.ExecuteNonQuery(uneTable.Script)

End If

Next

We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?

Thank and sorry about my bad English ^_^

I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||

I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:

Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0

Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure

db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next

scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True

ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)

This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.

|||

Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.

Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.

|||

I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.

Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database

db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"

Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile

dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size

dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent

dbCopy.Create() 'Create the database

'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()

|||Thanks but i have the same problem. When i tried to copy all objects it failed sometime (not all the time). I've got a message saying that it cannot created a view because the view use in the SELECT do not existe. In fact the problem is beacause it's trying to create the "child" view befor the "parent" view.

I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||

Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.

For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.

I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.