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
Hello ,
Thanks for the nice instructions.
Is there a way that this process can be automated, where I execute a script and then it would generate shema scripts.
This way, I do not have to backup the data, but just the Schema.
Thanks Farhad
Dear Maxwell,
I think you have to be same platform. If you are using SQL
2000, you can restore particular database on 2000 platform. Same will be 2005 server.
If you detach a database from the SQL server how can you attach it again? Can any one solve the problem?
Hi
This was exactly the information I was looking for. I have a shared MS SQL database at a hosting company and I wanna move my locally developed database there. I think these instructions will help me do the trick.
Any ideas on where to look if I want to find information on how to develop with MS SQL Express + Visual Studio Express on my local box and then use the hosting site as the production platform, so to speak.
Again, thanks a bunch!
Markus
what i really want is a full script of data from table
like
insert into script with data attached
i cant copt such data from a certain table..
i heard of something like “script table as” and all of the data will be shown as result…
It is available from version 2008 onwards
Thanks for posting this! While the wizard is self-explanatory, I didn’t know the wizard existed so I needed a push in the right direction.
sir ,
can i get resultset of store procedure in Trigger
Why do you want to do it from a trigger?
Anyway refer this
Generating INSERT statements for table content duplication is easy.
Write a VB or C# tool to get a dataset from the targetted table, and then output a INSERT line for each.
For my company I wrote a tool I call mssqldump (analogous to mysqldump) that does just this.
Here’s the useful bit (substitute your own database access object for DA):
SqlDataReader dtr = DA.ExecuteQuery(“SELECT * FROM [” + DBTable + “]”,false);
if (dtr != null)
{
Console.WriteLine(“– dumps content into ” + DBTable);
Console.WriteLine(“DELETE FROM [” + DBTable + “]”);
Console.WriteLine(“SET IDENTITY_INSERT [” + DBTable + “] ON;”);
while (dtr.HasRows && dtr.Read())
{
string strNames = “”;
string strValues = “”;
for (int i = 0; i 0) strNames += “,”;
if (strValues.Length > 0) strValues += “,”;
strNames += “[” + dtr.GetName(i).ToString() + “]”;
strValues += DA.MakeSafeSQLValue(dtr[i]);
} // end of looping on columns
Console.WriteLine(“INSERT INTO [” + DBTable + “] (” + strNames + “) VALUES (” + strValues + “);”);
Console.WriteLine(“GO”);
} // end of reading
dtr.Close();
dtr.Dispose();
Console.WriteLine(“SET IDENTITY_INSERT [” + DBTable + “] OFF;”);
}
Hello!
Thank you for this post and for all the comments you all posted here!
I’m trying to do the same as Online – getting a DB from 2000 to 2005, if possible as an automated process.
By using Copy Database I was able to get (apparently) all the information – tables, index, triggers, procedures and so on.
What I did was:
1. create an empty DB with the same name on the 2005 environment – NAME_DB
2. create the users on the 2005 server
3. give the users rights as owners on NAME_DB and then Copy Database to a new db – NAME_DB1.
Now i have in NAME_DB1 all the information (again, apparently).
Could you please advise me – is this a reliable procedure? Am I missing something?
Thank you in advance for your replies.
Irina
New to SQL 2005. In the generated DiskCopy script. how do you designate the name of the new database? Which line names the new database, if you want it to be different than the original? Sorry if this is a noobie question. Tahnks.
Also, Is there a way to Export just tables with no data?
In the wizard, dont select the checkbox “Script all objects in the selected database”
You will be able to check only the tables in the next steps
how can i use import/export comment in a nother programing languege , i dont want to use import/export wizard i want to use it in my application manully and import/export use in application
Hi:
How to Generating Scripts for SQL Server with Windows JOB?
Hi!
I want to copy all the system objects like SQL Jobs. Logins, SSIS packages etc to another insatnce, so how to generate scripts for them all at once?
thanks in advance!
Munish
Hi
I have migrate my database from sql server 2000 to 2005 using publish wizard and i have created it in local server and change some permissions now i want this 2005 database to move to live database server with different database name i have generated script same as above .Now i have problem that i can run query from management studion it runs perfectly but when i run it from asp page i cant get result ,i have checked connection it is established .To solve this problem i have changed some permissions but now i am getting access denied error… I am so disturbed please help me …its urgent ….
Thanks in advance ….
Regards
Dev
Hi,
There are some third party tools that you can use to script the table schema and as well as the data contained in those tables.
One that i Know and I have used is Embacadero(DBArtisan).
How would you automate this? What’s the command-line equivalent for scripting each object to a separate file? SCPTXFR can script each category of objects to separate files, but not each object.
In the wizard , one of the options about half way down is script for 2005 or 2000
it takes longer but it does the job
**** Very imp Note for scripting —
1. In the case of a production database, please generate the script as the non peak hours.
2. Databases which are very big, please be sure that you are scripting only those options which are required by you. Scripting tool will give you enormous number of options that you can set to true or false. Just TRUE the option that you want otherwise i have seen script generation taking 10-12 hours, which can be bad for the performance.
GKumar
Please use Log shipping in case you want the copy of the entired database as mirrored. In case you want only one table, go for replication.
Maxwell,
SQL Server 2005 Database cannot be restroed in SQL Server 2000.
Terry
Just by hitting the script button will not over rite your database. it will just generate a script (you can choose the option of clipborard, new window or save to a file). once you have the script ready and suppose you are on the query window with the script, you can just replace ( CTRL + H) the database name with the test database name.
roland
yeah roland, I agree with you one this. This process does have some hidden complications. What I would recommend you is to NOT SELECT the script view option initially. once you script other objects then later on you can script only the views. You have the options of true and false to minimise the scripting overhead.
hi,
before i start creating database its asking server name i dont know wat should i give…. can u plz help me….
Hi there
I wanted to knw if there is a way to copy database which is on another machine/server(MS SQL 2000 Server) to my new machine having MS SQL 2005 Server installed.
I tried it using Sql Server 2005 Management Studio to connect to the 2000server and using COPY DATABASE option but its not able to copy to local (machine) viz. SQL SERVER 2005 from SQL 2000 server.
Thanks in advance.
Rgds,
Amit