April 4, 2005 at 9:41 am
I know how to manually GENERATE SQL Script for all objects in a Database (using E/M, right mouse-clicking a DB, Generate SQL Script, then checkmarking all boxes, etc.)
Is there a way to accomplish this automatically - to schedule a SQL Server Job to Automatically script all objects in a database?
A Backup & Restore is not an option. The lead Developer wants an automated, nitely mechanism to dynamically script out Database "A" -- which inherits all changes to structures -- then replace Database "B" using the auto-generated Script. This to accomodate an .MSI type rollout.
thx in advance-
April 4, 2005 at 10:09 am
Pulled from the following link: http://www.karaszi.com/SQLServer/info_generate_script.asp
The SQLDMO API (DDL only)
The scrip generation code used by Enterprise Manager and Query Analyzer is available for us to use in the SQLDMO API. This means that you can write your own applets to generate scripts, and have the script looking exactly as you want. Below you find a simple VB example which generates script for all objects in the database:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.Connect "server", "login", "password" 'Connect to the server
Set oDb = oSS.Databases("pubs") 'Use a DB
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
You can also generate script using DMO per object. Just create an instance of the object and use the script method for that object.
Scptxfr.exe (DDL only)
This tool comes with SQL Server. See below URL for more information:
http://www.support.microsoft.com/?id=220163
They also listed some other products at that URL I listed at the top. You should probably review it in whole. I hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply