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
Dear Sir,
I installed Microsoft SQL Server 2005 in my pc . But i can’t create a database in my system so plz help in this matter….
Hi Pinal,
please send me the same details to automate this process.
one more thing that I’m not able to find ANSI Padding in my Scrept wizard. i am using sql server 2005
please assist
Thanks
Abdul Razzaq
hi, could i get the option to create script which will append data in database using sql server 2005?
where i want to take data from one db & insert it into another(not restore)
I need a query to Copy all the data from from one database to another database where the databases have same structure.
please sennd a query ..it is urgent…
Make use of import/export wizard
HOW CAN CREATE TABLE USING STORE PROCEDURE USING GET VALUE IN TABLE NAME
alter PROC sp_TP_CreateTable(@TABLENAME varchar)
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
create table [dbo].[@TABLENAME](ino int
)
sp_TP_CreateTable EMP_TABLE
TO CREATE PROC BUT NOT CREATE TABLE
hello
I am using sql server 2005
I have a table named users, columns are id,name,age,address.
first column id is auto increment.
I inserted all data successfully,
when i delete a row from table using visual studio 2005,it is deleted successfully,but when i insert new row it does not place on that index i deleted,next index generate and new row places on next index.
please tell me its solution.
Hi Pinal,
Could you please send me the same details to automate this process (SQL 2005)?
Thanks in advance!!
Hi,
Could you please send me the details as well, on how to automate this process?.
Thank you.
Adriana
Iam trying to come up with the best solution here and need some help. I have one server with about 200+ databases. How would be the best way to copy or script from one server to another? One is our production and the other is our stage. I tried to generate a script with the wizard but its all or one. I need only about 200 of the databases
I basically need the copy of databases from production to stage automated on a regular basis. We are on SQl 2005 with windows server 2003
Have you tried backup and restore method? You can also schedule it via job
Can u please tell me how i take script with table value in sql server 2008
Use Generate Script option from SSMS
Hi
It is possible to backup database to remote server tap drive, if yes so please can you me send me detail.
Thank in advance
Yes. Use UNC path
backup database your_db to disk=’\remote_systepathyour_db.bak’
Hello sir,
hello sir,
I have use sql server 2000 amd 2005 in my same system , but my problem is i cannot marge with 2000 and 2005, then i want retore 2005 db into 2000 it my big problem … give some solution…
i did do it attach ment and restore all failed how to possible to do
Hi Pinal,
Would like to know how to write a script, which can copy some of the fields in a table from 1 database to another table in another database?
Hope you understand what I am asking here. :-)
Thanks in advanced.
Yvonne
By copy, did you mean updating column values? You can join with other table and update columns
update t1
set t1.col=t2.col from table1 as t1
inner join table2 as t2 on t1.keycol=t2.keycol
Pinal Dave,
Could you please email me the details on automating this process in SQL server 2008? Also if you are familiar with any good resources regarding automating script generating, I would be interested in that too.
Thanks for your help.
You can make use of this post
Copy one table one database data to another table different database ????
You can use import/export wizard to do this
and how to get create database back from the sql server generated scripts????
Hi Pinal,
Could you please send me the same details on how to automate this process (SQL 2008)?
Thanks in advance!!
How do I create a backup of the database schema and then automate it to create a copy overnight?
Thank you. Very helpful.