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
Hi Pinal,
I´m trying to restore the Database schema and objetct (wirhout data) from MS SQL 2005 script file, generated in the way you justdescribed.
I’m trying to restore to MS SQL 2008 Express, but I got many errors when I try to run the script in the MS SQL Server Management Studio.
I would like if there is some compatibility issues between the version 2005 and 2008, or if you have some tip about this process.
Thanks in advance!!
Hi Pinal,
I am trying to compare store procedues from two different databases but same server in SQL Server 2005.
I have two databases on samesever Test1 and Test2. There are few store procedures in Test1 and few in Test2. I have to compare these two databases and then make a .txt file of there store procedure. Is it possible? If possible then how?
Thanks,
pinal,
how can i transfer the views to another views wherein the views is in the table when i import the table and views to the other database pls. help….
Your question is not clear. Can you explain with examples?
Hi all,
I am looking for a way to script out all of my database objects. can anyone help me out so that i can have a single script to so this task. I dont want to use generate script utility separately to generate script individually.
Experts kindly help me out.
BR
Rahm
It looks a bit like a more awkward version of RoR coding to me. RoR is definately the best language to use with SQL Servers. :D
hello sir
i have one column in d_o_j column.i have 400 col in d_o_j
example
12-8-2010
12-9-2001
12-7-2007
05-12-2001
08-1-2010
26-12-2009
i want a that all the col have come date to date means
2001,2002,2003,2004,2005,2006
clustered index used to arrange the physical order a to z
means date col come 2000 to 2010
i know you are busy but if you are free plz solve my query
thanks sir
If you need a specific order, use order by clause
hi pinal,
here i have a query related SQL jobs. my requirement is that job should be executed in a prescribed time duration. hoe to create job and what are the steps please let me know .
Awaiting for your positive and soon response,
Thanks,
Madhu L. Chande
Read about Jobs in SQL Server help file. It has all relavant informations
sir,
now i entired into .net world… and i want basics of all sql
queries,store procedures,connection,restore and so on… so
sent Basics of all SQL SERVER 2005 queries
Search for “Learn SQL” in google/bing
Hi pinal,
we are planning to migrate from sql 2005 to 2008 so i prepared backups while searching get some interesting things in back ups i found your site and i wonder about script automations its really cool solutions , i have a query that whether scripts took in sql 2005 will run in 2008 irrespective of the version, kindly do mail how to do the automation process and idea was simply super,
Sir,
What I will do after creating script to take backup of Database???
You need to restore into the required server
I’m unable to create database in sql server 2005…….the error is “CREATE DATABASE permission denied in database ‘master’.”….So,please give solution my problem !!!
Post the code you used
Hi Pinal,
I would also be very grateful to receive the instructions about how to automate the generation of database-creation script from a SQL server.
Great article, thanks in advance.
Hello,
I would like to know how to run scripts into a new server. I used SQL 2005 to generate scripts for each database on the old server. No I want to apply those scripts to the new server to create the database structure with no data. So that I can then restore the databases into the new database on the new server.
Thanks
Connect to other server. Open a Query Analyser. Run the script there
Dear Pinal,
Is it possible to generate a script file only for Stored procedures (all the procedures) in sql server 2005/2008. In sql server 2000 we can use EM to generate script file, but 2005/2008 it is difficult for me to take a complete script.
Expecting your reply.
Thanks in advance Pinal.
Regards
Kumar. NM
In the Generate script option, select only the procedures
Hi Pinal,
May I also have the script to automate this process? Thank you!!!!
Hello DJinn,
Pinal already answered your question, please refer to his reply on this same page, look for January 8 2010.
Quoting Pinal’s reply,
First install the Database Publishing Wizard and then use te below command to create script of databse:
SQLPUBWIZ SCRIPT -d yourDBName -S yourServerName -U userName -P userPassword scriptFilewithPath
~ IM.
good evening sir.
i have one problem in sql server 2008 R2
I am not able to restore the database form 2008 to 2008 R2 with data
because the version is not incompitible
how to do this
Help
me
Thanks
Hi all,
I need to create a script to insert data from an Excel sheet(i don’t simply wont to import it i want to write a script) is there a way for that like using generate script we can directly create tables.
regards
aniruddha
You can use operowset function. Refer the same in SQL Server help file
I tried this but it copies all but not the data. How will I copy the data by sql script ?
Take a backup and restore it
I will take the backup but how can I restore the mdf file in other databases ? Is it possible ? If possible how ?
Take a look at Restore database in SQL Server help file. Use with move option to restore to a different database
very nice Pinal as usual!
It appears that so many people want the script for automating the process of creating a script to copy the DB. Can you please post the code? If not can you please send me a copy.
Thanks,
Inde