Update: This article is re-written with SQL Server 2008 R2 instance over here: SQL SERVER – 2008 – 2008 R2 – Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
Following quick tutorial demonstrates how to create T-SQL script to copy complete database schema and all of its objects such as Stored Procedure, Functions, Triggers, Tables, Views, Constraints etc. You can review your schema, backup for reference or use it to compare with previous backup.
Step 1 : Start
Step 2 : Welcome Screen
Step 3 : Select One or Multiple Database
If Script all objects in the selected database checkbox is not selected it will give options to selected individual objects on respective screen. (e.g. Stored Procedure, Triggers and all other object will have their own screen where they can be selected)
Step 4 : Select database options
Step 5 : Select output option
Step 6 : Review Summary
Step 7 : Observe script generation process
Step 8 : Database object script generation completed in new query window
Reference : Pinal Dave (https://blog.sqlauthority.com), All images are protected by copyright owner SQLAuthority.com
673 Comments. Leave new
I want to create new database from a template database on same server with all objects and also data in tables. I want to run a store procedure to which I will pass two parameters and and store procedure will required to copy all objects with data in tabels to new database where new database is already created before runing store procedure.
Is there any way to copy all objects tables with data from one database to blank database in MS-SQL 2000?
Thanks,
Manoj
It is better to take backup and restore it in other database
Nice suggestion.
But I have a question:
In SqlServer2005 I can script the creation of a specific table (by right-clicking on the table and selecting Script table as…\CREATE To…).
With SqlServer2005 Management Studio Express I can customize the script with the Tools\options\script settings (I can tell SqlServer2005 Express to include indexes, triggers, … in the creation script), but with SqlServer 2005 Management Studio (no Express) this customization disappears and by default in the table creation script these objects are not included.
Can you tell me how can I script a single table including any additional object without using the widzard you described and without scripting one by one any additional object?
Thank you for any suggestion.
HI…
I am new to database sql server 2005.I want to change the data type of a column in the table. Therefore i created a new column with the required data type and tried copying the contents of the main column to the new column.But the problem is, the table is around 4gb and therefore when i try to execute the above said procedure my transaction log gets filled up really fast.I have around 25gb of free space in the drive.The transaction exceeds the available space and i get an error.I tried executing the transaction in the simple recovery model and also full.but both ways the transaction log filled up really quickly and exceeded the available space.
It would be nice if i could know the ideal method to implement the above procedure..
Thanks…
Hi Pinal,
I want to generate script of table with data. Is it possible with sql server 2005??
Thanks & regards
No. However it is possible in version 2008
Hi Pinal,
I would like to know that how can we compare two database in the sql server 2005?
Thanks,
NT
Hi,
Pls Help.
How can I create the script for individual objects in SQL server 2005.
In 2000 it is a easy task in Enterprise Manager.
I already generated script of one database. and make other database same as first one. Now I want to copy all the data from first database to new one.
thanks
Use import/Export wizard
Hello,
Unfortunately the generate script wizard’s results are quite unpredictable. If you generate a script using this tool, restore to a fresh database and repeat the process for this second database you get two hugely different results. This tool is not suitable for keeping track of your schema changes using code versioning, and unfortunately I found no better replacement for it. Also not this tool has a bug in as it generates descriptive header comments for RULE objects even when asked not to.
It is mind dazzling how a database engine such as sql2005 has no working, predictable, configurable schema creation tool. I doubt sql2008 will make any inroads in this territory. I find it mind boggling that there are so many developers out there that have had no concern (probably) for database schema versioning.
I absolutely agree with you. Can you suggest something that could help to solve that problem?
Is there any tool here that can check the script generated by wizard and report about inconsistencies in that script?
Because you never can see the problems before you run the script. And when you hit those errors you can’t just skip them and continue the script from that point where you stopped. You have to fix the script, delete the database and run the script again.
Sometimes that wizard generates unbelievingly wrong scripts. For example it generates some CREATE VIEW statements before even CREATE TABLE statements and you have to fix that manually, isn’t that just pain in the ass?
thanks for providing solution but bu doing this i didnt get data of that database just that script generated tables but not had data into it. from sql express 2005 to sql 2000
While this is” just” a wizard it showed me where to start. Thank you.
First. – thanks for this page. It was very helpful to me.
Several people posted requests to find out how to copy the data also. I did not find an automated way, but I was able copy the data by bringing up both Databases in SQL Server Manager and then opening up the source table, highlighting the rows and copying all of the rows into the second table. I only had 10 tables that I needed to do this on, but it didn’t take that long.
If someone else has a more elegant way of copying the data let me know.
Thanks
Make use of Export Data option
thanks. this page just saved me a bunch of time.
Can we script out the complete DB from the Query Analyzer? How?
Does anyone know to generate a script for an Index which should not be generated using the above article..It should be through front end.
can any body help me,
i want to create database for each user,
is their inbult functinality in SQL 2005
or i have to run script for each new DB
can u tell me whats the use of generating scripts?
i followed the whole procedure. in sql server 2005,the script got generated . but next what?
can u tell me the sue of it atleast?
You can use that script to create objects in another database
How to copy a database not o0nly structure but including data to another database.
Take a backup of the database
how to get the database backup
Hi,
Thnx a lot,
Great script, even for me, just copy the script to your new database script window, change the login details and you are away.
Thanks for saving me hours of work!
Hello,
I have already copied the database table contents from a SQL 2000 to 2005. However, it did not copy the stored procedures, index, default values.
Would running this script on the new database I copied onto the SQL 2005 copy those stored procedures, default values, etc… on the new database without erasing the contents in the tables?
What is the best way to make an EXACT duplicate of a MSSQL 2000 database onto another MSSQL 2005 server? I just want an exact copy including the tables, contents, stored procedures, index, default values, auto increment, and everything in the old database.
Thanks for your help.
One option is to take a backup from version 2000 and restore it in version 2005