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
Does it backup the data too ?
No. You need to use BACKUP DATABASE Command
BACKUP DATABASE your_db to disk=path’
How to restore the Generated Script.
Dear sir,
i have one database in sql server2000. that database size is 2GB. in this database contain 1000 table and 1800 order related data is there. Now i am deleted 1300 order related datas are deleted. after delete the record database size is not decreased. Why database base size is not decreased please give the reply as soon as possible sir.
by
kumar.T
Hi
I have one database in sql server2005 ,I know how to retrieve data from a particular table
But i want to know can i retrive data from a particular database? i.e. all tables infomation with single query.
Please let me know if any option to retrieve like that.
Hi Pinal,
I just changed a table definition and I losed all the dependencies of this table . How Can I get them back?
Thanks in advance.
Brett
Well, I know some people have this a useless post, but it helped me a great deal. I’m not a database programmer, but I needed to copy a database when our database programmer was out.
Looking at the Microsoft SQL Server Management Studio for the first time can be a bit intimidating, especially if you’re not familiar with all the new terms.
Thank you!
-Dave
hello.
please tell me how to copy tables from one database such as northwind database to my own created database??
Thanks for this.
Why did MS get rid of the DTS Import/Export option Copy Database objects? It was so flexible – you could select data and schema or just schema or just data, then select certain objects and not others etc.
Has anyone figured out how to do the above in SSIS or is there another way?
Thanks
Hello Sir,
I am from India …. how to create T-SQL script at sql prompt
Shanku
sir,
i want to know ….how identity columns used in procedures for insertion .
plz help me out.
thanks,
rajiv
hi ,
how to backup the database and restore it.
in sql prompt.
Dear sir
I wanted to know how we can take Scrapt Database(Copy Database)
Plz help provide.
Thank u so much
Hello Pinal Dave,
I have a client that has a sql server 200 db sitting on their local server. They want to make a copy of the db on a remote host. The remote host is a shared hosting account with http://www.godaddy.com run on sql server 2005. Godaddy will not let you restore from an outside source. I tried to copy over the data one table at a time but ran into an error for a larger table that contains html pages in the cells. The keep getting “Line 2 has 3 values. (13 expected)” What is your suggestion for getting a copy of my data over to the remote host? Please help!
Thanks,
Erica
Hi!
I’m a newbie and I’m not a SQL Worker.
I need to copy a database from a server to another server.
I already followed the procedures above and I can see the scripts.
What should I do next?
Please help me.
Thank you very much. Your help is highly appreciated.
Hope this helps someone as new to SQL as me:
Create a new database in SQL Server Management Studio.
In the Management Studio, select the database you want to copy and follow the instructions above. Now you have your copy script.
In Management Studio, select the new database and hit the exclamation button (run the script).
Your database tables will be copied minus their data records.
Forgive me if this is obvious to most but it took me a long time to figure out what to do with the script.
Thank you for the tutorial, it was very helpful.
how can i make a trigger which will take the back up of database automatically
You shouldn’t use a trigger
Create a procedure that takes a backup
Schedule that procedure to run periodically using a job
Hi,
I’ve installed SQL Server 2005 in my laptop…
while konnekting 2 the DB engine …it has been askin me the SERVER name…
What kud be the server name?
im a bit konfuzed ….
im neu 2 SQL Server…..plz help me out…..
thnx n regards,
Naga Raju A
i want to transfer sql db(2005) created in my local system to server (for hosting).
I am familier with dts option for 2000, but can you tell me How i can export data in 2005 ???
waiting for reply,
Gita Kansara
What is the procedure to create a table using a given schema in microsoft sql server 2005.
@ kuldeep.
Normally this is the script we use
CREATE TABLE TABLE_NAME ( COLA DATATYPE , COLB DATATYPE)
if a user execute this query who has ddladmin permissions, then a table will be created in the default schema of that user. Default schema for all users will be DBO unless you change it. so this new table will be actually “dbo.table_name”
Lets say there is a schema ” schema1 “which already exists in the database and that is not your default schema , lets says your default schema is dbo and you want to create a new table in shcema ” schema1″ then you can execute this query:
CREATE TABLE SCHEMA1.NEW_TABLE ( COLA DATATYPE , COLB DATATYPE)
This table will be created in schema1.
Schemas are nothing but containers. In 2000 we use to write fourpart name like this.
servername.databasename.ownername.objectname
In 2000 generally the owner name will be a user.
—————–
But in 2005, after schemas have been introduced, the new fourpart name will look like this,
servername.databasename.schemaname.objectname.
Long story short : when you want to create a table under any schema, mention schema name before table name in the create table statement.
CREATE TABLE SCHEMA1.NEW_TABLE ( COLA DATATYPE , COLB DATATYPE)
Hope this helps.