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

Script to copy entire table

I'm a newbie to script writing. I'm trying to write a script to copy all
data from a table to the same table in a 2nd database. Both databases are
on the same server and are identical in design. I can do this with DTS but
wanted a script I could email to a user to run in Query Analyzer.

Example:
Copy entire table called 'Customers' in the 'Data01' database to
table 'Customers' in the 'Data02' database

I want to overwrite all data in the destination table.

Thanks"sqlnewbie" <sqlnewbie@.yahoo.com> wrote in message
news:k4PYb.27374$vs5.13502@.newssvr25.news.prodigy. com...
> I'm a newbie to script writing. I'm trying to write a script to copy all
> data from a table to the same table in a 2nd database. Both databases are
> on the same server and are identical in design. I can do this with DTS
but
> wanted a script I could email to a user to run in Query Analyzer.
> Example:
> Copy entire table called 'Customers' in the 'Data01' database to
> table 'Customers' in the 'Data02' database
> I want to overwrite all data in the destination table.
> Thanks

/* Replace all data in the destination table */

use Data02
go

truncate table dbo.Customers
insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers

/* Insert only data which isn't already there */

use Data02
go

insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers c1
where not exists (select *
from dbo.Customers c2
where c1.PrimaryKeyCol = c2.PrimaryKeyCol)

Note that TRUNCATE TABLE requires certain permissions (see Books Online),
and won't work if the table is referenced by foreign keys. In this case, you
can use "DELETE FROM dbo.Customers".

I would be careful about sending scripts to users, as they often seem to run
them in the wrong place at the wrong time - moving data should really be a
DBA's task (although I appreciate that not everyone has a DBA available).
You may want to back up the database first, just in case.

Simonsql

Script database using stored procedure

Is it possible to use a stored procedure to script an entire database in MS SQL 2000?You can use the CREATE DATABASE statement in a stored proc.

What you can't do is run the following:

CREATE VIEW , CREATE PROC, CREATE RULE, CREATE DEFAULT , CREATE RULE

You can script the entire database from enterprise manager which would be sufficient for the any purpose i can think of.

Stored Procs should be used for heavily repeated processes not one off's.

Hope this helps ...|||Yes it is possible, but is bad idea... maybe better SQLDMO?

sp_helptext
Prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.
Syntax
sp_helptext [ @.objname = ] 'name'

sp_help
Reports information about a database object (any object listed in the sysobjects table), a user-defined data type, or a data type supplied by Microsoft SQL Server.

Syntax
sp_help [ [ @.objname = ] name ]

http://www.apexsql.com/|||well not exactly, let me explain.

What I want to do is call a procedure that exports the entire database design (tables views etc) to a sql-script file.

Pretty much the same as the server is able to do in the enterprise manager. The only difference is, that I want to be able to call the procedure from an application.

Clearer?

Rolf|||Hi Rolf,

What you might want to do is use DTS to copy the objects across to whatever server you wish

have a look at DTS - Copy SQL Server Objects

You can set this to copy the objects but not the data

If you need to call this from an application then you can use the cmd line DTSRun utility which will allow you to run it from a cmd prompt.

Hope this helps .....|||OK thanks,

I know of this possibility but I'd prefer to avoid it.

thanks anyway.

R.|||I swear that at one time I found a command-line utility that I could call from xp_cmdshell to output a database script identical to Enterprise Manager's scripting results. But now I can't find it. :(

I'll keep looking, but in the meantime check out the SQL-DMO SCRIPT method in Books Online.|||Now we're getting somewhere. I'll try this first and will return if it doesn't work.

Thanks.

R.|||There is sample VB app from Micosoft on how to use the SQL-DMO Script methods referenced in SQL Server Books Online. If interested, look in the Books Online index for "SQL-DMO", then "samples", then "SQLScripts".

I found the source on my machine in a file named GenSQLScriptsDemo.zip.