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 read the tutorial but I have some problems. I have two servers and I need to copy a database from a server to another. I try to use this tutorial but just the structure was made and I need all data also.
How can I do that using an easily way? Just to comment, I’m not a “Windows Man”. My knowledment is over MySQL and PostgreSQL.
Thanks for any advice.
Best regards
@ Paulino
The Easy way to make a copy on the same server or different server, is taken backup of database on primary server. A backup file will be created, copy that backup file to another or different server where you want to have a new database and then restore that backup to get the same copy of the database.
Three steps process,
1.Take backup of database on primary server.
2. copy backup file to secondary server.
3. Restore backup to get the same database on the secondary server.
Hope this helps.
Imran.
Hi,
Many thanks for posting this. I was looking for a way to create an Archive db without any data and this worked a treat. i then wrote a stored procedure to archive the data from the live db.
Thanks again and regards
Hiiiii……this is excellent website…..it heled me a lot…
Sir now plz help me one more time.
I learn sql and pl/sql programming.But now i m doing 3 onth training on C#.Net.For .net mainly sql server is used in every comp. I fell more comfort on database.I dont like form designing .Suggest me plz how can i switch to database.And sir plz send me the interview question and ans .
hi,
I had used the above mention method to generate the script of entire database including existing data. But I am able to get only the script for structure of all tables. Anybody can help me in this issue to generate insert script from an existing database as well as existing data which I can store in new database.
If you want data too, it is better you take a backup of the table and restore it
Hi,
How to append one database to other database within the same server. Please do help me to resolve this.
Thanks in advance.
Deepa
Hai
What is the major difference between SQLServer and SQLClient
Hi,
when I was at Step 4 : Select database options, I do not have “Ansi Padding” that condition. Why is that so? And after I create the Script already, how do I open and view my DB?
regards,
josephine
:)
Good day!
Is there a way to make the Wizard remember the Options I chose? – things like Script Triggers and Indexes and such? It gets tiring to use it.
Or is there a way to do this programmatically so that I can simply save a .sql file and run that according to the database that I want to script?
Thank you in advance!
how we make table backup with data and all type of relationship…..
we made some changes in the login and user details after which
our usual query ‘ select * from emp ‘ is not working but ‘select * from zz.emp’ is working where zz is our login and username…
i want to avoid given zz before table name. what should be done
Does using the RESTORE function create tables, stored procs, user functions, triggers, etc…if they do not exist in the target database?
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.
hey pinal i wanna do my project work and i wanna know how to create database in sql server 2005
It is as simple as
CREATE DATABASE database_name
Pinal, a number of comments relating to automating this process have been posted. Is there a mechanism that can be used to automate the script generation.
I expect it will take something like a ‘sqlcmd’ or ‘sqlmaint’ command line string that will specify the ‘generate script’ parameters including the name of the output file for the script.
It may seem a strange request but I’m trying to automate a process (as much as possible) for moving ever-changing DEV DB’s to UAT.
I thought it would be easy to find this information but your site was the only one I found that explained it well enough that I ( a relative newbie) could copy the data I needed first time without a hitch. Thanks.
Update to my prior post, Redgate SQL Compare 7 does a brilliant job of automating the scripting, allowing a scheduled batch job to be set up that will either generate a full DB script or a ‘differences’ DB script and also has an option to automatically synch the databases, all without manual intervention.
I would still be interested to hear whether MS has a similar capability.
thank u very much :)
Hi, using this way you can create only the structure of the data base.
how to copy the whole database with data into another database using only scripts.
my client wants to run the sript so that it should create the structure with the data.
Thanks,
Regards,
Viji
You need to take a backup of the database and restore it in another database