Backing up design changes

[Jerry Porter]

Is there a way to back up the design changes in a SQL Server database
without backing up all the data? It's just test data at this point.

[Steve Jorgensen]

The way I usually do it is to use the scripting feature in Enterprise Manager.
This will generate SQL scripts that can be run on a new database to rebuild
the database structure.

On 4 Jan 2005 11:38:05 -0800, "Jerry Porter" <je****@personablepc.com> wrote:

Is there a way to back up the design changes in a SQL Server database
without backing up all the data? It's just test data at this point.

[SQLDBA]

There are some thrid party tools like RegGate Compare that will push
just the changes and keep the data intact...!

[Tim Groulx]

Right click on the db --> all tasks --> generate SQL script. You can
script the whole db, you can do that before and after changes to keep
track of versions. You can create a job to do it to.

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jerry Porter]

Thanks Steve,

I had used that tool, but hadn't taken it all the way.
[Right-click on database, All Tasks, Generate SQL Script, Show All,
Script all objects]

I don't suppose there's a way to automate this?

[Jerry Porter]

I think you mean SQL Compare at www.red-gate.com. Thanks for the tip.

[Steve Jorgensen]

On 4 Jan 2005 12:19:21 -0800, "Jerry Porter" <je****@personablepc.com> wrote:

Thanks Steve,

I had used that tool, but hadn't taken it all the way.
[Right-click on database, All Tasks, Generate SQL Script, Show All,
Script all objects]

I don't suppose there's a way to automate this?

Unfortunately, it seems not. Most other database servers make this a feature
of the database server itself, not the GUI tool, but this is Microsoft we're
talking about.

[SQLDBA]

Rightclick and generating script will give you scripts but when you run
them in production environment, it will drop tables and recreate
them... You still need to figure out a way to save the data.... So I
wouldnt recomend that unless, you just wanted to keep a log of changes
made to the DB....!

As far as I know, there are no direct way to do this unless you go for
some thrid party tool....

[Jerry Porter]

I found some old threads suggesting the use of SQL-DMO to generate
scripts, so I looked into it. The following VBA code generates a text
file with scripts for all (?) database objects:

Dim srv As SQLDMO.SQLServer2
Dim db As SQLDMO.Database2
Dim tr As SQLDMO.Transfer2

Set srv = New SQLDMO.SQLServer2
srv.Connect "MyServer", "sa", ""
Set db = srv.Databases("MyDatabase")

Set tr = New SQLDMO.Transfer2

With tr
..CopyAllObjects = True
..DestDatabase = "test"
End With

db.ScriptTransfer tr, SQLDMOXfrFile_Default _
Or SQLDMOXfrFile_SingleSummaryFile, _
"c:\temp\dbscript.txt"

Set tr = Nothing
Set db = Nothing
Set srv = Nothing

There are at least 3 scripting methods (Script, GenerateSQL,
ScriptTransfer), and a long list of options, so there's a good change I
don't have it exactly right.

Jerry

[Matt]

Look into the SCPTXFR.EXE utility. For an example, see the end of this
article: http://www.dbazine.com/larsen4.shtml