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
Did you get an answer to your question here?
hello pinal,
i want to create script with data in sql server 2005 express as u shown. but i m not getting data.
Msg 15401, Level 16, State 1, Line 2
Windows NT user or group ‘ALWAR-BAGH-PC\Guest’ not found. Check the name again.
15401, Level 16, State 1, Line 2
Windows NT user or group ‘ARYANIWAS\Guest’ not found. Check the name again.
Msg 15023, Level 16, State 1, Line 2
User, group, or role ‘BUILTIN\Administrators’ already exists in the current database.
Msg 15023, Level 16, State 1, Line 2
User, group, or role ‘BUILTIN\Users’ already exists in the current database.
Msg 15401, Level 16, State 1, Line 2
Windows NT user or group ‘EBOX5\Dinesh’ not found. Check the name again.
Msg 15401, Level 16, State 1, Line 2
Windows NT user or group ‘EBOX5\Guest’ not found. Check the name again.
Msg 15401, Level 16, State 1, Line 2
Windows NT user or group ‘EBOX5\SQLServer2005MSFTEUser$EBOX5$SQLEXPRESS’ not found. Check the name again.
these errors are happens pls tell me the solution
i got one requirement Iike I want collect all dependencies like tables,functions, storedprocedures, views,triggers, constrains and so on by using PIVOT table can any one give the same SQL script.
Hi, Is it possible to backup original SP name as usp_updBuy to usp_updBuy _YYYYMMDD in same database with script?
you need to change that manually in the script which is generated.
how can db schema including Partition in SQL 2005
hii Pinal,
I want scheduled database backup in a drive.I am using SQL server 2005 . please send me solution ..
i have addscheduletask but after that what i have to to i dont know please send me the solution immediately.
Thanks in advance!
Vandana – You can use maintenance plan to schedule backup. Go to search.sqlauthority.com
How can script out particular schema dependent objects without selecting one by one db objects in Generate script wizard.?
hello pinal,
how are you?
I have a question … i have three database and having a single table in each have the same name like table name is “customer amount”. which contains date and amount. and i want that whenever i choose the date from calender the sum(amount) from all the three db come in a grid in different column.
how do i do so with database query.
thank you in advance!
Without schema and sample data, it would be difficult to provide query.
Pinal, awesome tutorial.
Could you send to me how to automate this process?
I need export data of 300 databases
i want to copy all tables,preocedures,packages,functions etc from a database user(Oracle10g) to a different location so that i can restore the database structure in my home m/c and work on the database
But the indexes were missing. How to get them too?
Hi Pinal,
It was really an awesome tutorial and more helpful.
I also wanted the scripts to be generated automatically. So could you please send me an copy of how to automate this process?
Thanks in advance,
Girish
how to restore sql server 2008 R2 64 bit database on sql server 2005 32 bit database ?
Is it possible?
How do you automate this process?
how can we get this script through t-sql[without uI]