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

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

About these ads

665 thoughts on “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

    • Glyn,

      Did you ever get an answer to this question from PINAL Dave? I am developing a backup infrastructure for all of our SQL2005 dabatabases that exist on different servers but are all backed up on a single server. I need to figure out how to automate the creation of existing SQL2005 Scheduled Jobs into T-SQL script for disaster recovery purposes. Did Pinal Dave ever answer this question?

      Dave Reynolds
      SQL Application Developer

      Like

          • Hi Pinal,

            Could you send me the same details to automate the process aswell please?

            And how to run the script and copy the database to a different server?

            Thx in advance!

            Shazu

            Like

        • Hi Pinal,

          I would also be very grateful to receive the instructions about how to automate the generation of database-creation script from a SQL server.

          Thomas Qvist
          CTO

          Like

          • Hello Thomas,

            First install the Database Publishing Wizard and then use te below command to create script of databse:

            SQLPUBWIZ SCRIPT -d yourDBName -S yourServerName -U userName -P userPassword scriptFilewithPath

            Regards,
            Pinal Dave

            Like

          • hey send me email regarding this query

            I am developing a backup infrastructure for all of our SQL2005 dabatabases that exist on different servers but are all backed up on a single server. I need to figure out how to automate the creation of existing SQL2005 Scheduled Jobs into T-SQL script for disaster recovery purposes

            Like

        • Hey send me mail regarding below issue

          I am developing a backup infrastructure for all of our SQL2005 dabatabases that exist on different servers but are all backed up on a single server. I need to figure out how to automate the creation of existing SQL2005 Scheduled Jobs into T-SQL script for disaster recovery purposes

          Like

          • Hi Pinal,

            Is it possible to have this script as well ?
            We want to automate a complete copy ( we are trying backup/restore database) of our databases from Production server to Quality server.

            Thanks
            regards

            Like

        • Pinal Dave,

          Would you please also send me the details about how to auomatic the process to create script to copy the db?

          I need to copy a db I was working on to another sql server, your help will be greatly appreciated.

          Anna H.

          Like

        • Hi Pinal –

          I would also be extremely interested in the automation for this scripting process. We are in need of an automated process for one of our databases for DR purposes.

          Thank you in advance for sharing.

          Regards-

          Aimee

          Like

        • Hi Dave,

          First thanks for a great blog on SQL.
          i am too desperately searching for a script to copy SQL jobs from one server to another.
          Do you possess such a treasure?

          Lior

          Like

        • Can I please have details of how to copy database including data from one server to another.

          This is to automatically update our test database from our live database on a regular basis.

          Like

          • execute a tsql query to have a full backup of the database.
            after execute a tsql to delete database in the test server
            then execute tsql to restore the backup in the test server.

            All this in a single transaction if possible!

            Like

        • hello sir,
          i am developing a project using SQL. And i want to take backup of Server database from any the attached computers without using sql backup facility. I want to generate a script for that. what shall i do? reply

          Like

        • Hi pinal,

          Thank you for these great articles.

          I am looking forward to finding a solution for the problem “how to automate the creation of existing SQL2005 Scheduled Jobs into T-SQL script.”

          Could you please send me a copy of the e-mail above.

          Thanks a million.

          Like

        • I am developing a backup infrastructure for all of our SQL2005 dabatabases that exist on different servers but are all backed up on a single server. I need to figure out how to automate the creation of existing SQL2005 Scheduled Jobs into T-SQL script for disaster recovery purposes.

          can you provide me with the details to do this

          Like

          • To automate this I created two batch files. The first is called backup.bat and the second is call run_backup.bat which helps log the output of the first.

            Contents of backup.bat:

            echo off
            echo %date% %time% backup start
            for /f “tokens=1-5 delims=/ ” %%d in (“%date%”) do set newDate=%%e.%%f.%%g
            set fileName=MyDatabaseName.%newDate%.sql

            “C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2\SQLPUBWIZ” script -d MyDatabaseName -targetserver “2008” c:\databasebackups\%fileName%
            echo %date% %time% backup end

            Contents of run_backup.bat:
            C:\MyBatchFiles\backup.bat >> c:\databasebackups\log.txt

            Then I created a task in task scheduler (Start->Administrative Tools->Task Scheduler) to run run_backup.bat every night. This allows me to review the log file and make sure all went well.

            backup.bat is configured to export SQL Server 2008 objects, if you have other versions of SQL Server just swap out the “2008” for “2005” or “2000”

            I hope this helps!

            Like

        • Hello Pinal Dave

          I have scripted a Sql Server 2005 database currently hosted on godaddy. The scripted file size is 250 MB but when I am trying to run the script file from Management Studion I got an exception message “out of memory”. Kindly help me on this.

          Like

        • hello sir, i need your help. i am going to make a script that will be executed by command prompt make the .tab or .prc file according to the file exists in the syste, can you help me or provide script.

          Like

        • Hi
          I want copy a table from one database to other with all functions,triggers,procedures,views and all how to do please let me know.
          Thanks
          ramu

          Like

  1. 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.

    Like

  2. Pingback: Top Posts WordPress.com

  3. 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

    Like

  4. 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

    Like

  5. 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.

    Like

    • Hi,

      I love the restore script you have, but would like to find a way to do a simply backup database to a .bak file. So far, I’m hitting a wall. Can you email me your thoughts? Thanks. SQL2005

      Like

  6. 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

    Like

  7. 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?

    Like

  8. 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 )

    Like

  9. 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?

    Like

  10. 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”

    Like

  11. 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

    Like

  12. 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

    Like

  13. 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 )

    Like

  14. 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..]

    Like

  15. 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

    Like

  16. 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

    Like

  17. 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.

    Like

  18. 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…

    Like

  19. 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

    Like

  20. 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.

    Like

    • I absolutely agree with you. Can you suggest something that could help to solve that problem?

      Is there any tool here that can check the script generated by wizard and report about inconsistencies in that script?

      Because you never can see the problems before you run the script. And when you hit those errors you can’t just skip them and continue the script from that point where you stopped. You have to fix the script, delete the database and run the script again.

      Sometimes that wizard generates unbelievingly wrong scripts. For example it generates some CREATE VIEW statements before even CREATE TABLE statements and you have to fix that manually, isn’t that just pain in the ass?

      Like

  21. 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

    Like

  22. Pingback: SQL SERVER - 2005 - Generate Script with Data from Database - Database Publishing Wizard « Journey to SQL Authority with Pinal Dave

  23. 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

    Like

  24. 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?

    Like

  25. 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!

    Like

  26. 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.

    Like

  27. 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

    Like

  28. 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?

    Like

  29. 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

    Like

  30. 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…

    Like

  31. 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.

    Like

  32. 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;”);
    }

    Like

  33. 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

    Like

  34. 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.

    Like

  35. 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

    Like

  36. 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

    Like

  37. 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

    Like

  38. 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).

    Like

  39. 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.

    Like

  40. **** 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.

    Like

  41. 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

    Like

  42. 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.

    Like

  43. 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.

    Like

  44. 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.

    Like

  45. 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.

    Like

  46. 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

    Like

  47. 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

    Like

  48. 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

    Like

  49. 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

    Like

  50. 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

    Like

  51. 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?

    Like

    • 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

      Like

  52. 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!!

    Like

  53. 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 !!!

    Like

  54. Hi Pinal,

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

    Thanks,
    Suren…

    Like

  55. I am create some table in one machine. i want to import that all table data into another machine ? what is the procedure?

    Like

  56. 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!

    Like

  57. 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

    Like

  58. 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.

    Like

  59. 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.

    Like

  60. 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.

    Like

  61. 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

    Like

  62. 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 .

    Like

  63. 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…

    Like

  64. 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!

    Like

  65. 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

    Like

  66. 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

    Like

  67. 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

    Like

  68. 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..??

    Like

  69. 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

    Like

  70. 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

    Like

  71. 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

    Like

  72. 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

    Like

  73. 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.

    Like

  74. 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

    Like

  75. 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

    Like

  76. 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

    Like

  77. 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

    Like

  78. 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.

    Like

  79. 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.

    Like

  80. 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

    Like

  81. 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

    Like

  82. @ 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.

    Like

  83. 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?

    Like

  84. 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.

    Like

  85. 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

    Like

  86. 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

    Like

  87. 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

    Like

  88. 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

    Like

  89. 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.

    Like

  90. 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.

    Like

  91. 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

    Like

  92. 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

    Like

  93. 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

    Like

  94. 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

    Like

  95. Hi sir,

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

    Thanks & regards,
    Swapnil

    Like

  96. 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.

    Like

  97. 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

    Like

  98. 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.

    Like

  99. 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

    Like

  100. 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

    Like

  101. @ 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.

    Like

  102. 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

    Like

  103. @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.

    Like

  104. Hi i’m doing C#, i want to create a application which will generate the script at runtime and update the newly database with that data

    happy coding
    thanking you

    Like

  105. Hi Pinal,

    Is there a way to generate the scripts for all the stored procedures present in a database into separate files? The files taking the name of the stored procedure….

    Thanks and Regards,
    Rama Krishna A

    Like

  106. Hi Imran,

    Same as farid, i would like to know how i can backup my database (including data) into 1 script. The purpose is to restore the complete script at my client side.

    i am using sqlserver 2005.

    Appreciate yout help

    Like

  107. I see several questions re: how to script to separate files (the way scptxfr.exe does in SQL 2000), but no answers.

    This is critical for effective versioning control.

    Does everyone agree that it is not possible to script to Generate Scripts . . . to separate files with SSMSE?

    – Tom

    Like

  108. Hello – I am installing a SQL Server 2005 Compact database created from scripts with a VB .NET application. The scripts seem to work and the installation of SQL Compact also works, but I cannot get the scripts to install the DB. I have tried several variations of SQLCMD from a DOS prompt but it is not working. I would like to make this as seamless as possible to the user and I can’t find anything out there to help! Thanks!!!!!!!!!

    Like

  109. Thank you for this information. We are all from different backgrounds and some may need to backup their databases due to the neglect of others.

    Even notepad has a help file :-)

    Like

  110. Yes, I’d love to know how to get it to backup the actual data also. It is kind of pointless to backup empty tables, where is the real important stuff, the data?

    Like

  111. First, I want to qualify my ignorance with SQL 2005 and I depend on posts like this to help me with the obvious.
    I needed to make a copy of a database. I started by creating a new blank database. Then I exported my data from my source to the new database. Unfortunately none of the stored procedures exported and I needed them also. I found this blog, created the script from my source database, pasted it into a new query in my target database and ran the script. With the combination of these two easy steps, I have a complete copy of my database, data and all. It worked for me…

    Like

  112. 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

    Like

  113. @ 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.

    Like

  114. 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

    Like

  115. 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 .

    Like

  116. 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.

    Like

  117. 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

    Like

  118. 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!

    Like

  119. 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

    Like

  120. 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.

    Like

  121. 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.

    Like

  122. 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.

    Like

  123. 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.

    Like

  124. 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

    Like

  125. Hi Pinal,

    I am using the same way as you mentioned in SQL 2008, but instead of “Scripts all objects in the selected database” i selected only the Stored procedures of my database.

    and when I executed the script on some other DB few of the Stored procedures are skipped in the script,

    On investigation about the name of the stored procedure, Its noticed that the skipped sp’s are the one which I had used in many other stored procedure.

    Please advice , how can I get the scripts of all the Sps.

    Thanks
    Manish Kaushik

    Like

  126. Hi,

    Does anyone know how to get SQL Server 2005 Generate Scripts to output ONLY the indexes, constraints and triggers (i.e. not the CREATE TABLEs)??

    I can do it in SQL Server 2000 Generate SQL Script by:
    1st Tab – Selecting All Tables
    2nd Tab – Unselect Generate DROP and CREATE commands
    3rd Tab – Selecting the Table Scripting Options in the third tab.

    Thanks,
    Niko

    Like

  127. Hi,

    I know how to generate the script by above mentioned steps.
    But instead of manual generating script, have to write new storedprocedure with argument of table name.
    This has to return the script of selected table.

    Help me if u know the answer.
    Thanks……..

    Like

  128. hi Pinal,
    I want to do dynamic project which will get all database names dynamicaly.i want to generate dynamic query to get all data base names.Can you please help me for that?
    Waiting for positive reply
    Thanks in Advance.

    Vilinda

    Like

  129. Hi MaxWell

    Copy the SQl2005 Database to a new temp database. Downgrade to SQL 200- Database ( in DB properties). If needed ( using 2005 only conventions ) remove/change them.
    Once done . Backup and restore in 2000 server, Or detach and reattach.

    – Gs

    Like

  130. Hi,

    Can I automate the generation of scripts (Schema)?

    I need to schedule it as a part of daily backup process. Please help me in this regard.

    -Hema

    Like

  131. hi pinal,

    i hv 1 problem . i want to take back of sql server database tables n all other objects. but i dont hv access to database mgmt. studio. so how can i take my database backup..?

    i hv heard tht it is possible using writing script.. n running it on our server as .aspx page…

    so can u help me out in this issue..?

    thanks ..

    waiting for yr response..

    Like

  132. hi ,
    i have one problem, i installed the sqlserver2005 in my system but i didnt get the sample databases like northwind.. and also i didnt get the reporting services.. how can i get these plz help meee and also tell me some points to do this…

    Like

  133. hi,

    This one is really good.

    If anybody want to Delete All Data from DB then Just Run the following query.

    EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
    GO

    EXEC sp_MSForEachTable ‘
    IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
    DELETE FROM ?
    else
    TRUNCATE TABLE ?

    GO

    EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
    GO

    Like

  134. Hi,

    I want to know if is it possible to alter a table (adding a column) in live production SQL server 2005 instance?

    If anyone know please reply.

    Thanks

    Like

  135. Hi,

    I want to know : how to copy some table from database A to database B in microsoft sql server 2005 ? And then how to schedule it everyday ?

    Thanks and regards,
    Sandhi

    Like

  136. Pinal,

    My Manager has asked me to write a job in TSQL to BACKUP SCHEMA only NO DATA. I became aware of the GENERATE SCRIPT option through this blog. I thank you for showing this and for the rest of you thank you for the discussion. I was able to get a jist of the good and bad of this GUI script. Now, to my question. Is there any way to do this via a TSQL or is the GUI the only choice. I do not want to sound ignorant when i recommend the GUI option.

    Please help

    Thank you

    Yousuf

    Like

  137. Have you ever used the red-gate dot com software products? Just google it and take a look. I have used SQL compare in the past. Great thing!

    Like

  138. Sir
    Hello How r u
    I Have Query Pls Help Me
    I am Using SQL Server 2005
    i make Secure My Database.
    So Make Certificate for the Database
    but how to use it how it Make Protected to External Use
    of Database.
    Dinesh Sharma
    (Matrix Solution)

    Like

  139. Hi

    i want to ask that while am running my website first time than i want that the data base create itself at a specified location as the wensite execute first time.

    Like

  140. Hi, There is also a database copy wizard in SQL Server 2005. Just wanted to know if the results of both (The generated script & Database Copy wizard) will be different?

    Like

  141. Hi Pinal,

    I want to copy table with its contents from one database to another database within the same SQL server 2000. May I know how to do that?

    Like

  142. Hi,if anybody requires how to generate script of database object using vb.net then
    In this example i have used SQL cmd Utility and BCP

    Like

  143. Hi,if anybody requires how to generate script of database object using vb.net then

    contact at vikiahireengg on gmail
    In this example i have used SQL cmd Utility and BCP

    Like

  144. Sir,
    I am working with .net, for one Save method i want to gengrate script ,But when i save 1 value and again try to save another it will not work.

    Plz help.

    Thank You,
    Dhananjay.

    Like

  145. Hi sir,
    i m facing problem in creating database in SQL SERVER 2005.so plz it my request to send a step by step process for creating database and corresponding Tables under a database.

    Like

  146. Hello Pinal!

    first of all thanks for another great post! :)

    i havent had the time to read all the comments on this one, but two questions that i see frequently asked on these comments (and also on my daily job) are: “how do i generate a create script for a given table or SP or view or trigger?” and “how do i copy all the tuples from a table on a given DB to the same table on other DB?”

    i suppose you have answered these questions over and over again, so i’ll leave here my aproach so further comments here wont give you much trouble answering :P

    first of all there are two very useful sys procs that are sp_helptext and sp_help. the first of them is very usefull to know the code for procedures or views or other “text objects”. with sp_help you can get all the info about a table.
    to generate the create script, just need to right click over the table name on SQL Server Management Studio’s Object Explorer and click “script table as…” and choose if you need the code for creating or inserting, or whatever. This is when people ask: “but i thought you were talking about helpful scripts, other then using Management Studio’s feature…” true. Just open SQL Profiler and see the query that the Management studio itself runs for that given table, and you’ll understand the few changes you’ll need to do for changing the table name and/or schema ;)

    to answer the second question, just need to google the following string: “sp_generate_insert_script”.
    it is a very usefull script to get the insert into table(col,col2) values (val,val2) statement u need to migrate the contents of the table elsewhere :)
    WARNING: it wont work for those imported temp tables that the name starts with $.

    hope i could be helpful for someone!
    tell me what you think about what i just said, and what are the pros and cons that you find here.

    Kind Regards and apologies for my poor english,
    Afonso from Lisbon, Portugal :)

    Like

  147. I am trying to change the shema of objects in database.
    How can I do?
    I have a database like TestDB the table name is START.CUSTOMER where START is Schema and I want to change it agian in DBO. How can I do?

    With Regards

    Like

  148. @Ravi.

    You can do it like this,

    Alter Schema New_Schema_Name transfer Old_Schema_Owner.ObjectName

    In your example you can do like this.

    Use TestDB
    ALTER SCHEMA DBO TRANSFER START.CUSTOMER

    This will transfer object customer from START schema to DBO schema.

    Hope this helps.
    IM.

    Like

  149. Hi..
    as you told i got all views in table form… in sqlserver 2000

    so how to get it back in view form …

    and i can not create all views again …
    they are around 250 so… Please

    Thanx

    Like

  150. @ Sandeep,

    Follow Screenshots properly, it has everything starting point to ending point.

    At the last step you will get a new WIndow which will have all views scripts, you need to run that script in the database you want to create all these objects,

    Also, please clearly explain your question.

    Regards,
    IM.

    Like

  151. There is a way of transferring databases between 2005 and 2000 via Generate Scripts. You will need to drop several 2005 features though like Extended properties,etc.

    More importantly the Script for Server Version must be set to 2000. I’ve tried it. It works.

    Like

  152. Hi PinalDave,

    I am afraid I don’t have very clear the concept of scripting the database. All I want to do is to copy a database from a computer or server to another computer or server. I tried to script the database to a file thinking that I could run it on the other computer and that I would be fine but it would ask me for the logging information which is not going to work as I am in a different computer. So, I am confused in which method should I use if there is any possibility of copying a database to the other computer.
    An another thing… in case there is a method, would I be able to copy all the Data too?

    Forgive my lack of knowledge please…

    Any help would be appreciated.

    Thanks,

    Al

    Like

  153. hi
    i wannt to asked any one if some time we are forgot password in sql server 2005 or 2008. how can chage password in both version with re-installing the software
    if any one have this answer please give me reply

    Like

  154. hi
    i wannt to asked any one if some time we are forgot password in sql server 2005 or 2008. how can change password in both version without re-installing the software

    Like

  155. Hi,
    Can you tell me how to change the definition in a function call when moving to diferent DB’s please.

    old_db replicated to new_db but the views contain definition calls to the old_db functions

    i.e.
    select data,
    old_db.function(variable) as result
    from data_table

    Should be

    select data,
    new_db.function(variable) as result
    from data_table

    thank you Graham

    Like

  156. @Graham

    This is a serious issue.

    I came across same issue recently. Earlier when I wrote scripts for an application I hardcoded database names in script. When we moved our database from one environment to other, name of the database changes accordingly to naming convention and I saw my script doesn’t work any more in new environment.

    Best way to over come this issue would be
    1. Don;t use FourPart name if not needed For example if you are running this script in database Db_Example, and your script is something like this,
    select * from Db_Example..Table1.
    This is just waste, as it is of no use.

    2. Use a variable that select database name by itself,
    something like this,

    declare @dbname varchar(100)
    select @dbname =DB_NAME()
    declare @sqlcmd varchar(8000)
    set @sqlcmd = ‘select * from ‘+@dbname+’..Table1)
    Exec(@sqlcmd)

    You can perform step1 or step2 while creating sps/views/functions.

    At this time since you already have views created you can do this.

    Use Replace Function, this function will find and replace a string in text field.

    You can get the definition of views and functions from syscomments system table.

    Let us know if you need more help on this.

    Regards,
    IM.

    Like

  157. hi pinal sir,

    sir i want to attach/deattach and backup/restore database of sqlserver 2000 with the vb.net 2005
    how can i do it
    can u give me the vb.net code to attach/deattach .mdf database file and backup and restore database.

    actually i am devloping one application which uses multidatabse and database is created dynamically how can i do this with sqlserver 2000.

    this is completly done with ms-acess but is slow and single user

    please reply me as far as possible.

    thanks in advance.
    and anyone expert read this comment and want to help me or reply me please send your help

    Like

  158. hello sir,

    i have requirment that i want to complile all the procedurs
    of db to another db.

    both db are contains same list of procedure
    .
    but i want compile all the procedurs

    is there any script to do like this work.

    kuldip bhatt

    Like

  159. Thanks for sharing the knowledge.

    I was wonder if we could write a stored procedure that would delete all the unused stored procedures after a month or certain deadline.

    Is it possible????

    Like

  160. hi thanks for the post it is very help full
    i whant to copy all the data from one dabase of sql 2005 to another databse of sql 2005 how can i do that

    Like

  161. hi ,
    can someone help me with audit triggers .
    am using column_updated function but it does not work for text data type fileds and more than 8 columns
    if i am using normal trigger script it is taking a image of all the rows even if there is a change in one row.

    please suggest on this issue.
    thanks.

    Like

  162. hi ,

    can i do the same thing with some other way with stored procedure. Actuly I want the stored procedure which will automatically copy one store procedure from one database to other database …

    thanks

    Like

  163. hi,
    In visual studio 2005 i want make a connection with sqlserver 2005,when i am using asp.net to make an connection sql data source connection is success but when i test query it will show an error msg that ‘there was an error executing the query.please check the syntax of the command and if present the type and values of the parameters and ensure they are correct……..
    invalid object name.

    help me how to rectify it……………

    Like

  164. i used this script to change collation setting of all databases(server)…

    i changed collation of all databases n i want all views, arelations betn databases…
    so

    will it work fine ???

    i did it but i want to know

    Like

  165. hello..

    as i wrote i changed the collate setting of a database but now im getting log in error of administrator..

    i resoved that problem by updating database engine…

    but i want proper technique to change it so

    please send or tell me the proper setting of STEP 4 (shown above in print screens) to get proper scripting to just change collate setting.. and i want all the properties, statistics, constraints ,etc related to that database

    Like

  166. Pingback: SQL SERVER - Top Five Articles of Year 2008 Journey to SQL Authority with Pinal Dave

  167. Hi,

    What if I wanted to script all the views of a database, taking into account dependencies (I have some views that are based on other views). SQL Server 2000 did this, but alas 2005 doesn’t seem to.

    Thanks. Any ideas welcome.

    Like

  168. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

  169. hi. i have done this for one of my database. i used ” generate scripts but at the end when it creates the query it will show an error like this :
    Msg 2714, Level 16, State 6, Line 1
    There is already an object named ‘Table_1′ in the database.

    i test it with several databases but they produce the same error . what should i do? please help me.
    thanks.

    Like

  170. and i run it for one of my database. i used “tasks->generate scripts” but at the end when it creates the query it will show an error like this :
    Msg 2714, Level 16, State 6, Line 1
    There is already an object named ‘Table_1′ in the database.

    i test it with several databases but they produce the same error . what should i do? please help me.
    thanks

    Like

  171. Hi Pinal,
    In my project requirement is like, I have one master datbase when I went in one form where i sholud create new database copy same as master by simply clicking on create database button it should be same db has been created on given location. and attached with my server……

    Thanks in Advance..

    Like

  172. Hi. Following up my post from Feb 26…

    When I used the Wizard of SSMS to script all my views, the views did not come out in the order expected, i.e. the views that were referenced by other views were not created first in the script.

    I have today discovered the joys of sp_refreshview. After running sp_refreshview on all my views, my view script generated from the SSMS Wizard now has the views in the correct order.

    I obtained this genius script to run sp_refreshview on all my views from here…

    http://www.fits-consulting.de/blog/PermaLink,guid,bc048c6b-9338-45ec-82bb-da688eb69c53.aspx

    Cheers.

    Like

  173. dear Friends,

    How can copy one table from oldTable include Data, indexes, constraints, or triggers?

    if SQLSERVER 2005 have SP to solve this problem please tell me.

    thx

    Like

  174. Hi pinal .

    Hear my requiremet is , in my production server just i want run one stored procedure on all databases. I have around 60 databases in that server but I don’t want to run individually and i want run at a time on all databases . Is there any script to run the SP on all databases plz let me know . If its yes then its great helpfull for me .

    Thanks in Advance

    Regards,
    Santu

    Like

  175. Sir,
    I Know the Process of Generating the Script file but i don’t now how to Secure the Data in the Table…..
    Could you please help me regarding this……..
    Thank “U”
    ……………………………………………………………………………………..

    Like

  176. How to create a dymnamic script to select field for a report from a list.

    1. We generate view by running scripts from application. This creates the main view for the report. The problem is that the fields for this view can change when customer adds fields to report from application.

    2. So we need to create a stored procedure that will dynamic select the fields from the view (rather than creating a series of pivot tables that will have to be changed each time the customer added another field to their report).

    3. The database does have a table that stores all the table names and fields but how would I need to code the stored procedure to pick fields that meet certain criteria. For instance, if the field has PL in it than this field goes into the planning bucket. But this same field will also have CY or CY1 for different cycle years.

    Like

  177. hi,
    This blog help me much. But once a create the scripts, how do i create a msi/exe file to create an automated installer for my database? please help me..

    Thank You!

    Like

  178. When I execute the script, about 90% of the time I get the error message “‘IUSR_E18586′ is not a valid login or you do not have permission.”. The tables are created, but they are empty.

    Like

  179. @ Mike Adams.

    When I execute the script, about 90% of the time I get the error message “‘IUSR_E18586′ is not a valid login or you do not have permission.”.

    Response: May be you are scripting users in the generate script. When users are scripted they include login information also. Just as an example, using the same screen, I have generated a script for a user called Imran which is attached to login ImranMohammed.

    CREATE USER [Imran] FOR LOGIN [ImranMohammed] WITH DEFAULT_SCHEMA=[dbo]

    When after you have generated script on a server, and you are trying to execute the same script on some other server, the above script will try to create a user, Imran in that database, but it will search for login name, ImranMohammed, if this login name is not present in new sql server instance then you will see that error : ImranMohammed is not a valid login.

    or

    In the script you generated, there could be a script that is actually impersonating another login, something like. Execute this stored procedure as IUSR_E18586 login ( which has high privileges) and this login might not be present on new sql server instance.

    Long Story Short : Login Name is used some where in the script you generated and that login name is missing in the new sql server instance on which you are trying to execute this script.

    Question 2:
    The tables are created, but they are empty.

    Response: You are just creating script for the table structure. This process actually copies only the structure of the table and all information about keys, indexes, constraints, that is it. This script will not generate data for you.

    But if you follow this article, using this you can also generate script for data as well as sturcture, and when you execute this script on new server, you will see both tables and data inside those tables.

    This is screen shot explanation, how to use Database Publishing wizard.

    http://products.secureserver.net/products/hosting/PublishingWithDPW.htm

    ~ IM.

    Like

  180. Hey

    how this process can be automated?

    I mean how the scripts can be automatically generated using osql or .. ??

    so that i can schedule it ..

    Like

  181. Hello

    I want to copy a table from another server to my server, and schedual it.
    up to now I create a vb.net program and did it, but from now i’m looking for a new way with sql server ability.

    Regards,
    R.Haddadi

    Like

  182. I have looked in all the posts to this article, but no one seems to address the question – how does one get the wizard to include the data as well? I don’t just want the ” Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects”…I want the data as well?

    Like

  183. I took the scripts of table and then I took the scripts of others. But I took errors when I executing other scripts especially on procedures:
    1. Ambiguous column name ‘ID_SINIF’.
    2. Invalid column name ‘ID_PERUCRGID’.”
    3. Column “OkyAday.SEMT” is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
    4. Insert Error: Column name or number of supplied values does not match table definition.
    5. Could not find server ‘LOGO’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
    But the procedures are correct. How can I solve this problem?

    Thanks.

    Like

  184. Hi friends
    I found a neat solution to execute scripts on nested views.

    I have about 130 views with some having up to 4 layers of views each depending on the other in the lower layer.

    The generate script function generates the script for all views correctly. But on execution, it fails when it cannot find the lower level views that are needed for higher layer views. This is because, the CREATE script for the views are not ordered in the dependency sequence correctly. Hence the script looked as if it failed.

    However, i did get it right when I ran the script over and over again for 4 times on the same database. What happened was that the script kept adding views in each run for the lowest layer for which it found all the dependent views. The lowers layers were added in the first run and the higher level views were not added. In the second run, it could find the lowest level view for the next level views and hence they were created successfully. When I ran 4 times, it added all my views correctly into the database! It was so simple to do and it took so long to find the answer!

    Raman Venkatraman

    Like

  185. Hi Pinal,
    Need your advaise ,
    I want to backup database from existing server and it should keep the backup in another server and this process should do daily.

    Thanks

    Like

  186. Hi Pinal ,
    i sent mail but reply is not correct solution which i want
    i do not want the scripts to take table , sp,trigger,functions etc.

    I want to take single database backup and store it in other server in diffrent dirve will it work using mapping or any other way to workout this issue.

    Thanks,
    Harinath

    Like

  187. Hi,

    Do you have/know a scrip to copy the data of all the tables (or sometables) from one database to another???

    I have done this with a software. But Id like to know if any script that do this kind of export…

    Regards

    Like

  188. Hi,

    Can we do this stuff by using SQL Statement?.

    If there are 500 Stored Procedures and I want to generate the script for 50 stored procedures, its difficult stuff to find out and generate the script by wizard.

    if any any solution please give.

    Thank You,
    Jayraj Todkar

    Like

  189. Thank you for a helpful article. And no, it’s not like writing an article for Notepad — I would not have even known of the existence of this facility if it weren’t for your article, so thanks!

    Like

  190. USE [master]
    GO
    CREATE DATABASE [abc] ON PRIMARY
    ( NAME = N’abc’, FILENAME = N @mdf, SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    LOG ON
    ( NAME = N’abc_log’, FILENAME = N @fullldf , SIZE = 2304KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    COLLATE SQL_Latin1_General_CP1_CI_AS
    GO

    Hello i need to pass the path of mdf and ldf in varaiable . Can you help me to how to pass it becasuse i am getting error when i trying to do this

    Like

  191. @Liliana

    Check out the script below i used to copy data from a table in one database[db1] to another[db2] using a cursor,both databases had the same schema,u can modify it to include more than one table.

    —–
    declare @id int
    declare @userid int
    declare @logtime datetime
    declare @logoff datetime
    declare @isonline bit
    declare @workstation varchar(50)
    declare @modid int

    declare mycursor cursor for select
    db1.dbo.hrsloginhistory.loginhistoryid,
    db1.dbo.hrsloginhistory.userid,
    db1.dbo.hrsloginhistory.logintime,
    db1.dbo.hrsloginhistory.logofftime,
    db1.dbo.hrsloginhistory.isonline,
    db1.dbo.hrsloginhistory.workstation,
    db1.dbo.hrsloginhistory.moduleid from db1.dbo.hrsloginhistory
    open mycursor
    fetch mycursor into
    @id ,
    @userid,
    @logtime,
    @logoff,
    @isonline,
    @workstation,
    @modid

    while @@fetch_status=0
    begin
    insert into db2.dbo.hrsloginhistory(loginhistoryid,userid,logintime,logofftime,isonline,workstation,moduleid)
    values(@id ,
    @userid,
    @logtime,
    @logoff,
    @isonline,
    @workstation,
    @modid)

    fetch next from mycursor into
    @id ,
    @userid,
    @logtime,
    @logoff,
    @isonline,
    @workstation,
    @modid
    end
    close mycursor
    deallocate mycursor

    Like

  192. Can we take only indexes script from one database if it is possiable than please help me because i want to move all indexes from default file to created new data file (specialy created for indexes).

    Like

  193. In the Scripting Wizard, after you choose the DB, you come to Choose Script Options. about 2/3’s of the way down, you see an option labeled Script For Version where you can choose to script it for 2005 or 2000. I think in this way you can script a DB in order to restore to a sql2000 box, since the restore for last night’s backup option has never worked for me.

    Like

  194. Thanks. This was helpful.

    Somewhere in the blogs someone posted that this is self-explanatory.

    I think the number of responses are self-explanatory to contradict this.

    Like

  195. Hi

    i do not have any instance of SQL server at my machine but while installing a DOTNET product i connect to the another machine SQL Server and i want to create a database by executing a script on that machine. Is it possible.

    Thanks in Advance
    VImal

    Like

  196. Hi Pinal,

    How to Clone a existing sql database???So that to create a new one…..or is there any query to generate this?if its possible please notify that also…

    thanks in advance
    Satheesh P.M.

    Like

  197. Sir,

    I want to generate separate script files for each stored procedure in my database.

    And it should be in the format
    IF EXISTS DROP SP
    CREATE sp

    I am using sql server 2005 management sudio express

    Thanks,
    Priya

    Like

  198. What is the code run behind the Generate SQL Script. I need to build a code which will dynamically copy the table schema in DTS package using global variables. I tried using Config INI file and global variables but couldn’t reach to the solution of generating a schema of source table to destination table.

    The purpose of doing this is we need to transfer table from one server to another server without having any manuall intervention. So we can just pass the parameters and everything sets on its own, The DTS package suppose to generate the schema at destination table and then copy the data at destination.

    Could you please help me out in finding the code that SQL Server runs behind the Generate SQL Script GUI.

    I tried to run SQL Server Profiler to trace the code but didn’t help enough.

    Like

  199. Hellow sir

    just i copy a database in my sql databases and i want paste this database in another system sql databases without taking any backup or any restoring process

    Like

  200. Hello,

    how can I add the ‘USE [DATABASE]’ statement to the create-script, when I’m using “SQL-Prompt Script Generation” as coded in SQL for automated Script Generation.

    If I am using the Wizzard, there is an option to mark this issue as true. But in the ScriptMethod (for SQLDMOObjects) I can’t find any options to set this true.

    Like

  201. Hi,
    I want to move records from one database table to another database table ( both table structures are same) . But i want here i want to move first 1000 records and then next 1000 records like that. i don’t have any key in my table.

    Thanks in advance.

    Like

  202. How can i retrieve list of user defined roles assigned to a user ? I tried with sp_helpuser gives only one role at a time…even though user has mutliple roles… again this is for SQL Server 2000 db

    thanks in advance for the help……

    Like

  203. Pinal,

    You mentioned in the few top replies that you sent e-mails to people showing how to generate similar scripts programmatically (in other words, script table creation and all related information: indexes, constraints, triggers) using code.

    Would you please send me this by e-mail as well and it may be a great new blog post.

    Thanks in advance.

    Like

  204. Hi Pinal,

    I think the information you provide is great, please keep up the work.

    If possible could you please send me the script to automate this process

    Thanks in Advance
    Brett

    Like

  205. i want comparation between two data bases table wise, i want only diffent table names and different columns only

    like

    tablename col1,col2

    if (ds.Tables[0].Rows.Count > 0 && ds1.Tables[0].Rows.Count > 0)
    {
    try
    {
    IList missedFields = new List();

    foreach (DataRow dr in ds.Tables[0].Rows)
    {
    bool stts = false;
    foreach (DataRow dr1 in ds1.Tables[0].Rows)
    {
    if (dr[“Column_name”].ToString() == dr1[“Column_name”].ToString())
    {
    stts = true;
    break;
    }
    }
    if (stts == false)
    {
    missedFields.Add(“from US:” + dr[“Column_name”].ToString());
    }
    }
    if (missedFields.Count == 0)
    {
    Response.Write(“Success”);
    }
    else
    {
    DataTable dt = new DataTable();
    DataColumn dc = new DataColumn(“Missed Field Names:”);
    dt.Columns.Add(dc);
    foreach (string str in missedFields)
    {
    DataRow dr = dt.NewRow();
    dr[0] = str;

    dt.Rows.Add(dr);
    }
    GridView1.DataSource = dt;
    GridView1.DataBind();
    }
    }
    catch (Exception ex)
    {
    Response.Write(ex.Message.ToString());
    }

    }

    Like

  206. Hi,
    Can we have such type of sql commands by which we can generate create script of any table.
    I am thinking about to making a procedure which can copy a table with its whole data.
    Thanks
    Rahul

    Like

    • Hello Rahul.

      Use Database Publishing Wizard to generate script of tables with data.

      Try searching for key word Database Publishing Wizard either in Google Search Engine or SQLAuthority search.

      ~ IM.

      Like

    • Hi Rahul,

      In SQL Server 2008, you can script the table with data (an insert statement for each row) using SQL Server Scripting wizard. In this wizard, on Choose Script Options page set the Script Data option to True.

      Regards,
      Pinal Dave

      Like

  207. Pingback: SQLAuthority News – 1200th Post – An Important Milestone Journey to SQL Authority with Pinal Dave

  208. Hi,

    To cover this topic completely a whole book can be written. Here I mentioned few enhancement in SQL Server 2005 that are also applied to SQL Server 2008.

    New Data types
    Error handling with TRY-CATCH
    ROW_NUMBER and other ranking functions
    CTE
    OUTPUT clause
    INTERSECT and EXCEPT operands
    PIVOT and UNPIVOT functions
    CROSS APPPLY operator

    For more details please check the following article:

    http://technet.microsoft.com/en-us/library/cc721270.aspx

    Regards,
    Pinal Dave

    Like

  209. Hello,

    I was wondering what would be the best approach to creating a “template” to create a database, tables and indexes based off of current databases I have already created. I have used current tools within management studio and combined the ‘create database template’ with a the script created with one of the current databases which contains the tables and indexes I would like to be able to reuse. I don’t need data. Any help would be appreciated.

    Thanks for your time.

    Jake

    Like

    • Hello Aatish,

      Use the SQLCMD utility for this purpose. At first right the complete t-sql script to restore database in a notepad for example in c:\RestoreScript.txt
      Now go to command prompt and write a command as below:

      >sqlcmd -S “yourServerName” -U “UserName” -P “password” -i c:\RestoreScript.txt

      If you can login with windows credential then username and password are not required.

      Regards,
      Pinal Dave

      Like

  210. Dear Pinal

    I have a problem with a database in SQL 2008.
    I can not generate the script of this database. I only tried with the option of generating script in SQL Server Management Studio.
    I can generate script for all other databases except this.
    I also tried this database by taking backup and restoring it in another machine. The result is same.
    The scripting utility can see all the tables, functions and procedures etc. but it will show it is validating the settings, if we try to do a script.
    Any idea how to take script for this stubborn database..?
    Your valuable suggestion is requested in this regard….

    With Love
    Thomas

    Like

  211. Hi,

    Can anybody tell me how can I write a stored procedure to copy a database from one server to another server. This procedure is going to be accessed by relevant parameters from an application.

    Like

  212. Hi ,

    I am Fresher with more curious to learn new things.

    I am going to Design one web-Site for showing Details that had been stored on database (on Mysql which is existing one) ,Will it be possible to copy all that data in SQL-2005 database as it is.
    Also which tool/technique i prefer to copy that to my Sql database ,i want to design website in C#.net,Firstly it’s an it’s intranet application,if successful may ploy on Internet.

    Another question is that can i convert flash file application in to text ?

    hope you will be there for me
    thanks for your important suggestions!!!

    Like

  213. Sir,

    A)
    Could you please send me the details as well, on how to automate this process.

    B)
    Can you answer me the professional way to create a schema of tables in sql server for database building in develpment of website in asp.net

    Like

  214. Dear Pinal,

    This is a nice article and thanks for it.

    I have one question. The script generated by this only creates the empty tables & other structures. But the table data is not backed up.

    Is there a way to generate this script including the data that was in the tables too? (much like a mySQL dump)?

    TIA

    Like

  215. Dear Pinal,

    I just want to know
    “how to get schema and data both in sql server by export”.

    I have create the script for schema and get the schema but when i want to get the data it could not insert into my database. A column TimeStamp datatype is in the all the table so i have faced an error from this.

    Can you please help me out.

    Best Regards
    Karnak

    Like

  216. Dear Pinal,

    I have one problem to solve it.

    I have one server at India and another server in UK.

    I need to export some data from India to UK server

    But condition is we have to send only those data for which in every table selected sysid ( sysid is field in every coulmn).

    there are more than

    Like

  217. Hi
    A question, our website was developed under sql 2005, and the server it will be installed on is sql 2008. can my website run correctly or i will face some troubles? please i need an answer urgenty?

    thank you

    Like

  218. Views can provide advantages over tables :
    ———————————————————

    Views can represent a subset of the data contained in a table
    Views can join and simplify multiple tables into a single virtual table
    Views can act as aggregated tables, where the database engine aggregates data (sum, average etc) and presents the calculated results as part of the data
    Views can hide the complexity of data; transparently partitioning the actual underlying table
    Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents
    Depending on the SQL engine used, views can provide extra security
    Views can limit the degree of exposure of a table or tables to the outer world

    Like

  219. i know all sp names to be apply in new db, instead of selecting the required sp in th wizard is there any possible way to run the required sp in a db.

    Like

  220. Hello Pinal,

    Thank you for this great resource! I’ve got a SQL 2005 database on a Win2000 server that I need to upgrade. I’ve created a VM with Windows 2003 server and SQL 2005.
    I’d like to copy the database from the original server to the VM (which is on a different computer).

    I’ve generated your script that will copy the db, schema etc. I’m wondering if you would be able to help me figure out how to move the copy to another computer/server?

    Thanks in advance.

    Jeff

    Like

  221. Hello Pinal,

    Data Synchronization In SQL Server database table using sql script or TSQL script or code.

    I want to Synchronize my SQL Server database table at server end using client database table’s records.

    Actually, I have to updated SQL Server database table data/records with the client database.

    The client is sitting in the districts, who is entering the data/records collected from villages and block where there is no internate connection, so that data/records are entered offline at the client database these records must be updated and reflected at the SQL Server database at the end.

    If this can be accomplish by using TSQL ,Stored Procedure.

    Deepak Chandra

    Like

  222. deepak:

    TableDiff.exe (included in SQL Management Studio) will do the job for you.

    Is a prompt command that is provided with the source parameters and the destination file where the script will be generated.

    Google it and you will find use esamples.

    Byron.

    Like

  223. iam required to take the backup of the database on the remote server everyday at a particular time .so i do remote login daily and take backup ( right click>tasks>backup…..) and again i send the backup file back to my local system.Is there any way we can automate the entire process plz help me sir
    thanks in advance

    Like

  224. Hi Pinal,

    Could you please send me the details as well, on how to automate this process. I am trying to copy a database from a sql server management studio express.

    Thanks so much.

    Ryan

    Like

  225. I want to database with data so i am generate Script but In sql server my ansi padding Option is not available . and only genarete script which is contain table but not data. so how can i get table with data?

    Like

  226. Hi,

    I have two databases : Local , Prod

    Made the table structure changes into the Local DB tables.
    now i want to apply the changes on the production server while preserving data on the production server.

    thanks in adv..
    Vikas

    Like

  227. Hi Pinal,

    I hav 2 DB’s names TESTDB and SAMPLEDB , both DB table name is same(tableinsert) and fields name also same.

    I got TESTDB backup. Now i want to know whenever i restore .bak file to TESTDB at the same time SAMPLEDB also changed if it’s possible..? pls send me code and ideas..

    Thanks In advance

    Like

  228. Excuse my bad english.
    Error on replicated table.
    When i run sqlpubwiz on database with replicated table, i have : Erreurÿ: Erreur de syntaxe dans TextHeader de NumberedStoredProcedure ®ÿsp_MSdel_dboORD_PRO_PreneursOrdres;2ÿ¯.

    Like

  229. thank you thank you thank you I have been pulling my hair out trying to get ms publishing wiz to work but every where I looked I was told to do this longgggggg and complicatedddddd procedure i.e. you got this error because your db engine isn’t on (how do I turn it on? no answer) I was stuck looking and looking reading and reading. I will be the first to admit I still have a long way to go with c#.net, vwd and sql server before I will qualify as a web master but you have helped me immensely toward my goal of getting online and for this I can not thank you enough

    Like

  230. Hello Sir,

    i want to configure SQL SERVER 2008. when i give permission on perticular database with particular User id as DBO. that user access only that particular database.but he can see all Databases in his SMO.

    how can set , only the pemissable database see in his SMO. not all database on Server.

    thanks in Advance

    give me Reply as soon as possible.