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
Do you know how this process can be automated?
Glyn,
Did you ever get an answer to this question from PINAL Dave? I am developing a backup infrastructure for all of our SQL2005 dabatabases that exist on different servers but are all backed up on a single server. I need to figure out how to automate the creation of existing SQL2005 Scheduled Jobs into T-SQL script for disaster recovery purposes. Did Pinal Dave ever answer this question?
Dave Reynolds
SQL Application Developer
Not to be overly critical but blogging on a wizard that is, for the most part, self-explanatory is somewhat akin to writing help file for Windows Notepad.
Hi,
i’m using the “Microsoft SQL Server Database Publishing wizard”.
It’s free and needs less clicks than the wizard included in Management studio
Hi Penal,
I want to Create a database in SQL 2000, which will be the mirror image of source database and will updated automatically. Can you provide me the process…..
Thanks in advance
GK
Can SQL Server 2005 Database be restroed in SQL Server 2000?
I doubt that you can restored a 2k5 db to 2k and thats because there are so many new objects that were implemented in 2k5 and not supported in 2k. However, you may be able to script it and remove all 2k5 features before restoring.
I think Restoring SQL Server 2005 database to SQL Server 2000 database is possible by exporting the database.
The views are not exported as a views in 2000 but it get restored as tables.
So Accept views all objects can be restored from SQL 2005 to SQL 2000
And then what?
Once you have a script for your whole database, can you use it to build a copy of your database in another directory, such as for testing purposes? How? If you hit the execute button, will the script write over your existing database?
A problem with this method that I often encounter is how to order the scripts of dependent objects… if you use nested views the script will fail when not executed in the right order.
Even the option ‘Script all dependencies’ does not put the script in the right order. With a database with several thousand views nested into as many as 10 layers this becomes almost impossible to script manually.
Any suggestions?
sir,
what is the difference in variable starts with @ and @@.
Thanks
Sachin Kulshreshtha
@ for local variable
@@ for gloval variables
Hallo,
i would like to know if there is a way of using the wizard through command line arguments.
I am using the Ms DB professionals power tools to compare an empty database to the dev db to generate a Create Script of the schema and the database, the script fails due to dependency problems. I have found that the wizard takes these dependencies into account when scripting all the database objects.
Thanks
Philip
-“moet nooit laat ‘n vreemde persoon op jou grasperk dans nie”
sir,
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.
Pls, help me.
Thanks in advance.
Kumar Ashish
Take a backup of database and restore it in other database
Hi ,
Any one have procedure to generate table create script with passing table name to the proc .
Thanks in advance.
Alkesh K.
This script may help you
Pass the table name and get the script of the table
Hi Kumar ,
You can just restore the new database by sql enter prise manager .
hi,
Does this method log the transaction in sql server.
Hi,
Please guide me the procedure by which I can creeated one record in a table and then copy the same into the other tables of the same database by using command line.
Thanks in Advance
Dave,
Is it possible to have a backup of a some set of tables only using the backup script?
-Jay
No it is not possible until you create a new database with set of required tables and take backup of it
may i know the importance and use of cursor,trigger,stored procedure..and transaction…and the difference of datafetch….
how to fetch the middle record from the table
[and]
how to find the next 10 records
[i need the query have to print 10 records in first time..other 10 in next time and so on..]
There is no concpet of middle record in relational database
You need to define it
Give us more informations
Sir,
About the “T-SQL script to copy complete database schema “.
The one you generate above. Is is possible to use this in Databaset? I want to use sql in a form, when I click backup database button. It will backup.
Thanks,
Jeof