By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Backing up design changes

P: n/a
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.

Jul 23 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.


Jul 23 '05 #2

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

Jul 23 '05 #3

P: n/a
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!
Jul 23 '05 #4

P: n/a
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?

Jul 23 '05 #5

P: n/a
I think you mean SQL Compare at www.red-gate.com. Thanks for the tip.

Jul 23 '05 #6

P: n/a
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.
Jul 23 '05 #7

P: n/a
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....

Jul 23 '05 #8

P: n/a
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

Jul 23 '05 #9

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

Jul 23 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.