Creation scripts from a database

I would like to know the sql command to generate all the scripts of a database so that I can replicate the same database for testing purpose. For instance, I need those scripts to help me re-create the database itself and indexes, triggers, and stored procedures of it if possible.

I remember there was a command to display all these scripts in Sql but I forgot it. If someone knows it, please help. Thanks in advance.

blumonde

Maybe you can use Enterprise Manager, select the database in EM, right click it, choose 'All tasks'->'Generate Script', click 'Show All'. Then you can choose objects to be scripted, and save the script to file system.|||

If you are in SQL Server 2000 in Query Analyzer in the Object browser with the database closed in Enterprise Manager right click on the database and click on create and you will get the create database statement. If you need the create statement for any object in the database make sure the database is open in Enterprise Manager and do the same per object and you will generate the create statement of all the objects.

In SQL Server 2005 do the same in Management Studio to generate the create statement.

Another way to move database is to use the Backup and Restore wizard and use the restore from device option to recreate the database. If your test SQL Server in not on the network remember to put the .BAK file in the Backup subfolder in Microsoft SQL Server folder in Programs. Hope this helps.

|||

Hi Iori_Jay and Caddre,

Thanks for your help. I have the scripts now. Thanks again. I appreciate it a lot.

blumonde

Creating XML output...unusual/impossible format?

Hi all,

I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.

The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)

The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.

Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?

I want my SYMBOL tag to be the actual SYMBOL for the stock fund.

Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.

Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!

Thanks!You cannot achieve this with a single SELECT statement because you can have only one column name for the entire result set.(you stated this already). If you need to have the column value as a tag then a stored procedure is the way to go where you can generate your own XML TAGs.
Hi all,

I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.

The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)

The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.

Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?

I want my SYMBOL tag to be the actual SYMBOL for the stock fund.

Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.

Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!

Thanks!|||Thanks for the reply.

I was afraid of that. The previous version of the product used a C++ program to generate the XML (or pseudo-XML) output. I was hoping it was just because the developer didn't know how to use the XML extraction stuff in SQL Server. *sigh*

Oh well, I guess I get to gain some C# practice, as the C++ application has the source of the data "hard coded" in the logic, and we need to use a predefined, "standard" linked server reference (aka "alias").|||"Can't be Done"?

The three words I tell developers to never let me hear, otherwise I bring out the baseball bat

Dude, you know the drill

DDL, DML, I gues we got the expected results already...|||Never mind...but I'm only guessing...

USE Northwind
GO

CREATE TABLE myTable99(FundSymbol char(5), FundName varchar(255))
GO

INSERT INTO myTable99(FundSymbol, FundName)
SELECT 'AMRGX', 'AMERICAN GROWTH D' UNION ALL
SELECT 'AHERX', 'AHERX HERITAGE FUND' UNION ALL
SELECT 'AMRVX', 'AMERICAN INVESTORS GROWTH FUND'

SELECT XML_Output
FROM (
SELECT '<?xml version="1.0" encoding="utf-8"?>' AS XML_Output, 1 AS XML_Group, Null AS FundSymbol
UNION ALL
SELECT '<FUNDS>'AS XML_Output, 2 AS XML_Group, Null AS FundSymbol
UNION ALL
SELECT REPLICATE(' ',20)+'<'+FundSymbol+'>'+CHAR(13)+CHAR(10)
+ REPLICATE(' ',40)+'<NAME>'+FundName+'</NAME>'+CHAR(13)+CHAR(10)
+ REPLICATE(' ',20)+'</'+FundSymbol+'>'+CHAR(13)+CHAR(10)AS XML_Output
, 3 AS XML_Group, FundSymbol
FROM myTable99
UNION ALL
SELECT '<FUNDS>'AS XML_Output, 4 AS XML_Group, Null AS FundSymbol
) AS XXX
ORDER BY XML_Group, FundSymbol
GO

DROP TABLE myTable99
GO|||*sigh* What can I say? Every day you guys amaze me more-n-more :)

That's EXACLY what I was a-lookin for! I appreciate you doing my homework for me *hanging head* I tend to forget the usefullness of UNION, and have yet to use "REPLICATE", so now I need to go look THAT one up in BOL so that I can once again expand my horizons just a little more.

Funny (to me, anyway) that because every other application in our world here that creates XML output uses the FOR XML directive to create the output, that I get tunnel vision and just can't seem to see the easy answer lies in "standard" SQL. I guess kinda like my kids, who can't seem to get their heads around the ideas that (a) you can make your own ice cream, (b)clothes can come from a sewing machine instead of a store, (c) there's a way to change your oil without going to Jiffy Lube, and myriad other such examples of "OMG, you mean you can do that the old-fashioned way?".

But I digress...Thanks once again, Brett, for taking the time and effort to plainly show how I can get around yet another stumbling block.

It's too bad though, that since it can't be done, I will have to disregard your fantasy code and open my C# book. ;)

Thanks again!|||A mere trifle...

I was interested, because of a discussion I had with a Java developer.

If I could deliver the content in a manner like you are looking for, then the development life cycle for java gets cut in half.

I'm just exploring this now, but I was leaning on having metadata stored in the database, where I could configure all of the results via table.

Each element would have it's own properties stored.

So if the don't like the text to be black, I can make it red, on the fly. No application release headaches.

I was going to extend that and see if I could also incorporate the style sheets in the database as well..

FOR XML...what a bunch of crap

Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
Thanks
Every table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
David Portas
SQL Server MVP
|||If you would like to have a tool to create the tables, generate the triggers, and manage the audit trail, consider WT6 - one of the SQL Server Tools applications...
http://www.sqlservertools.us
If it's compatible with your database architecture it will do what you have indicated, as well as a lot more.
|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>

Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
ThanksEvery table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
David Portas
SQL Server MVP
--|||If you would like to have a tool to create the tables, generate the triggers
, and manage the audit trail, consider WT6 - one of the SQL Server Tools app
lications...
http://www.sqlservertools.us
If it's compatible with your database architecture it will do what you have
indicated, as well as a lot more.|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>sql

Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
ThanksEvery table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
--
David Portas
SQL Server MVP
--|||If you would like to have a tool to create the tables, generate the triggers, and manage the audit trail, consider WT6 - one of the SQL Server Tools applications..
http://www.sqlservertools.u
If it's compatible with your database architecture it will do what you have indicated, as well as a lot more|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>

creating replication with extra column

Hi ,
I have a few tables that i wanted to replicate from ServerA to ServerB ,
however, these tables in ServerB needs a country code column as it's sort of
a Data Center.
How i can add the column during replication setup ?
kindly advise
tks & rdgs
Use the ALTER TABLE statement to add the column to the subscription table.
Note the column must allow NULLs or have a DEFAULT constraint.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:3329F606-26D6-4D88-8E45-D623C8787E49@.microsoft.com...
> Hi ,
> I have a few tables that i wanted to replicate from ServerA to ServerB ,
> however, these tables in ServerB needs a country code column as it's sort
> of
> a Data Center.
> How i can add the column during replication setup ?
> kindly advise
> tks & rdgs

creating replication with extra column

Hi ,
I have a few tables that i wanted to replicate from ServerA to ServerB ,
however, these tables in ServerB needs a country code column as it's sort of
a Data Center.
How i can add the column during replication setup ?
kindly advise
tks & rdgsUse the ALTER TABLE statement to add the column to the subscription table.
Note the column must allow NULLs or have a DEFAULT constraint.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:3329F606-26D6-4D88-8E45-D623C8787E49@.microsoft.com...
> Hi ,
> I have a few tables that i wanted to replicate from ServerA to ServerB ,
> however, these tables in ServerB needs a country code column as it's sort
> of
> a Data Center.
> How i can add the column during replication setup ?
> kindly advise
> tks & rdgs