Script out the entire database in SQL 2005

Question

Post reply

Script out the entire database in SQL 2005

msforumpost

Right there with Babe

Points: 795
More actions
April 7, 2009 at 4:29 am

#132904

Hi All,
In SQL 2005, is there a way that I can script out the entire database, so that it contains all the create scripts for all the tables, views, constraints, functions, procedure in the right sequence. Also is there a way that I can script out data , so that after I create the database objects, I want to insert data into them.
In Effect , I want to do something like a backup and restore.
Thanks in Advance,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 1

In Effect , I want to do something like a backup and restore

In that case, why cant you prefer backup and restore.

In SQL 2005, is there a way that I can script out the entire database, so that it contains all the create scripts for all the tables, views, constraints, functions, procedure in the right sequence

Right-click on the database in Mgmt studio you have generate sql scripts.Go through that wizrd and you can script out all the objects .

Also is there a way that I can script out data , so that after I create the database objects, I want to insert data into them.

Use Export data wizard for this.I think you cannot script out the data.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 2

Right click on your database -> Tasks -> Generate scripts and follow up the wizard

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

To perform both, you might want to look into using SSIS. You'll get a lot more control although it will require more work out of you. Otherwise, the other posts point in the right direction.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

msforumpost Right there with Babe Points: 795 More actions · Answer 4

Hi,

Thanks for the help. Now I am able to script out the database

i.e. Right click on your database -> Tasks -> Generate scripts and follow up the wizard

and am I able to create the database objects using this script.

Now I have created insert scripts, which insert data into the tables.. These insert scripts look like..

INSERT INTO [TableA] ([col1],[col2],[col3])VALUES("onething",'something2','nothing')

INSERT INTO [TableB] ([colB1],[colB2],[colB3])VALUES("onethingB",'something2B','nothingB')

But while I run these scripts, I need to make sure that I run the scripts in the right order, else I get errors that the data in the table that is referenced does not exist. So I need to insert data in the "base most" table first.. So how do I determine the sequence of dependencies of table and hence the sequence in which the insert scripts for the table should be run.

Thanks in Advance

Lowell SSC Guru Points: 323518 More actions · Answer 5

I've got an upcoming article on how to do this in TSQL, with a complete solution:

all objects in dependancy order, along with the data.

while you are waiting for the article on SQlServerCentral, you can read it here:

Create Table via DDL

just jump to the end, get the zip file and put all the stored procs in master.

change to your database,

exec sp_export_all @withData=1

and you are done!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!