scripting an entire database along with the data

Hi,
I would like to know how to create a script for an exsisting database such
that the script also contains statements to recreate all the data in the
database aswell.
regards,
Ahmed.Ahmed Shafi wrote:
> Hi,
> I would like to know how to create a script for an exsisting database
> such that the script also contains statements to recreate all the
> data in the database aswell.
There was a post about a free tool http://www.sqlscripter.com in
sqlserver.tools recently.
You might also want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
There is also really a good one in Vyas's site,
http://vyaskn.tripod.com/code/generate_inserts.txt
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create insert statements via any of the following tools
Largo SQL Tools -- http://www.largosqltools.com/
ObjectScripter -- http://www.rac4sql.net/
QALite -- http://www.rac4sql.net/
Lockwood Tech -- http://www.lockwoodtech.com/
You can create the database script via some of these tools. You can =
also use Enterprise Manager to script the DDL for you.
With that said, what is your goal? If you want to "save" or "move" your =
database, an easier method might be BACKUP and RESTORE. You can execute =
a Transact-SQL backup command to the database server. This command will =
backup your database (tables, data, stored procedures...) to a file =
that you can backup to tape or burn to CD. You can also use this file =
to recreate your entire database.
--=20
Keith
"Ahmed Shafi" <aflatoon99 At hotmail DOT com> wrote in message =
news:OxMW4t5BEHA.1236@.TK2MSFTNGP11.phx.gbl...
> Hi,
>=20
> I would like to know how to create a script for an exsisting database =
such
> that the script also contains statements to recreate all the data in =
the
> database aswell.
>=20
> regards,
> Ahmed.
>=20
>|||Hi Keith & Ahmed,
take a look at this script we give away
http://www.innovartis.co.uk/downloa..._statements.zip
you may want to take a look at what else we have to offer...|||>> you may want to take a look at what else we have to offer...
You are certainly good at copying others' code and claiming it as your own.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:61AD85AE-450A-4599-9503-EAB1CD7AF58B@.microsoft.com...
Hi Keith & Ahmed,
take a look at this script we give away
http://www.innovartis.co.uk/downloa..._statements.zip
you may want to take a look at what else we have to offer...|||I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
MODIFIED/IMPROVED!
GET BENT!!!

>--Original Message--
offer...
>You are certainly good at copying others' code and
claiming it as your own.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:61AD85AE-450A-4599-9503-
EAB1CD7AF58B@.microsoft.com...
>Hi Keith & Ahmed,
>take a look at this script we give away
>http://www.innovartis.co.uk/downloa...reate_insert_st
atements.zip
>you may want to take a look at what else we have to
offer...
>
>.
>|||First of all, who are you to 'give away' my code, on your website?
I did look at the code, and you did make a few modifications here and there.
But it is a minimum courtesy in software industry, to credit the original
author when using his/her code.
You wrote "There is no claim that it is your code". But hey, the code is
hosted on your website. You haven't bothered giving credit to me. Isn't that
implying that it's your code?
If you want to continue offering that code on your website, you better add
my name and URL in the header of that script. Or else, remove it from your
site.
I hate plagiarism.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:c3cc01c408f1$201cdec0$a301280a@.phx.gbl...
I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
MODIFIED/IMPROVED!
GET BENT!!!

>--Original Message--
offer...
>You are certainly good at copying others' code and
claiming it as your own.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:61AD85AE-450A-4599-9503-
EAB1CD7AF58B@.microsoft.com...
>Hi Keith & Ahmed,
>take a look at this script we give away
>http://www.innovartis.co.uk/downloa...reate_insert_st
atements.zip
>you may want to take a look at what else we have to
offer...
>
>.
>|||mark baekdal wrote:

>I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
>MODIFIED/IMPROVED!
>
>
Yeah, right. Read your own site:
<begin quote from your site>
The DB Ghost Process
*What you do, as a one-time process, is to:*
* Script out, using Enterprise Manager, your entire production
database's schema and static data into individual scripts. To
script your static data use this _stored procedure_ [link to
Vyas's sp] <downloads/sp_create_insert_statements.zip>. Some
companies actually market products to do this...
<end quote from your site>
I guess you find it easier to steal products to do this, remove the
copyright and authorship info and then throw a tantrum when you get
found out.
People like you suck.
Vyas's original code:
BEGIN
/ ****************************************
***********************************
********************************
Procedure: sp_generate_inserts (Build 22)
(Copyright 2002 Narayana Vyas Kondreddi. All rights reserved.)
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some
other location.
This procedure is also useful to create a database setup, where
in you can
script your data along with your table definitions.
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Acknowledgements:
Divya Kalra -- For beta testing
Mark Charsley -- For reporting a problem with scripting
uniqueidentifier columns with NULL values
Artur Zeygman -- For helping me simplify a bit of code for
handling non-dbo owned tables
Joris Laperre -- For reporting a regression bug in handling
text/ntext columns
Tested on: SQL Server 7.0 and SQL Server 2000
Date created: January 17th 2001 21:52 GMT
Date modified: May 1st 2002 19:50 GMT
Email: vyaskn@.hotmail.com
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL
Server 2000's sql_variant data types
Whenever possible, Use @.include_column_list parameter to ommit
column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data
(Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables
in this procedure to their respective unicode counterparts
like nchar and nvarchar
The stolen code on your site:
BEGIN
/ ****************************************
***********************************
********************************
Procedure: sp_create_insert_statements
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some
other location.
This procedure is also useful to create a database setup, where
in you can
script your data along with your table definitions.
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL
Server 2000's sql_variant data types
IMPORTANT: Whenever possible, Use @.include_column_list parameter
to ommit column list in the INSERT statement, for better results
>GET BENT!!!
>
>
>offer...
>
>claiming it as your own.
>
>wrote in message
>
>EAB1CD7AF58B@.microsoft.com...
>
>atements.zip
>
>offer...
>|||The code has been modified extensively.
Deletes have been added.
A transaction has been created to make it atomic/durable.
Triggers have been turned off and then turned back on to
strop unwanted inserts.
Error handling has been added for completeness.
Transaction checking has been added for portability.
All of the above has added value to the script - that was
my intention. I did try to contact Vyas originally (about
two years ago) through the contact on
http://planetsourcecode.com/ where I originally found it,
however no-one got back to me. If I was trying to sell it
then I could understand your comments, but it is posted
here to help, and it is free, and it has been
modified/improved which may well help Ahmed - the whole
point of the excercise.
I will put in comments crediting Vyas for the original
work done, it has moved on though and it is not my
intention to ever profit from the work - I repeat - I
think I added value to it and it will always remain free.

>--Original Message--
>First of all, who are you to 'give away' my code, on
your website?
>I did look at the code, and you did make a few
modifications here and there.
>But it is a minimum courtesy in software industry, to
credit the original
>author when using his/her code.
>You wrote "There is no claim that it is your code". But
hey, the code is
>hosted on your website. You haven't bothered giving
credit to me. Isn't that
>implying that it's your code?
>If you want to continue offering that code on your
website, you better add
>my name and URL in the header of that script. Or else,
remove it from your
>site.
>I hate plagiarism.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:c3cc01c408f1$201cdec0$a301280a@.phx.gbl...
>I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
>MODIFIED/IMPROVED!
>GET BENT!!!
>
>offer...
>claiming it as your own.
>wrote in message
>EAB1CD7AF58B@.microsoft.com...
t
>atements.zip
>offer...
>
>.
>|||And another modification made
An order by clause was added to make sure the insert
statements are ordered by the primary key if it exists.
Previous to this contact - I hadn't recieved any even
though I did ask - therefore I could not ask anyone
requiring answers to questions to contact Vyas as I
couldn't get them. This is why there wasn't any
references. That has since changed and I have talked with
Vyas who seems happy with the arrangement as it now
stands.
I for one never want to have plagiarist next to my name.
And the end result is everyone has a damn fine piece of
code for free - that must be a good ending to this story!sql