SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

August 21, 2007 by pinaldave

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 (http://www.SQLAuthority.com), All images are protected by copyright owner SQLAuthority.com

Posted in Database, Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Constraint and Keys, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQLAuthority Book Review, Software Development, T SQL, Technology | 220 Comments

220 Responses to “SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects”

on August 22, 2007 at 6:13 am Glyn Darkin

Do you know how this process can be automated?
on August 22, 2007 at 7:11 am Nick Koszykowski

Not to be overly critical but blogging on a wizard that is, for the most part, self-explanatory is somewhat akin to writing help file for Windows Notepad.
on August 22, 2007 at 7:34 am pinaldave

Nick,

You are correct. However, If you search my blog how to do script complete database question is asked very often.

My blog is focused on helping users who need help and I think, looking at stats of this particular post, I am very satisfied.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on August 22, 2007 at 5:01 pm Top Posts WordPress.com

[...] SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, F… Following quick tutorial demonstrates how to create T-SQL script to copy complete database schema and all of its […] [...]
on August 28, 2007 at 8:34 am Heiko Leuze

Hi,
i’m using the “Microsoft SQL Server Database Publishing wizard”.
It’s free and needs less clicks than the wizard included in Management studio
on August 28, 2007 at 3:07 pm GKumar

Hi Penal,

I want to Create a database in SQL 2000, which will be the mirror image of source database and will updated automatically. Can you provide me the process…..

Thanks in advance
GK
on August 31, 2007 at 11:35 pm Maxwell

Can SQL Server 2005 Database be restroed in SQL Server 2000?
on September 4, 2007 at 10:30 am Andre

I doubt that you can restored a 2k5 db to 2k and thats because there are so many new objects that were implemented in 2k5 and not supported in 2k. However, you may be able to script it and remove all 2k5 features before restoring.
on September 4, 2007 at 10:35 am pinaldave

Hi Maxwell,

Restoring SQL Server 2005 database to SQL Server 2000 database is not possible.

Kind Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on September 5, 2007 at 7:31 am Prakash Nandwana

I think Restoring SQL Server 2005 database to SQL Server 2000 database is possible by exporting the database.

The views are not exported as a views in 2000 but it get restored as tables.

So Accept views all objects can be restored from SQL 2005 to SQL 2000
on September 8, 2007 at 8:14 am Terry

And then what?

Once you have a script for your whole database, can you use it to build a copy of your database in another directory, such as for testing purposes? How? If you hit the execute button, will the script write over your existing database?
on September 8, 2007 at 7:43 pm pinaldave

Hi Terry,

It is glad that you asked question. I will be glad to answer your all question.

Yes. You can build this database in another directory for testing purpose. Just run the script.

If you hit the execute button it will or will not over write your existing database, depends on your settings when you can create database.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on September 12, 2007 at 2:56 am roland

A problem with this method that I often encounter is how to order the scripts of dependent objects… if you use nested views the script will fail when not executed in the right order.

Even the option ‘Script all dependencies’ does not put the script in the right order. With a database with several thousand views nested into as many as 10 layers this becomes almost impossible to script manually.

Any suggestions?
on September 25, 2007 at 7:41 pm sachin

sir,

what is the difference in variable starts with @ and @@.

Thanks
Sachin Kulshreshtha
on September 26, 2007 at 12:58 am Philip

Hallo,

i would like to know if there is a way of using the wizard through command line arguments.

I am using the Ms DB professionals power tools to compare an empty database to the dev db to generate a Create Script of the schema and the database, the script fails due to dependency problems. I have found that the wizard takes these dependencies into account when scripting all the database objects.

Thanks
Philip
-“moet nooit laat ‘n vreemde persoon op jou grasperk dans nie”
on September 26, 2007 at 2:09 am Kumar

sir,

I already generated script of one database. and make other database same as first one. Now I want to copy all the data from first database to new one.

Pls, help me.

Thanks in advance.
Kumar Ashish
on September 28, 2007 at 5:50 am Alkesh K.

Hi ,
Any one have procedure to generate table create script with passing table name to the proc .
Thanks in advance.
Alkesh K.
on September 28, 2007 at 5:53 am Alkesh K.

Hi Kumar ,
You can just restore the new database by sql enter prise manager .
on September 30, 2007 at 11:43 pm Vamshi

hi,

Does this method log the transaction in sql server.
on October 1, 2007 at 3:49 am Mubashar

Hi,

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.

Thanks in Advance
on October 2, 2007 at 5:57 pm pinaldave

Hello,

@Roland,
I understand what you are suggesting.
However, I do not use Views for my own databases so I never faced that error.

@Sachin,
Answer to your questions : @ is local variable and @@ global variable.

@Philip,
This method will not fail due to dependency problems.

@Kumar,
You need to write insert script manually or restore the database.

@Vamshi,
Script Generation will not log the transaction.
Script Execution will log the transaciton.

@Mubashar,
You need trigger. I do not like to use trigger so I suggest you write second insert script.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on October 9, 2007 at 9:18 pm Jay

Dave,

Is it possible to have a backup of a some set of tables only using the backup script?

-Jay
on October 10, 2007 at 12:45 am maha

may i know the importance and use of cursor,trigger,stored procedure..and transaction…and the difference of datafetch….
on October 10, 2007 at 12:56 am maha

how to fetch the middle record from the table
[and]
how to find the next 10 records
[i need the query have to print 10 records in first time..other 10 in next time and so on..]
on October 11, 2007 at 8:00 pm jeoffrey

Sir,

About the “T-SQL script to copy complete database schema “.
The one you generate above. Is is possible to use this in Databaset? I want to use sql in a form, when I click backup database button. It will backup.

Thanks,
Jeof
on October 17, 2007 at 11:30 pm Manoj

I want to create new database from a template database on same server with all objects and also data in tables. I want to run a store procedure to which I will pass two parameters and and store procedure will required to copy all objects with data in tabels to new database where new database is already created before runing store procedure.

Is there any way to copy all objects tables with data from one database to blank database in MS-SQL 2000?

Thanks,
Manoj
on October 23, 2007 at 12:45 am Gdc

Nice suggestion.
But I have a question:
In SqlServer2005 I can script the creation of a specific table (by right-clicking on the table and selecting Script table as…\CREATE To…).
With SqlServer2005 Management Studio Express I can customize the script with the Tools\options\script settings (I can tell SqlServer2005 Express to include indexes, triggers, … in the creation script), but with SqlServer 2005 Management Studio (no Express) this customization disappears and by default in the table creation script these objects are not included.
Can you tell me how can I script a single table including any additional object without using the widzard you described and without scripting one by one any additional object?

Thank you for any suggestion.
on October 25, 2007 at 12:36 am Ibrahim

HI…

I am new to database sql server 2005.I want to change the data type of a column in the table. Therefore i created a new column with the required data type and tried copying the contents of the main column to the new column.But the problem is, the table is around 4gb and therefore when i try to execute the above said procedure my transaction log gets filled up really fast.I have around 25gb of free space in the drive.The transaction exceeds the available space and i get an error.I tried executing the transaction in the simple recovery model and also full.but both ways the transaction log filled up really quickly and exceeded the available space.

It would be nice if i could know the ideal method to implement the above procedure..

Thanks…
on October 26, 2007 at 5:09 am Lokesh Bhatt

Hi Pinal,
I want to generate script of table with data. Is it possible with sql server 2005??
Thanks & regards
on October 26, 2007 at 5:10 am NT

Hi Pinal,

I would like to know that how can we compare two database in the sql server 2005?

Thanks,
NT
on October 29, 2007 at 5:25 am Neela

Hi,
Pls Help.
How can I create the script for individual objects in SQL server 2005.

In 2000 it is a easy task in Enterprise Manager.
on November 6, 2007 at 3:20 pm matin

I already generated script of one database. and make other database same as first one. Now I want to copy all the data from first database to new one.

thanks
on November 8, 2007 at 6:22 pm Geo

Hello,

Unfortunately the generate script wizard’s results are quite unpredictable. If you generate a script using this tool, restore to a fresh database and repeat the process for this second database you get two hugely different results. This tool is not suitable for keeping track of your schema changes using code versioning, and unfortunately I found no better replacement for it. Also not this tool has a bug in as it generates descriptive header comments for RULE objects even when asked not to.

It is mind dazzling how a database engine such as sql2005 has no working, predictable, configurable schema creation tool. I doubt sql2008 will make any inroads in this territory. I find it mind boggling that there are so many developers out there that have had no concern (probably) for database schema versioning.
on November 15, 2007 at 12:25 pm Amodia Ashish

thanks for providing solution but bu doing this i didnt get data of that database just that script generated tables but not had data into it. from sql express 2005 to sql 2000
on November 17, 2007 at 3:41 pm SQL SERVER - 2005 - Generate Script with Data from Database - Database Publishing Wizard « Journey to SQL Authority with Pinal Dave

[...] 16, 2007 by pinaldave I really enjoyed writing about SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Fu…. Since then the I have received question that how to copy data as well along with schema. The [...]
on November 20, 2007 at 8:38 pm Robert

While this is” just” a wizard it showed me where to start. Thank you.
on November 22, 2007 at 10:10 am stan

First. - thanks for this page. It was very helpful to me.

Several people posted requests to find out how to copy the data also. I did not find an automated way, but I was able copy the data by bringing up both Databases in SQL Server Manager and then opening up the source table, highlighting the rows and copying all of the rows into the second table. I only had 10 tables that I needed to do this on, but it didn’t take that long.

If someone else has a more elegant way of copying the data let me know.

Thanks
on November 25, 2007 at 10:18 am Hamish

thanks. this page just saved me a bunch of time.
on November 27, 2007 at 4:22 pm Manoj

Can we script out the complete DB from the Query Analyzer? How?
on December 3, 2007 at 11:14 am Jaimy

Does anyone know to generate a script for an Index which should not be generated using the above article..It should be through front end.
on December 3, 2007 at 1:08 pm suyog kale

can any body help me,
i want to create database for each user,
is their inbult functinality in SQL 2005
or i have to run script for each new DB
on December 6, 2007 at 12:23 pm shishma

can u tell me whats the use of generating scripts?

i followed the whole procedure. in sql server 2005,the script got generated . but next what?

can u tell me the sue of it atleast?
on December 13, 2007 at 11:59 am Sathish

How to copy a database not o0nly structure but including data to another database.
on December 13, 2007 at 2:57 pm rpr

Hi,

Thnx a lot,
on December 17, 2007 at 9:30 am Lethal007

Great script, even for me, just copy the script to your new database script window, change the login details and you are away.

Thanks for saving me hours of work!
on December 21, 2007 at 6:36 am Online

Hello,

I have already copied the database table contents from a SQL 2000 to 2005. However, it did not copy the stored procedures, index, default values.

Would running this script on the new database I copied onto the SQL 2005 copy those stored procedures, default values, etc… on the new database without erasing the contents in the tables?

What is the best way to make an EXACT duplicate of a MSSQL 2000 database onto another MSSQL 2005 server? I just want an exact copy including the tables, contents, stored procedures, index, default values, auto increment, and everything in the old database.

Thanks for your help.
on December 26, 2007 at 11:36 pm Farhad

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
on December 29, 2007 at 3:07 pm Khondaker haaque

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?
on December 29, 2007 at 10:45 pm mossi

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
on January 2, 2008 at 2:59 pm Ronald

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…
on January 3, 2008 at 12:43 am Shawn

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.
on January 3, 2008 at 7:09 pm Yogesh Nikam

sir ,
can i get resultset of store procedure in Trigger
on January 4, 2008 at 1:11 am kforslun

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;”);
}
on January 7, 2008 at 6:05 pm Irina

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
on January 9, 2008 at 11:28 am Charles

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.
on January 9, 2008 at 6:20 pm Charles

Also, Is there a way to Export just tables with no data?
on January 14, 2008 at 2:14 pm moj

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
on January 17, 2008 at 1:42 pm jacky

Hi:
How to Generating Scripts for SQL Server with Windows JOB?
on January 24, 2008 at 3:05 pm Munish Bansal

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
on February 6, 2008 at 11:13 am Dev

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
on February 7, 2008 at 6:07 am Emeka

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).
on February 7, 2008 at 11:23 pm Chris

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.
on February 8, 2008 at 10:37 pm ravimotha

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
on February 9, 2008 at 4:50 am amit pandey

**** 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.
on February 9, 2008 at 6:47 pm gowtham

hi,

before i start creating database its asking server name i dont know wat should i give…. can u plz help me….
on February 11, 2008 at 5:12 pm Amit

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
on February 12, 2008 at 10:39 am amit pandey

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.
on February 13, 2008 at 3:14 pm aashish patidar

I want to create a Database at run time through coding in C# in windows application
on February 21, 2008 at 4:32 am Khondaker haque

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.
on February 22, 2008 at 8:39 pm najam

Need help, I want to update back up database server from production every week and it has to be automated
on February 25, 2008 at 9:48 pm Bryan Carter

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.
on February 26, 2008 at 9:26 pm baru

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.
on February 26, 2008 at 11:52 pm Cognac

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
on February 27, 2008 at 8:16 am Ana

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
on March 5, 2008 at 7:37 pm Claire

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
on March 6, 2008 at 10:22 am Kuldeepak Sharma(KD)

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
on March 7, 2008 at 9:01 am jaxx

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.
on March 7, 2008 at 11:43 am ramesh

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
on March 7, 2008 at 12:48 pm amy

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?
on March 7, 2008 at 6:30 pm Tatiana

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!!
on March 7, 2008 at 11:43 pm Neha

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 !!!
on March 10, 2008 at 3:25 pm Suren

Hi Pinal,

I want to create a database schema from the existing database using C#.
Can you please guide me for this?

Thanks,
Suren…
on March 12, 2008 at 12:17 pm srini

HI ,

I want to know the best practice for a schema Transfer so that i can do in an envrironemnt with Numbers users
on March 12, 2008 at 3:04 pm Mohammad siddik

I am create some table in one machine. i want to import that all table data into another machine ? what is the procedure?
on March 13, 2008 at 4:34 pm Stevo

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!
on March 15, 2008 at 10:43 am Manar

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
on March 15, 2008 at 1:58 pm Abeer

Hi all

Thanks dear it facilities my work a lot
But can I ask about how I can execute this procedure from the vb.net
Thanks for you in advance
on March 17, 2008 at 9:43 pm muhammad

first time coming to this blog..this page is very good so i may visit more often now. thanks
on March 18, 2008 at 3:29 am RHodgins

This was VERY useful. I am no SQL guru, although I do know my way around RDBMS…

My company needed to put together a validation process for the changeover from Mode 80 to Mode 90. We elected to force compile of all the scripts and deal with the fallout.

The trouble was that no one knew how to do this…

Although a little editing was required of the final script, this was a Very necessary first step.

Thank you.
on March 21, 2008 at 12:41 am Sathish

Thank you so much for the info. of taking the database schema.

Actually I wanted to take a entire database backup, but which came around more than 30 gigs.

So I have taken the schema backup by using the info. given here.
on March 25, 2008 at 7:22 pm mbourgon

Wow. I wonder if Pinal knows that he’s still getting comments and questions so many months later.

First: Pinal, thanks for this - I figured we’d been given a crappy scripting app courtesy of the SSMS team. Nope, just not where I expected it. Many thanks.

And to everyone looking for a different way to script everything, I found something on sqlteam dot com called ScriptIO. The downside is that it’s 2005 only, but it’ll script out whatever you need.

A couple answers to earlier questions:
81: Redgate Data Compare & Redgate SQL Compare. If you just want to compare data on the cheap, do a BCP out with -c (character version) and use kdiff3 (yes, it exists for Windows) to compare files. But Red-gate’s tools are cheap and work pretty durn well.

66: one easy way is to do a backup/restore - when you restore to 2005 it’ll convert it. Another way is to detach, copy, and attach the database. But it will be unavailable while it’s detached - so restoring the backup should be easier.

53: There’s a couple different ways to do it. (But, as always - if it works for you, use it!) Another way is to do a BCP out/in. If they’re exactly the same table definition you can use -n (native), but otherwise use -c, and make sure of your field delimiter (and check the flag for Identity inserts). If doing character-based BCP imports, I frequently have to use | instead of [tab]. I also found a script that’ll create the insert statements from within SQL: http://blogs.consultantsguild.com/index.php/2005/02/07/dynamic_sql_insert_generator_unleashed_1?blog=6
Though, honestly, most of the time I’ll just use BCP/Bulk Insert.
on March 27, 2008 at 11:47 am Shambu

Hi
I found this website is very useful
Can u plz give me some tutorial of sql server 2005 which contains concepts like triggers, stored procedure, cursors etc
on April 2, 2008 at 3:10 pm Yaman Gupta

Respected Sir ,

I have a problem in sql server 2005

At front hand a user can login .
A username and password is saved in HASHED FORMAT

how can i get the a userpassword in orignal form
Pls sir give me a solution i am very much thanks ful to you .
on April 3, 2008 at 5:49 am Rahul

hey pinal i generate d script by d procedure told by u….but now whn m insert dat script in my database n hit the execute button….it start processing but after some time it shows me the error dat request time out…..my script dat generated is of 21246 lines…..
plsss help me out…
on April 3, 2008 at 10:28 am ronald

i have executed and disabled sa log in in sql server 2005 version how can i fix it tnx
on April 4, 2008 at 6:58 am Jonathan

YOU RULE!!! Don’t worry if some people did not appreciate your column–you are right that even though it’s a Wizard, I didn’t know how to use it!

Thank you!
on April 5, 2008 at 8:03 pm shashi

sir,
how can i create a database in sql 2005 and create a create a table plz tell me
on April 6, 2008 at 1:25 am Vinyl

Please how to check if a distant sql server is running (a ping?), what is the data source instance (exemple SQLEXPRESS), and if a database name exists? of course having the ip port user and pass
on April 6, 2008 at 9:43 pm Denise

Hello Pinal Dave,
Thank you for your web SQLAuthority.com and postings. That’s helped me in great deal…

after following your instruciton of “SQLSer 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects”, the SP, table names, Functions, Triggers are created, but i still have a trouble to load all rows into the tables. I tried the SSIS, import/export, copy database that none of are working. Can you make a suggestion for me to get this work done?

Thank You!
Denise
on April 10, 2008 at 4:24 pm lotfi

hi there,
what i really want is to copy the entire database to another Sql Server, this server in question is accessible via internet

if you can help, thanks
on April 12, 2008 at 7:05 am Raja

I had 3 server on 3 different places. 3 server got same database with own datas.
This 3 datas i want to put on 4th server which located on my place.
How can i do this..??
on April 14, 2008 at 10:49 am chris

Hey wait a minute… it doesn’t generate the ‘insert’ statements for the date.
Any equivalent for that?
Pls email me if you have a solution
on April 15, 2008 at 2:50 am Susan Henesy

Hello,

I’d love to know the best way to copy a database/table from SQL Server 2005 to SQL Server 2000.

I have SQL Server 2005, but I can’t seem to find a good way to copy all my tables’ properties over to SQL Server 2000 — identities, primary and foreign keys and such.

I tried creating a few “CREATE TABLE” scripts in the Query Editor Window, and then tried executing the same script in the SQL Server 2000 server, but all I get are syntax errors.

Would love to know if there’s an easy answer out there!
Thank you,
Susan
on April 15, 2008 at 3:05 pm MLC

Hi Neha we are not having any inbuilt schema comparision tools we should depend on third party tools
visit
http://www.sql-server-tool.com.
or search forredgate comparision tool
on April 16, 2008 at 5:14 pm SP

HI Pinal,

I just want to know how to get the table’s (one or more table) script using T-SQL Command.

Pls let me knw
on April 17, 2008 at 11:19 pm Sachin P

Hi Pinal,

i just want to know what is database schema?

Thank You
Sachin
on April 21, 2008 at 11:42 am bhagyashree

hi ,
i want to know database name through script in sql server 2000/2005. is it possible? if yes,plz let me know.

thanks in advance
on April 22, 2008 at 10:06 am sqlman

Does it backup the data too ?
on April 22, 2008 at 1:02 pm Gyan

How to restore the Generated Script.
on April 22, 2008 at 2:38 pm KUMAR.T

Dear sir,

i have one database in sql server2000. that database size is 2GB. in this database contain 1000 table and 1800 order related data is there. Now i am deleted 1300 order related datas are deleted. after delete the record database size is not decreased. Why database base size is not decreased please give the reply as soon as possible sir.

by
kumar.T
on April 22, 2008 at 3:47 pm Roja

Hi

I have one database in sql server2005 ,I know how to retrieve data from a particular table

But i want to know can i retrive data from a particular database? i.e. all tables infomation with single query.

Please let me know if any option to retrieve like that.
on April 22, 2008 at 4:38 pm Brett

Hi Pinal,
I just changed a table definition and I losed all the dependencies of this table . How Can I get them back?
Thanks in advance.
Brett
on April 24, 2008 at 12:48 am David

Well, I know some people have this a useless post, but it helped me a great deal. I’m not a database programmer, but I needed to copy a database when our database programmer was out.

Looking at the Microsoft SQL Server Management Studio for the first time can be a bit intimidating, especially if you’re not familiar with all the new terms.

Thank you!
-Dave
on April 24, 2008 at 4:41 am izac

hello.
please tell me how to copy tables from one database such as northwind database to my own created database??
on April 25, 2008 at 2:57 pm Slime

Thanks for this.

Why did MS get rid of the DTS Import/Export option Copy Database objects? It was so flexible - you could select data and schema or just schema or just data, then select certain objects and not others etc.

Has anyone figured out how to do the above in SSIS or is there another way?

Thanks
on April 26, 2008 at 1:02 pm SHANKU

Hello Sir,

I am from India …. how to create T-SQL script at sql prompt

Shanku
on April 28, 2008 at 5:01 pm Rajiv Bansal

sir,

i want to know ….how identity columns used in procedures for insertion .

plz help me out.
thanks,
rajiv
on April 29, 2008 at 12:31 pm gautam

hi ,
how to backup the database and restore it.
in sql prompt.
on May 1, 2008 at 11:27 am Dinesh Pal

Dear sir

I wanted to know how we can take Scrapt Database(Copy Database)

Plz help provide.

Thank u so much
on May 6, 2008 at 7:34 pm Erica

Hello Pinal Dave,

I have a client that has a sql server 200 db sitting on their local server. They want to make a copy of the db on a remote host. The remote host is a shared hosting account with http://www.godaddy.com run on sql server 2005. Godaddy will not let you restore from an outside source. I tried to copy over the data one table at a time but ran into an error for a larger table that contains html pages in the cells. The keep getting “Line 2 has 3 values. (13 expected)” What is your suggestion for getting a copy of my data over to the remote host? Please help!

Thanks,

Erica
on May 14, 2008 at 5:55 am Eirya Chrin

Hi!
I’m a newbie and I’m not a SQL Worker.
I need to copy a database from a server to another server.

I already followed the procedures above and I can see the scripts.

What should I do next?

Please help me.

Thank you very much. Your help is highly appreciated.
on May 17, 2008 at 3:07 am Rick

Hope this helps someone as new to SQL as me:

Create a new database in SQL Server Management Studio.

In the Management Studio, select the database you want to copy and follow the instructions above. Now you have your copy script.

In Management Studio, select the new database and hit the exclamation button (run the script).

Your database tables will be copied minus their data records.

Forgive me if this is obvious to most but it took me a long time to figure out what to do with the script.

Thank you for the tutorial, it was very helpful.
on May 17, 2008 at 10:11 am Lokesh

how can i make a trigger which will take the back up of database automatically
on May 17, 2008 at 12:57 pm nag

Hi,

I’ve installed SQL Server 2005 in my laptop…
while konnekting 2 the DB engine …it has been askin me the SERVER name…
What kud be the server name?
im a bit konfuzed ….
im neu 2 SQL Server…..plz help me out…..

thnx n regards,
Naga Raju A
on May 26, 2008 at 8:58 am Gita Kansara

i want to transfer sql db(2005) created in my local system to server (for hosting).

I am familier with dts option for 2000, but can you tell me How i can export data in 2005 ???

waiting for reply,

Gita Kansara
on June 2, 2008 at 10:30 am Kuldeep

What is the procedure to create a table using a given schema in microsoft sql server 2005.
on June 3, 2008 at 12:36 am Imran

@ kuldeep.

Normally this is the script we use

CREATE TABLE TABLE_NAME ( COLA DATATYPE , COLB DATATYPE)

if a user execute this query who has ddladmin permissions, then a table will be created in the default schema of that user. Default schema for all users will be DBO unless you change it. so this new table will be actually “dbo.table_name”

Lets say there is a schema ” schema1 “which already exists in the database and that is not your default schema , lets says your default schema is dbo and you want to create a new table in shcema ” schema1″ then you can execute this query:

CREATE TABLE SCHEMA1.NEW_TABLE ( COLA DATATYPE , COLB DATATYPE)

This table will be created in schema1.

Schemas are nothing but containers. In 2000 we use to write fourpart name like this.

servername.databasename.ownername.objectname

In 2000 generally the owner name will be a user.

—————–

But in 2005, after schemas have been introduced, the new fourpart name will look like this,

servername.databasename.schemaname.objectname.

Long story short : when you want to create a table under any schema, mention schema name before table name in the create table statement.

CREATE TABLE SCHEMA1.NEW_TABLE ( COLA DATATYPE , COLB DATATYPE)

Hope this helps.
on June 5, 2008 at 7:35 pm Steven

I have one SQL 2005 Server.
I want to make an exact copy of my database + content on the same server with the only difference being the database name. How can I achieve this.
I have a copy of the database but without the content. How do I get all the content of the original database into the new one?? backup/restore does not work. SQL says the target database is not the same database as the database that was backed up…

Can you help me out on this?
on June 6, 2008 at 7:06 pm Ashish

I think with this we need to attach drop and create database also I am having some problem in that

1.IF EXISTS (SELECT name FROM sys.databases WHERE name = N’Items’)

some where sys.databases works but some places we need to change it to sysdatabases.
how can we avoid that.

2.CREATE DATABASE [SVIDB] ON PRIMARY
( NAME = N’Items’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\SVIDB.mdf’ , SIZE = 12288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’ITEMS_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\SVIDB_log.ldf’ , SIZE = 57664KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’SVIDB’, @new_cmptlevel=90
GO

problem with path and user authentications.
like some systems stores at MSSQL.2\MSSQL\DATA\
some stores at MSSQL.1\MSSQL\DATA\

and some systems don’t have rights to apend folder.
on June 8, 2008 at 2:57 pm Anuj

How to create a duplicate database from another database?
Example we have a db with 4 tables named (’A', ‘B’, ‘C’, ‘D’) with some data. wa want to create a new duplicate db from first one with tables(’A', ‘D’) with there data. Need immediate reply on mail. Thanks
on June 10, 2008 at 6:17 pm tanna

hello sir,
how can i create a database in sql 2005?
on June 11, 2008 at 8:30 am harish

Hi Penal

Thanks for such an informative website, am a dummy in SQL 2005.

Can you please tell me how to copy the data extracted through sql query into another sql table.

Thanks and regards
Harish B K
on June 12, 2008 at 11:06 am Noman Muhammad

Hi PD

will u plz help me on how to add the sql script that u generate above to another Sql Server 2005 so that the same database is created on that Sql Server 2005

plz help me
on June 13, 2008 at 3:27 am Nick

I have two different servers, each with sql server 2005. I wanted to copy a database from one to the other. After struggling with generating scripts and such, I just created a database on on the destination server with the same name and security settings. Then I copied the DB_NAME.mdf and DB_NAME.ldf from the source server to the destination server replacing the files on the destination. (I turned both servers services off before copying the files). When I restarted the destination server, everything came up fine. With this method, the schema, data, views, and stored procedures transfered perfectly.

Do you foresee a problem with this method?

This blog was helpful in generating the scripts, thank you.

Nick
on June 13, 2008 at 12:07 pm Satya

Hi,

I m using asp.net with sql server 2005. Most of the time the server on which my database is kept faces some problem. I have taken one other database server also. What I want is that whatever record (tables,views,procedures and functions) I have, should automatically be transfered in the new database along with data. What should I do for it? Please suggest.
on June 20, 2008 at 11:45 pm cubemonkey4

Pinal, I was just trying to script out a full database in Visual Studio 2008 but it wouldn’t work because of an XML assembly object. The workflow was through the Database Schema Compare method. VS 2008 let me choose to not create the assembly object but then choked on the assembly anyway.

I’m going to try the method above see if this will work but I suspect I’ll still have a problem with the assembly.

I’ll let you know. Maybe my only choice is to detach the assembly, script out the database, then go through the steps to attach the assembly to the new database.

Your blog is very helpful, by the way. Thanks.
on June 22, 2008 at 4:03 pm ami

hello,
i want to make a user an owner of my database,am using sql server 2005,please can you help me to create a
1.login
2.user
3.make the user the owner of my database

thx
on June 23, 2008 at 4:58 pm Ramesh

Hello Pinal,

Presently I am working on dotnet. We are using sql server 2005. So, my requirement is when ever I click the button I have to delete the particular table’s data from database. But, before deleting I have to take backup and insert into another database.

Please give me advice in 2 to 3 ways. We are using sql select statements for transferring data. Is there any other way to transfer?

regards
Ramesh
on June 23, 2008 at 10:00 pm Hemant

Hello –

This was a great help. But is there a way to generate seperate scripts for the objects like we can do it SQL 2000 instead of getting one script in one go.

Thanks
Best Regards
on June 24, 2008 at 10:43 am srinu

Hi All,
I am following the above steps to take the stored procedures scripts to individual sql or text files. but to my surprize in the 5th step: Select Output Options, I couldn’t find the option to take the individual stored procedure scripts to defferent sql files. Is it my local sql server software problem or need I do any setting for this option?

Awaiting for your earlier reponse.

Thanks & regards,
Srinu
on June 30, 2008 at 10:37 pm swapnil khairnar

Hi sir,

I want how to create runtime sqlservser database in asp.net with c#.
Awaiting for your earlier reponse.

Thanks & regards,
Swapnil
on July 2, 2008 at 2:30 am Imran Taher Mohammed

I want to add something saying, there is an option called

Script Behavior: you can select two options for this,

a) Generate CREATE Statement only
b) Generate DROP Statement only.

Make sure you select option a) if you are trying to script all objects of database so that you can create it later on.

Before running the script in the PROD, Make sure you check if it is DROP or if It is CREATE.

Second thing I am concerned about is, Whether to use : Script USE DATABSE:

SCRIPT USE DATABASE:
a) TRUE
b) FALSE
By default it would be false. I also suggest to keep it false, because, when you generate the script and you execute (RUN) the script in prod, and you haven’t checked that it was a DROP Statement script. Trust me you are done…. ( Your Job is finished…) It will DROP all your database objects.

if you dont mention Database name, still you have one more chance to check…

hope this helps, please correct me if I am wrong.
on July 2, 2008 at 8:38 pm sunil

Hi,

I have to give ‘create Proc’ permission to developers team but restrict them from creating or altering table designs. I tried giving Connect, Create Procedure’,Delete, Insert, Select and Update. But it doesn’t work for them. It only works if I also give ‘Alter any schema’ but on giving this, they get permission to change table designs. How can I restrict table design changes for deleopers while giving them create/alter proc permission. This is all for sql server 2005. Please help.

Thanks,
Sunil
on July 3, 2008 at 11:45 am Imran Mohammed

Hello Sunil,

This might be a temporary solution,

Open SQL Server - expand database- expand security - expand roles - database roles - right click ( new database role)

1. Give the name of the role,
2. select the owner of the role ( dbo or any other user)
3. on the left hand side of the box, click securables, and then click add
a) after you click add, click specific objects - click ok
b) click objects types, check stored procedure- click ok
c) Click browse, all stored procedures will be displayed, you select which stored procedures you want to select, and then click ok.
d) Then you should be able to see which types of permissions you can give to those stored procedures. Remember, you have to give permissions to every single stored procedure, dont think if you give it to one, it will apply for all stored procedure, you have to select one stored proc at one time and then give permisisons ( alter/create/control) and after you complete all the permissions for all the stored procedures, then click ok.

4. Add developers userid to this database role, to do this,

Open SQL Server - database - security - roles- database roles- select newly created role - right click properties ( double click name) and then click on add and then click browse, select the userid of developers and click ok, and click ok again, Thats it.

Hope this helps.
on July 3, 2008 at 8:48 pm sunil

Thanks for quick reply, Imran.

I tried this, but this way developers won’t be able to create new proc. So I give ‘Create Procedure’ permission to the role. On giving this, user is still not able to create proc as he is getting error
Msg 2760, Level 16, State 1, Procedure try, Line 2
The specified schema name “dbo” either does not exist or you do not have permission to use it.

I think, I might need to give ‘Alter Schema’ also because its addition of object to the schema. And if I give this, then user can change table design also.

Regards,
Sunil
on July 4, 2008 at 5:58 pm sahil

Thanks
on July 9, 2008 at 4:26 pm Sujit

what is main differeence between sql server 2000 and 2005?
on July 10, 2008 at 9:59 am Taposh

Hey Pinal,

I would like to change the “domain (in pure db terms)/Schema name” in a db.
Example I have used
XYZ.Tablenames instead of
DBO.Tablenames.

Every time I am restoring a DB i have to manually change it, which with SQL Server 2005 is a sequential process, if any one step fails it does not work.

Is there a way to automate this.

Please let me know.

Regards,

Taposh
on July 15, 2008 at 2:47 pm seshathri

I want to know the steps to create a database .
on July 15, 2008 at 2:48 pm seshathri

steps to create a database
on July 16, 2008 at 3:06 am Imran Mohammed

@ Seshathri

1. Books Online - in index type create database and scroll down a little bit and there are plenty of examples.

2. Command to create a database with default setting,

CREATE DATABASE DATABASE_NAME

3. In Object Explorer / SSMS, expand sql server, you will see database, right click new database, give the name of the database and check if you want to other options, this is pretty easy, you should be able to do it, even if you have no SQL Server Knowledge,

Things which are important/which you should change while creating a database,

1. Database name
2. Logical Name
3. Physical address, if you want you can keep the same physical address.
4.Initial size ( depends on how much data will be needed)
5. Collation: default
6. Recovery Model : FULL ( defualt)
7. keep all default settings. click ok and you are done.

Hope this helps.
Thanks,
Imran.
on July 16, 2008 at 3:20 am sqlNeeds

If you don’t know how to create a DB to begin with, you should not try to do so.
on July 20, 2008 at 11:06 am balaji

HI pinal
how to genarate scripts [create] for all my data tables alone from a database…
with out any object for tables…….
Is that possiblity available in sql server 2005 ?
Else only by usual way of copying tables using query is alone possible….
With regards
Balaji.G
on July 26, 2008 at 10:40 am piyush

how to copy tables from one database in to another in sql server in .net application
on July 28, 2008 at 5:35 pm faridmasood

Is there any way to generate a script containing all tables data.
on July 29, 2008 at 7:52 am Imran Mohammed

@faridmasood

What I learnt the meaning of script is code. When you say I want to script table, it means you want the code of the table which was used to create that table.

First create the script and then run the script to create a new table and then you can transfer the data with many ways,

insert into new_table_name select * from old_table_name

This is the easiest one

create a dts package select object transfer task, select source, select destination, select the table you want to transfer, the table is created at destination with all keys, indexes and all constraints, and also data….

Hope this helps.
Thanks.
on July 29, 2008 at 4:50 pm prakash

Hi i’m doing C#, i want to

Journey to SQL Authority with Pinal Dave

Notes of a SQL Server MVP and Database Administrator

SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

220 Responses to “SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects”