Copying schema

Hi Everyone,
Using SQL 2000 or 2005 (I have both in my lab), how can I copy the schema?
I'm not trying to do a migration or anything, just need a copy of the schema
to compare it to the schema from another database, for a homegrown app
database our team is working on.
Thanks in advance!If you want to compare schemas, there are a few tools that you can use that
work quite well. I have used Red Gate's Schema Compare many times and I
have found it helpful. I don't have direct experience with the other tools
in this list, but I know that they offer similar features to SQL Compare.
Red Gate has a tool called SQL Compare
http://www.red-gate.com/
Quest - Comparison Suite for SQL Server
http://www.quest.com/sql_server/
Innovartis DB Ghost
http://www.dbghost.com
WinSQL
http://synametrics.com/SynametricsWebApp/WinSQL.jsp
Database Workbench
www.upscene.com
If you want to script the schema you would do so (within SQL 2005) by
right-clicking on the database within SSMS's Object Explorer and choose
Tasks then Generate Scripts.
In SQL Server 2000 the steps are the same but the text on the right-click
menu is slighly different. It is All Tasks then Generate SQL Script.
--
Keith Kratochvil
"Samantha B." <nospam@.myisp.com> wrote in message
news:OrbxHHV8GHA.4552@.TK2MSFTNGP05.phx.gbl...
> Hi Everyone,
> Using SQL 2000 or 2005 (I have both in my lab), how can I copy the schema?
> I'm not trying to do a migration or anything, just need a copy of the
> schema to compare it to the schema from another database, for a homegrown
> app database our team is working on.
> Thanks in advance!
>|||Samantha,
If all you are looking for is to compare a schema against a reference
schema, you can use the SchemaCrawler tool. SchemaCrawler outputs
details of your schema (tables, views, procedures, and more) in a
diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can
also output data (including CLOBs and BLOBs) in the same plain-text
formats. You can use a standard diff program to diff the current output
with a reference version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.

Copying schema

Hi Everyone,
Using SQL 2000 or 2005 (I have both in my lab), how can I copy the schema?
I'm not trying to do a migration or anything, just need a copy of the schema
to compare it to the schema from another database, for a homegrown app
database our team is working on.
Thanks in advance!If you want to compare schemas, there are a few tools that you can use that
work quite well. I have used Red Gate's Schema Compare many times and I
have found it helpful. I don't have direct experience with the other tools
in this list, but I know that they offer similar features to SQL Compare.
Red Gate has a tool called SQL Compare
http://www.red-gate.com/
Quest - Comparison Suite for SQL Server
http://www.quest.com/sql_server/
Innovartis DB Ghost
http://www.dbghost.com
WinSQL
http://synametrics.com/SynametricsWebApp/WinSQL.jsp
Database Workbench
www.upscene.com
If you want to script the schema you would do so (within SQL 2005) by
right-clicking on the database within SSMS's Object Explorer and choose
Tasks then Generate Scripts.
In SQL Server 2000 the steps are the same but the text on the right-click
menu is slighly different. It is All Tasks then Generate SQL Script.
Keith Kratochvil
"Samantha B." <nospam@.myisp.com> wrote in message
news:OrbxHHV8GHA.4552@.TK2MSFTNGP05.phx.gbl...
> Hi Everyone,
> Using SQL 2000 or 2005 (I have both in my lab), how can I copy the schema?
> I'm not trying to do a migration or anything, just need a copy of the
> schema to compare it to the schema from another database, for a homegrown
> app database our team is working on.
> Thanks in advance!
>|||Samantha,
If all you are looking for is to compare a schema against a reference
schema, you can use the SchemaCrawler tool. SchemaCrawler outputs
details of your schema (tables, views, procedures, and more) in a
diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can
also output data (including CLOBs and BLOBs) in the same plain-text
formats. You can use a standard diff program to diff the current output
with a reference version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.

Copying schema

Hi Everyone,
Using SQL 2000 or 2005 (I have both in my lab), how can I copy the schema?
I'm not trying to do a migration or anything, just need a copy of the schema
to compare it to the schema from another database, for a homegrown app
database our team is working on.
Thanks in advance!
If you want to compare schemas, there are a few tools that you can use that
work quite well. I have used Red Gate's Schema Compare many times and I
have found it helpful. I don't have direct experience with the other tools
in this list, but I know that they offer similar features to SQL Compare.
Red Gate has a tool called SQL Compare
http://www.red-gate.com/
Quest - Comparison Suite for SQL Server
http://www.quest.com/sql_server/
Innovartis DB Ghost
http://www.dbghost.com
WinSQL
http://synametrics.com/SynametricsWebApp/WinSQL.jsp
Database Workbench
www.upscene.com
If you want to script the schema you would do so (within SQL 2005) by
right-clicking on the database within SSMS's Object Explorer and choose
Tasks then Generate Scripts.
In SQL Server 2000 the steps are the same but the text on the right-click
menu is slighly different. It is All Tasks then Generate SQL Script.
Keith Kratochvil
"Samantha B." <nospam@.myisp.com> wrote in message
news:OrbxHHV8GHA.4552@.TK2MSFTNGP05.phx.gbl...
> Hi Everyone,
> Using SQL 2000 or 2005 (I have both in my lab), how can I copy the schema?
> I'm not trying to do a migration or anything, just need a copy of the
> schema to compare it to the schema from another database, for a homegrown
> app database our team is working on.
> Thanks in advance!
>
|||Samantha,
If all you are looking for is to compare a schema against a reference
schema, you can use the SchemaCrawler tool. SchemaCrawler outputs
details of your schema (tables, views, procedures, and more) in a
diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can
also output data (including CLOBs and BLOBs) in the same plain-text
formats. You can use a standard diff program to diff the current output
with a reference version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.

Copy Table and Data from one database to another

I am working on a migration project.

Doing the standard processing, taking source data in a staging database, where I then create the new target tables, transform the source data into the new target table structure, and load the data.

However, having created the new target tables in my staging database, I cannot accurately migrate these tables into the new database.

An example table.

The following is the script created by SQL Management Studio if I right click and script the table as CREATE.

CREATE TABLE [dbo].[tbPRO_Package](
[PRO_PackageID] [int] IDENTITY(1,1) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedBy] DEFAULT (suser_sname()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedDate] DEFAULT (getdate()),
[ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedBy] DEFAULT (suser_sname()),
[GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbPRO_Package_GUID] DEFAULT (newid()),
CONSTRAINT [PK_tbPackage_ID] PRIMARY KEY CLUSTERED
(
[PRO_PackageID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Note that the field PRO_PackageID has an identity, and is a primary key.

Also note the constraints on several fields and the default values.

If I try to move this table and data using the SQL Server Import and Export Wizard, telling it to drop and create the table, and to keep identity, it does not correctly generate the table defintion - it generates the following script

CREATE TABLE [Migration Staging].[dbo].[tbPRO_Package] (
[PRO_PackageID] int NOT NULL,
[PackageCode] varchar(8) NOT NULL,
[LKU_VatRateCode] nchar(10),
[CancellationCharge] decimal(18,2),
[CancellationMultiplier] decimal(18,2),
[CreatedDate] datetime NOT NULL,
[CreatedBy] varchar(50) NOT NULL,
[ModifiedDate] datetime NOT NULL,
[ModifiedBy] varchar(50) NOT NULL,
[GUID] uniqueidentifier NOT NULL
)

We've lost the identity, the PK,the constraints and the defaults.

If I use the SSIS Transfer SQL Server Objects Task, then the primary key is kept, but the identity is not, nor are the constraints or defaults.

Am I missing something? Is it actually POSSIBLE to accurately move tables between databases?"If

I use the SSIS Transfer SQL Server Objects Task, then the primary key

is kept, but the identity is not, nor are the constraints or defaults."

This behavior is by design to maintain backward compatibility with the Copy SQL Server Objects task in SQL Server 2000. Only two constraints (Primary key and foreign key constraints) are expected to be copied by Transfer Objects task.

Sorry for the inconvenience.

If transferring the entire database (as opposed to transferring only the tables) is okay, you can use "Transfer Database" task.

If you want to transfer only tables and preserve the table definition, I can think of two ways it can be done using SMO. You can implement C# code using SMO API to copy the tables or you can implement the same (in VB) inside a script task in SSIS. Both of these approaches should be pretty easy.

Hope this helps.|||so - it is not possible to accurately move a table and its data from one database to another, without resorting to writing code?

DESPITE there being two SQL tools that say they do it?

what is the purpose of these SQL tools then? For those oocassions where you want to move tables and data inaccurately?

you have stated this reponse before, in my post here.

Another poster asked in this thread:

MehtaR wrote:

Kaarthik,

I am not sure why you say "This behavior is by design

to maintain backward compatibility with the Copy SQL Server Objects

task in SQL Server 2000."
In SQL 2000 you can use DTS to transfer tables from one database to another preserving the primary as well as identity columns.
Rahul

edit: added smiley to ensure sarcasm detectors are activated |||I have two issues with this:

1) It's *not* transferringg foreign key constraints. That is what it's complaining about.

2) The "transfer database" task is a joke becuase it doesn't transfer the entire database, just the bits and pieces it chooses to transfer.

All I need is a simple, exact, bit for bit transfer from a 2000 to a 2K5 DB. This appears to be an impossibility in SSIS.|||

You can use Redgate SQL Packager to do what you are doing, since i have worked recently on a similar project. You can download their trial verison to play around with it I htink. SQL Packager will allow you to pick and choose the tables or any objects in your source db, and then package it. Then all you gotta do is to move the package(which is an exe file) to the server where you want to migrate it to. When you execute the package, it will cleanly create the db, with the chosen tables. If you are doing an upgrade, and then choose destination db when preparing the package, and it will create an upgrade package, to again move data/tables both(any that you choose). Thanks

MA

|||exactly!

surely a means to do this is fundamental requirement of a product such as SSIS?

I dont think this is too much to expect.|||I have several Redgate tools, such as packager, sql compare and data compare, and they are fantastically useful - I honest couldn't do my job without sql compare now.

But - we shouldn't need a third party tool to do such a basic task.|||There is a "wizard" that supposedly does this. I've not yet tested it and the way it works is quite bass-ackwards. It also only works for 2K/2K5 --> 2K5. What you have to do is go to your 2K5 DB, right click on the desired DB, go to tasks and down at the very bottom you'll see "Copy database". This theoretically creates a SSIS package which I am hoping I can them tweak if necessary.

What info I found on this is here:
http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

Copy Table and Data from one database to another

I am working on a migration project.

Doing the standard processing, taking source data in a staging database, where I then create the new target tables, transform the source data into the new target table structure, and load the data.

However, having created the new target tables in my staging database, I cannot accurately migrate these tables into the new database.

An example table.

The following is the script created by SQL Management Studio if I right click and script the table as CREATE.

CREATE TABLE [dbo].[tbPRO_Package](
[PRO_PackageID] [int] IDENTITY(1,1) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedBy] DEFAULT (suser_sname()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedDate] DEFAULT (getdate()),
[ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedBy] DEFAULT (suser_sname()),
[GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbPRO_Package_GUID] DEFAULT (newid()),
CONSTRAINT [PK_tbPackage_ID] PRIMARY KEY CLUSTERED
(
[PRO_PackageID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Note that the field PRO_PackageID has an identity, and is a primary key.

Also note the constraints on several fields and the default values.

If I try to move this table and data using the SQL Server Import and Export Wizard, telling it to drop and create the table, and to keep identity, it does not correctly generate the table defintion - it generates the following script

CREATE TABLE [Migration Staging].[dbo].[tbPRO_Package] (
[PRO_PackageID] int NOT NULL,
[PackageCode] varchar(8) NOT NULL,
[LKU_VatRateCode] nchar(10),
[CancellationCharge] decimal(18,2),
[CancellationMultiplier] decimal(18,2),
[CreatedDate] datetime NOT NULL,
[CreatedBy] varchar(50) NOT NULL,
[ModifiedDate] datetime NOT NULL,
[ModifiedBy] varchar(50) NOT NULL,
[GUID] uniqueidentifier NOT NULL
)

We've lost the identity, the PK,the constraints and the defaults.

If I use the SSIS Transfer SQL Server Objects Task, then the primary key is kept, but the identity is not, nor are the constraints or defaults.

Am I missing something? Is it actually POSSIBLE to accurately move tables between databases?"If

I use the SSIS Transfer SQL Server Objects Task, then the primary key

is kept, but the identity is not, nor are the constraints or defaults."

This behavior is by design to maintain backward compatibility with the Copy SQL Server Objects task in SQL Server 2000. Only two constraints (Primary key and foreign key constraints) are expected to be copied by Transfer Objects task.

Sorry for the inconvenience.

If transferring the entire database (as opposed to transferring only the tables) is okay, you can use "Transfer Database" task.

If you want to transfer only tables and preserve the table definition, I can think of two ways it can be done using SMO. You can implement C# code using SMO API to copy the tables or you can implement the same (in VB) inside a script task in SSIS. Both of these approaches should be pretty easy.

Hope this helps.|||so - it is not possible to accurately move a table and its data from one database to another, without resorting to writing code?

DESPITE there being two SQL tools that say they do it?

what is the purpose of these SQL tools then? For those oocassions where you want to move tables and data inaccurately?

you have stated this reponse before, in my post here.

Another poster asked in this thread:

MehtaR wrote:

Kaarthik,

I am not sure why you say "This behavior is by design

to maintain backward compatibility with the Copy SQL Server Objects

task in SQL Server 2000."
In SQL 2000 you can use DTS to transfer tables from one database to another preserving the primary as well as identity columns.
Rahul

edit: added smiley to ensure sarcasm detectors are activated |||I have two issues with this:

1) It's *not* transferringg foreign key constraints. That is what it's complaining about.

2) The "transfer database" task is a joke becuase it doesn't transfer the entire database, just the bits and pieces it chooses to transfer.

All I need is a simple, exact, bit for bit transfer from a 2000 to a 2K5 DB. This appears to be an impossibility in SSIS.|||

You can use Redgate SQL Packager to do what you are doing, since i have worked recently on a similar project. You can download their trial verison to play around with it I htink. SQL Packager will allow you to pick and choose the tables or any objects in your source db, and then package it. Then all you gotta do is to move the package(which is an exe file) to the server where you want to migrate it to. When you execute the package, it will cleanly create the db, with the chosen tables. If you are doing an upgrade, and then choose destination db when preparing the package, and it will create an upgrade package, to again move data/tables both(any that you choose). Thanks

MA

|||exactly!

surely a means to do this is fundamental requirement of a product such as SSIS?

I dont think this is too much to expect.|||I have several Redgate tools, such as packager, sql compare and data compare, and they are fantastically useful - I honest couldn't do my job without sql compare now.

But - we shouldn't need a third party tool to do such a basic task.|||There is a "wizard" that supposedly does this. I've not yet tested it and the way it works is quite bass-ackwards. It also only works for 2K/2K5 --> 2K5. What you have to do is go to your 2K5 DB, right click on the desired DB, go to tasks and down at the very bottom you'll see "Copy database". This theoretically creates a SSIS package which I am hoping I can them tweak if necessary.

What info I found on this is here:
http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx