Backing up design changes

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.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" <jerryp@.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.|||There are some thrid party tools like RegGate Compare that will push
just the changes and keep the data intact...!|||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!|||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?|||I think you mean SQL Compare at www.red-gate.com. Thanks for the tip.|||On 4 Jan 2005 12:19:21 -0800, "Jerry Porter" <jerryp@.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.|||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...|||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|||Look into the SCPTXFR.EXE utility. For an example, see the end of this
article: http://www.dbazine.com/larsen4.shtmlsql

Availability during ETL processing

Hi,

I am in the midst of designing a new Data Warehouse system. As we get further into the design of the system, the more we are realising how complex our ETL is going to be and that the amount of time it will take to run could be significant i.e. a few days! My question is obviously I don't want to have a down time in my relational system for this long and prevent my users from accessing the data for days at a time each month. So what functionality should I be looking at to allow me to maintain a working copy of the data that users can query whilst I perform database updates and then perform a quick promotion of the updated data to users for querying?

If you can point me in the direction of the right functionality in SQL Server 2005 and possible some relevant white papers that cover this sort of scenario I would be grateful.

regards

Colin

In your ETL process, are you building a completely new DW database, or are you updating the existing one?

This seems like a very good application for some form of snapshots.

If you're updating an existing database, then database snapshots should fit the bill. They will present a static view of the data as it existed at the point in time when the snapshot was created. This allows the source database to be updated without disturbing the users.

If you create and populate a new database in each ETL cycle, then you may want to look into something like volume snapshots using the VSS framework. In this case, you are making a snapshot of the entire volume(s) on which the database resides. This can then be mounted as a read-only database for your users to query. The advantage in this scenario is that it doesn't need to be tied to the previous database.