How could I Generate SQL Script in SQL server 2005?

Forums › IT Pro and developers › How could I Generate SQL Script in SQL server 2005?

addisu33

24 posts

Geek

#19241 11-Feb-2008 05:06

Hello, guys how are you doing?

There are steps how to GENERATE SQL SCRIPT in SQL server 2000

Right click on the temporary data and pointing all tasks
Select Generate SQL Script, in the dialogue box click show all.
under script all objects, check the box of all tables
Click options, under table scripting options, check the box of Script indexes, and Script primary keys, FOREGIN keys, defaults, and check constraints.
Click General then preview. Now Copy the Script preview, click Ok. Close then cancel.
Go to tools, SQL Query Analyzer, Paste. Now Execute Query and that's it.

Right now, this data is Empty and ready for Importing data from another database. Meanwhile, I have done the same thing in SQL server 2005 ( Management studio) but after Executed the Query still the data is there. but it should be gone ( tables should be Empty) inorder to import data. so, how could you Import data if your database has full of data?

so, please guys help me?

thank you very much

signz

95 posts

Master Geek

#109803 11-Feb-2008 09:32

Generating scripts is designed to allow you to re-create a database, not destory your current one and just delete the data. For this reason the scripts generated should have IF NOT EXISTS statements to only re-generate tables etc that do not already exist. In normal use this would prevent you from accidentally killing your source database but still run correctly in an empty database (which is what it was designed for).

Personally, just discussing it scares me but ... If you REALLY want to blow away your source database then turn OFF that option in the generate scripts tool.

An alternative if you want to just blow away just test data is to write a script to delete the data you want to get rid of:

DELETE FROM OrderDetails
DELETE FROM Orders
DELETE FROM Customers

etc.

Dale

addisu33

24 posts

Geek

#109997 11-Feb-2008 22:22

                     Hello, signz

             I think it much better to move on with SQL server 2000 because it is fast, easy to use and saves time. because as i said i am new for SQL server 2005 and i have asked the Question of Generating Script more than 3 websites and i think most people have difficulties with SQL server 2005. so, i have decided to move on with SQL server 2000.
          Is there any download availble for SQL server 2000 SP3?

            THANK YOU VERY MUCH

chakkaradeep

799 posts

Ultimate Geek

Trusted

#110000 11-Feb-2008 22:37

addisu33:
Right now, this data is Empty and ready for Importing data from another database. Meanwhile, I have done the same thing in SQL server 2005 ( Management studio) but after Executed the Query still the data is there. but it should be gone ( tables should be Empty) inorder to import data. so, how could you Import data if your database has full of data?

Generate Scripts doesnt copy data from one database to another or one table to another, it is to generate the SQL scripts that you can use to create those database or tables. So, if you want to put data, either you have to backup from the source and restore at the target server. And what you mean by - "after Executed the Query still the data is there." which is being executed and what data is there?

I am really confused with what you are asking

Regards,
Chaks

Desktop : Intel Quad Core Q9400 2.66GHz - 8GB RAM - 500 GB + 500 GB HDD - NVidia GeForce 9800GT - LG246WH Flatron Display - Windows Server 2008 R2 Enterprise with Hyper-V
Virtual Machine : Powered by Hyper-V and VMWare Workstation
Laptop: HP dv7-3004TX Entertainment Notebook PC | HP Touchsmart tx2 1119au - Windows 7 Ultimate x64
Mac: iMac 21.5" Snow Leopard
Mobile : iPhone 3GS

addisu33

24 posts

Geek

#110028 12-Feb-2008 03:38

          Hi, i want you to look carefully for what i am doing?

    Example: - I have two databases
>Temporary data ( ACCTEMP) – We use for when we change any primary key, or design tables…….etc
>Permanent data ( Accounting)- this is customer database
      There are steps how to GENERATE SQL SCRIPT in SQL server 2000

Right click on the temporary data and pointing all tasks
Select Generate SQL Script, in the dialogue box click show all.
under script all objects, check the box of all tables
Click options, under table scripting options, check the box of Script indexes, and Script primary keys, FOREGIN keys, defaults, and check constraints.
Click General then preview. Now Copy the Script preview, click Ok. Close then cancel.
Go to tools, SQL Query Analyzer, Paste. Now Execute Query and that's it.
The last thing what I do is, just Back up the temporary data and Restore the permanent data with the Backed up data.

                 Now See the tables of your Temporary data one by one it's Empty and Ready for Importing data From your permanent data. tell me if you have more 10 different databases, Each database has around 800 tables just for 15 customers, could you Script each table one by one? I am talking about SQL server 2000 and this is my job and i took this training from my company. and when I Update databses, they told me to use this steps.

        THANKS

signz

95 posts

Master Geek

#110051 12-Feb-2008 10:12

This sounds VERY dangerous to me.

Personally, I script all changes to databases so that if I need to add (or modify) a table, index, field ... whatever then I just execute the script. I have 15 clients running across 7 cities and I can assure you that the way you are tackling the problem sounds like a good way to lose data.

I agree that using a generated script should create you a copy of your development scheme which you can then import data into. I just worry that unless the script you are using to import the data is not doing everything in the right order (due to relationships) and checking as it goes (ie. source and destination record counts on each table) then something is going to get missed out.

As for SQL 2005 Vs SQL 2000 I can't see there is any different to the logic or behavior as long as you are running your generated script on an *empty* database.

DS

chakkaradeep

799 posts

Ultimate Geek

Trusted

#110272 12-Feb-2008 23:20

addisu33:
Example: - I have two databases
>Temporary data ( ACCTEMP) – We use for when we change any primary key, or design tables…….etc
>Permanent data ( Accounting)- this is customer database
There are steps how to GENERATE SQL SCRIPT in SQL server 2000

The last thing what I do is, just Back up the temporary data and Restore the permanent data with the Backed up data.
Now See the tables of your Temporary data one by one it's Empty and Ready for Importing data From your permanent data.

So, you mean to say when you do a Backup from Temporary and then Restore them to the Permanent, you lose data in Temporary database?? This should not be the case. I do Backup and Restore here with SQL Server 2005 and I dont see this behaviour. I think there is something wrong. And when you backup fully, they are backed up along with scripts + data, so if you just say Restore From Database and give a new database name, all the tables,stored procedures will be created for you anyhow.

addisu33

24 posts

Geek

#110301 13-Feb-2008 03:30

Hello, guys how are you doing?

First what I want to say is, my company has been working with this kind of steps almost 12 years and they have developers and designers…….and also have like me Network administrator and familiar with SQL server 2000.

I think it is better to try in your machine. Any how, see it now from the first step what I am doing

Example: - I have two databases
>Temporary data (ACCTEMP) – We use for when we change any primary key, or design tables…….etc
>Permanent data (Accounting) - this is customer database
THIS IS how to GENERATE SQL SCRIPT

1. Right click on the temporary data and pointing all tasks 2. Select Generate SQL Script, in the dialogue box click show all. 3. under script all objects, check the box of all tables 4. Click options, under table scripting options, check the box of Script indexes, and Script primary keys, FOREGIN keys, defaults, and check constraints. 5. Click General then preview. Now Copy the Script preview, click Ok. Close then cancel. 6. Go to tools, SQL Query Analyzer, Paste. Now Execute Query and that's it.

NOW EXPORT THE PERMANENT DATA TO THE GENERATED DATA

1. Right click the permanent Data, select Tasks, and then click Export Data2. In the data transformation service, click next, next, choose the destination database then next, next, select all tables and views.3. Uncheck the views, next, next, finish. Now execute the package. And finish. The last thing what I do is, just Back up the temporary data and Restore the permanent data with the Backed up data. And that's it.

Quic Broadband

Move to New Zealand's best fibre broadband service (affiliate link). Free setup code: R587125ERQ6VE. Note that to use Quic Broadband you must be comfortable with configuring your own router.

addisu33

24 posts

Geek

#110306 13-Feb-2008 03:59

                    Of course, I understand that what you are saying is true, because I am loosing data but this is the way how we are working. Some times, after I update we see some errors in the customer data then this time, they give me a SCRIPT to run in Query analyzer.

           I think you have got my Idea

                 thank you very much

chakkaradeep

799 posts

Ultimate Geek

Trusted

#110311 13-Feb-2008 07:25

addisu33:

First what I want to say is, my company has been working with this kind of steps almost 12 years and they have developers and designers…….and also have like me Network administrator and familiar with SQL server 2000.

Well, We dont see a point why you are doing this (for the past 12 years!) when you see the data is being lost in the Temporary Database and there is sure some mistake in the process. I have also done these things in Windows 2000 and the data stays there in the Temporary Database and you were asking in SQL Server 2005 and that is the topic of this thread and I told you its not the way and you can just use Backup & Restore option in SQL Server 2005 and its straight forward.

If you dont plan to find whats the problem and say you are doing the same mistake for so many years, you are free to go with it