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
gowtham: servername here is the servername on which you want to perform a sql operation like scripting etc. If you are connected to the server already and still it asks you need to manually put the server name. To know the server name pls type the following command in the query analyser or mgt studio.
select @@servername
Amit: the fastest way of copying databases that I would suggest is to detach >> use the “FOR ATTACH”clause to create the second database.
Please check the following sample code from BOL. Plse refer BOL.
CREATE DATABASE database_name
[ ON
[ [ ,…n ] ]
[ , [ ,…n ] ]
]
[ LOG ON { [ ,…n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]
Please let me know if you face any issue with this.
I want to create a Database at run time through coding in C# in windows application
If you want to create a new empty database use this script
CREATE DATABASE db_name
Hi Everybody,
If you a backup of a database in 2000 Server. You can easily restore the database in 2005 Server using Backup and Restore procedure. Other wise you can’t do that.
To datach and attach a database, You have to remember where is the datafile located before detach the database. Other wise, you will have a problem finding datafile location. If you know the exact location of the datafile you can easily attach the database.
Need help, I want to update back up database server from production every week and it has to be automated
Seems you need to setup Replication
Read about it in SQL Server help file
Anyones help appreciated.
I’ve been using SQL Server for about 3 years. Not a DBA, but know it decent enough.
I have a database which I can not see in the Script Wizard (highlighted in this blog)
It is a database created prior to my arrival at my current employer.
I’m logging in as SA.
Is there a flag or setting which will provide me access to this database within the Script Wizard?
You can email me at gnoter at yahoo dot com.
Oh, and Pinal Dave’s articles have helped me well in other SQL areas. Thank you Pinal.
Thx.
Hi Pinal,
I have to migrate database and its associated data ,SQL IDs with their password from SQL Server 2000 to SQL Server 2005. I did generate the scripts and executed and could migrate the database objects. My question here is how do i move data from Sql 2000 to SQL 2005. Can I use Import/Export wizard to do that? and by generating scripts does the IDs and Passwords get migrated?
Thnks,
Baru.
Hi Penal,
I have to create Database structure based on an existing database structure. Could you please guide me… I have to make sure that None of the data from the source database is copied…
In this post when we enerate scripts … dows it generate scripts to insert data too??
Please let me know ASAP…
Thanks
Cognac
No. It only scripts the objects and not data of table
Hi Cognac,
You can using some 3rd party tools like Red-Gate SQL Data Comapre (you can try it for free) to do data sycn between your 2 databases, though SQL Server also provides this functionality. Try using the Import/Export ulitily.
I personally found that the Red-Gate tool was very good.
Cheers
Ana
Hi,
I have sql express installed (and developed an application on Visual Studio 2005) but the maximum database size is 4GB. What can I do when the db size reaches 4Gb? Create new database? How can I check everytime the db size?
Thanks
EXEC Sp_spaceused
will show you database size
Very good article for learners of sql server. i have been struggling to get information about how to generate full table create scripts and seeking information about that. This article solved my problem.
Thanks
Kuldeepak
I have a SS 2000 database that I want to move over to 2005, into a schema. What is the best way to do that?
Thanks a million.
Hi,
i am beginner in sql server2005…..
i need good guid books…..
i saw a create scripts to copy the database and schema…. and i follow it…. but i dont kown how is open the copy of all objects….. send me how is access copy of objects…..
Thanks
Ramesh.S
Hi
I was trying to take back up of all stored procedures,
but I was not getting the option “Files to generate”,
So that I can save each stored procedure in separate files
instead of single file.
Can you help me how to get that option?
If you want to store the script of each procedure to each file, refer this blog post
http: //beyondrelational.com/blogs/madhivanan/archive/2009/10/26/script-out-procedures-to-seperate-files.aspx
Hi,
if yoy want to copy objects (tables, store procedures, view, etc and data) between SQL 2000 servers, you shoud use DTS, and use Copy SQL Server Objects Task is very siimple to configure, and it works fine. and later you can schedule this dts
in the case you have SQL 2005, you could create a SSIS Project (integration services, the evolution of DTS), and use Transfer SQL Objects Task, also, i recommend try frst this test database, because is not very smart and if you dont configure properly it could makes a disaster or a unexpected result.
with the SQL 2005 SSIS i couldn´t transfer object between 2000 and 2005, it doesn´t drop properly the objects in the destination, if anyone know other workaround welcome!!
Do we have any tool or utility available in SQL2005 to compare two database schemas?.
Eg:
I want to compare the current production database with Staging(UAT) database. The comparision should result me the difference in dbobjects(Schema,Functions,Storedproc,Triggers) .
Thanks in Advance !!!
Hi Pinal,
I want to create a database schema from the existing database using C#.
Can you please guide me for this?
Thanks,
Suren…
HI ,
I want to know the best practice for a schema Transfer so that i can do in an envrironemnt with Numbers users
I am create some table in one machine. i want to import that all table data into another machine ? what is the procedure?
Make use of Export option from SSMS
Dittos for documenting this. I program, but, lately seem to be doing everything including moving databases around… Your post was excellent. Or, lets see I could run every wizard known to see what they accomplish… NOT!
Thanks sir for this big effort,
PLS could you tell me what can i do to change the data type of column.
Best regardes,
Manar
You need to use ALTER table
ALTER TABLE table_name
ALTER COLUMN column_name new_datatype
Note that you may need to clear the data before doing this