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

659 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

          • Could i get a copy of that email? I’m also looking at automating creation scripts from various database.

            Thanks

          • 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

        • 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

          • 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

          • 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

        • 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

          • 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

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

        • 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

        • 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

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

        • 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

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

        • 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

          • 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!

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

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

        • 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

  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.

  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

  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

  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.

    • 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

  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

  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?

  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 )

  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?

  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”

  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

  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

  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 )

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

  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

  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

  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.

  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…

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

    In 2000 it is a easy task in Enterprise Manager.

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

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

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

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

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

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

  25. Does anyone know to generate a script for an Index which should not be generated using the above article..It should be through front end.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  53. Hi

    I was trying to take back up of all stored procedures,

    but I was not getting the option “Files to generate”,

    So that I can save each stored procedure in separate files

    instead of single file.

    Can you help me how to get that option?

    • If you want to store the script of each procedure to each file, refer this blog post
      http: //beyondrelational.com/blogs/madhivanan/archive/2009/10/26/script-out-procedures-to-seperate-files.aspx

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

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

  56. Hi Pinal,

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

    Thanks,
    Suren…

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

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

  59. Thanks sir for this big effort,

    PLS could you tell me what can i do to change the data type of column.

    Best regardes,
    Manar

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

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

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

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

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

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

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

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

    • for example your database name = gold
      your table name = tran

      create database gold
      go
      use gold
      go
      create table tran
      (id int, name char(6))
      go

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

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

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

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

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

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

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

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

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

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

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

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

  80. Dear sir

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

    Plz help provide.

    Thank u so much

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  99. Hi sir,

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

    Thanks & regards,
    Swapnil

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

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

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

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

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

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

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

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

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

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

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

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

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

  113. 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 :-)

  114. i would like to know how to generate script that will also have the data as well just like mysql , or is there any tool for that kind of script generation

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  151. 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 :)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  180. 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”
    ……………………………………………………………………………………..

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  206. Sir,

    How to i Migrate Windows Sql server Database to Linux Sqllite Database ????. Please Help Me

    – Jayaprakash Rajendran

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

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

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

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

  211. 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());
    }

    }

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

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

    • 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

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

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

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

    • 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  234. 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ÿ¯.

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

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

  237. Im developing a website.. & at d last i hv came to knw that d database must be on SQL server.. how could i create script, and reuse my old DB. i cant create d DB twice.. its too large.. plzzz smbody reply……………………………plzzzzzzzzzzzzzzzzzzz

  238. I am using VB.NET 2005 and SQL Express 2005. When I run the application it able to insert new records, but when I close the application the data is not reflecting in database.
    Please help.

  239. hi ,
    i want to generate automated backup from the database in sql server 2005 & 2008, so is it possible? if yes then how and please tell me what is the procedure for that. i m waiting for your reply..

    thanks,

    • Hi Ankit

      -After Login,Expand Management->create new Maintenance plan
      -Drag ‘Back Up Database Task’ to design window
      -Then right click and edit the back up database task as per your requirements
      -Finally make a schedule and save it.

      Hope,you were looking for this sol!!!

      Amit

  240. Hi

    I want to copy a databse with table’s content,constraint(everything) to local machine without any wizard.
    Is there any SP or any other way,where i can copy one database and paste the same database with different name through script.

    Thanks
    Amit

  241. I want to know that how we create a script with the backup of data that is store in the tables from server’, database????????

  242. Hi, pls help me. I need to write a query to find where data is storing by default after taking a backup in SQL.. I need to run dis query in many systems.. pls help me…

  243. I am trying to figure out a stored procedure to take an existing database, which is my base master database, and clone it exactly within the same server but with a new name. I have tried various ways including scripting but that won’t do because several tables in the master have data and need to be included in the clone. I have tried a stored procedure that would detach the master database, copy the mdf/ldf files using xp_cmdShell, then reattach my master and my new database. I have it working on one server but when I try on another, I get access denied errors during the xp_cmdShell copy part.

    Do you have any scripts in your toolbox or ideas on how to make this work for me without having to tweak every server I want to run this on.

    Thanks

  244. I do not wish to use the Management Studio as this is to be part of an application. The application is to call the stored procedure to execute the steps to clone my datatbase. As I mentioned, I was able to get it working on one server but because the permissions on the server were played with so much, we are unable to duplicate the settings on a second server. There must be something that can allow the sp to execute all the steps that are necessary.

  245. Is it possible to generate scripts for all objects of any particular schema using the tool “Database Publishing Wizard “?

  246. Hello Pinal Dave
    i have a one quistion
    I allready collectd db generated sql script
    now i need execute all object on vs memory (not file)
    i used ExecuteNonQuery used from richtextedit.text
    then incorrect syntax near ‘create’
    then incorrect syntax near ‘create’
    then incorrect syntax near ‘create’
    ….

    later I paste all script to sql management studio
    next run is successfully

    why didn’t run it???

  247. Pinal Dave,

    I need to take a db backup from a SQL server and make a copy to another SQL 2008 Express server on a daily basis.

    Could you please send me the details how to create an automated script to import the data?

    Your help will be greatly appreciated.

    Kate

  248. Dear sir…

    I want Database Replication…
    or i want to run report to different-different Database…
    so can a possible that making store procedure using different-different database please quickly reply..

    i wait..
    Regards
    Ashish Singh

  249. Is it possible to get an answer to my orginal question here? It seems that this post has gotten lost in the other posts.

    Here is my post again…..

    I am trying to figure out a stored procedure to take an existing database, which is my base master database, and clone it exactly within the same server but with a new name. I have tried various ways including scripting but that won’t do because several tables in the master have data and need to be included in the clone. I have tried a stored procedure that would detach the master database, copy the mdf/ldf files using xp_cmdShell, then reattach my master and my new database. I have it working on one server but when I try on another, I get access denied errors during the xp_cmdShell copy part.

    Do you have any scripts in your toolbox or ideas on how to make this work for me without having to tweak every server I want to run this on.

      • I need to clone the master database due to a security and confidentiality requirement wherein each client MSUT have their data stored in a separate database. Data from one client cannot be mixed with data for another. And before anyone suggests it, identifying each client’s data in the same database using a unique identifier is NOT an option. (caps are for emphasis only and not to be construed as yelling).

        Hope that explains it.

  250. Salhi

    Hello Pinal Dave
    i have a one quistion
    I allready collected db generated sql script
    now i need execute all object on vs memory (not file)
    i used ExecuteNonQuery used from richtextedit.text
    then incorrect syntax near ‘create’
    then incorrect syntax near ‘create’
    then incorrect syntax near ‘create’
    ….

    later I paste all script to sql management studio
    next run is successfully

    why didn’t run it???

    *
    on June 7, 2010 at 12:41 pm | Reply Madhivanan

    Make sure the scripts were seperated by the keyword GO

    it is too error

    Incorrect syntax near ‘GO’.
    Incorrect syntax near ‘GO’.
    Incorrect syntax near ‘GO’.
    Incorrect syntax near ‘GO’.
    Incorrect syntax near ‘GO’.
    …….
    Incorrect syntax near the keyword ‘CREATE’.
    Incorrect syntax near the keyword ‘CREATE’.
    Incorrect syntax near the keyword ‘CREATE’.
    Incorrect syntax near the keyword ‘CREATE’.
    Incorrect syntax near the keyword ‘CREATE’.
    …….

  251. I want to create script through coding or with in procedure?
    Is it possible??

    or i want to create table same as existing table with constrains. how can i do this???

    thanks.

  252. hello

    i want to create table2 same as table1′s structure with constrains, in procedure
    i have one option that first create script for table1 and it’s constrains, then execute that statements in procedure.

    but in that each time when i change in table1 i need to change in procedure’s script.

    In procedure i have total 12 tables and many constrains.

    Is it possible to do this by any other way?

    thanks.

  253. Excellent post for people who need to maintain and backup multiple servers. Its real pain to do everyhing manually !!!

    I have to maintain 3 server and my client perchased new server just for Backup :)

    I required to take periodic backup. I was doing it manually. I will not automate it using command given by you.

    Thank you for sharing…

  254. hi,

    could you please tell me how to generate script of multiple table within stored procedure.
    tables are predefined. i.e i want to generate tbltest1_112, tbltest2_112, tbltest3_112 same as tbltest1_111, tbltest2_111, tbltest3_111 respectively
    i want to do this task when i execute procedure names TestCreate_sp with one parameter, parameter value is 112
    so i want to get script of table tbltest1_111, tbltest2_111, tbltest3_111 with constrains.
    then i will replace _111 to _112, constrains also have suffix _111
    and then execute it.

    Have u any suggestion for me?

    Thanks in advance

    Thanks

  255. Pinal,

    we have sql 2000 server as prod and sql 2005 as reporting server. we would like to maintain a copy of the prod databases on reporting server for users to extract reports periodically. what is the most efficient way to do this ?

    how can we automate the process to get data worth the whole day on the sql 2005 server ?

  256. Hi Pinal,

    I can not view images in this article

    “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”

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

    Thanks,
    Pakbavil

  257. I upgraded from SQL 2000 to 2005. I had a dts package that would determine the last backup of my production database. It would then restore to another DB called reportdb. How can I accomplish this in SQL 2005. This job ran nightly so that the report database was a copy of production as of the midnight before. All users would then only access the report DB and never touch production DB.

  258. Hello again Dave, well my requeriment is “Take the previous database named p2010 and create a database named p2011 from p2010″, my idea is first backuped p2010 and restore this as p2011. I don’t know if this solution is the best because I don’t have idea if exists another option to do this.

    For the moment my steps are restore and backup:

    1. Backup

    BACKUP DATABASE p2010
    TO DISK = ‘C:\Archivos de programa\Microsoft SQL Server\MSSQL.4\MSSQL\Backup\some_name.Bak’

    1.1 Add to device

    EXEC
    sp_addumpdevice ‘disk’,
    p2010,
    ‘C:\Archivos de programa\Microsoft SQL Server\MSSQL.4\MSSQL\Backup\some_name.bak’

    (1.1.1 To correct for tests)

    exec sp_dropdevice p2010

    2. Restore

    (option 1)

    RESTORE DATABASE p2011_newName
    FROM DISK = ‘C:\Archivos de programa\Microsoft SQL Server\MSSQL.4\MSSQL\Backup\some_name.bak’
    WITH REPLACE,
    MOVE ‘filename_Data’ TO ‘C:\temp\filename_Data.MDF’,
    MOVE ‘filename_Log’ TO ‘C:\temp\filename_Log.LDF’

    /* Move to change drive:path */

    (option 2)

    RESTORE FILELISTONLY FROM p2010
    RESTORE DATABASE p2011_newName
    FROM DISK = ‘C:\Archivos de programa\Microsoft SQL Server\MSSQL.4\MSSQL\Backup\p2010.bak’
    WITH RECOVERY,
    MOVE ‘filename_Data’ TO ‘C:\temp\filename_Data.MDF’,
    MOVE ‘filename_Log’ TO ‘C:\temp\filename_Log.LDF’

  259. Hi friends,
    I need a help for converting sql script to oracle script
    i generate script from sql developer , now i want convert this script to oracle script.

    i am using ubuntu os 10.4, then i am installed oracle10g edition
    any one pls help me out for converting and how import script to oracle 10g

    pls pls urgent

  260. hi dev ,
    again vinot, pls help me out for the solution for
    sql script to oracle script
    i am having sql script, using ubuntu 10 os.
    and install oracle10g
    pls help me out, and how to run script in oracle 10g

  261. hi madhi,
    i cant find above site
    could u pls help for covert or tell me is there any tools(s/w) available for converting

  262. Hi, I need to validate the proccess of a backup and restore, all the issues that will occur, handling errors. It’s possible use a tray & catch sentence in windows server 2000?
    Thanks in advance

  263. Dear Experts,
    I’m facing a very critical situation…and no one is here to help me…So my humble request to guide me….

    Actually we have a server running a Intranet Site having os 2000 server and SQL 2000 .data also uploading to sql server i.e. at Denmark office…now I have to upgrade server in to a new machine…at first step i have transferred all data to new server .
    now ii want to shift completely sql 2000 DB to SQL 2005 server including all security / logins store process SQL server agent jobs…everything..how will i do??I have not much idea on SQL except backup restore…and all simple activities

    i have already took database backups from SQL 2000 and restore in SQL 2005…but how can I thasfer other Login and server agent jobs or any other process that is running in SQL 2000.

    Thanking
    You
    Prabhusap

  264. Hi,
    This is great, but it just create a script with create table structure. How can i get all data in database to the scrips also.
    Thank you alot.

    Xinglee

  265. I am looking for a script i can run that will restore 6 DB’s from a local drive on the SQL machine to 6 DB’s on the local machine. They are all named different but i am not very versed in the SQL world. I already have a script to stop and start all the DB’s now i just need to complete the script so i dont have to manually go in and restore each single database. I have to do this to them everyday and its quite a time consuming task and was hoping for some help. I will explain in some detail the names of the DB’s and hopefully that will help in the creation of such a script.

    Train01 (Full DB name) has a backup located at d:\backup\Train01.bak, I want that file to replace whats currently in the Train01 DB. I guess if i knew more SQL script code it would probably be very easy to do this but i dont. Any help or pointers would be very appreciated

    Thanks
    Joakim

    • I hope this example give to you some idea about how to do a backup/restore to specific drive:

      set ANSI_NULLS ON
      set QUOTED_IDENTIFIER ON
      go
      – =============================================
      – Author: IDS – Gerardo Angeles Nava
      – Create date: August 20, 2010
      – Description: 1. Make a backup. 2. Make a restore of backup but with another name (year + 1)
      – This stored procedure most be reside on the database server that contains the database to backup

      – HELP to ME:
      – If it’s possible that this script resides in external database server that’s no contains the database to backup,

      – =============================================
      Create PROCEDURE [dbo].[sp_Backup_RestoreWithNewName]
      @dbNameSource VARCHAR(100),
      @dbNameTarget VARCHAR(100)
      AS
      BEGIN
      SET NOCOUNT ON;
      DECLARE @dbNameToBackup AS VARCHAR(100)
      DECLARE @pathBakFile AS VARCHAR(255)

      DECLARE @dbNameToRestore AS VARCHAR(100)
      DECLARE @pathRestore AS VARCHAR(255)
      DECLARE @pathData AS VARCHAR(255)
      DECLARE @pathLog AS VARCHAR(255)

      DECLARE @data AS VARCHAR(100)
      DECLARE @log AS VARCHAR(100)

      /* Configuration path (drive): */
      SET @dbNameToBackup = @dbNameSource
      SET @pathBakFile = ‘E:\data\MSSQL\BACKUP\’ + @dbNameToBackup + ‘.Bak’

      SET @dbNameToRestore = @dbNameTarget
      SET @pathData = ‘E:\data\MSSQL\BACKUP\’ + @dbNameToRestore + ‘.mdf’
      SET @pathLog = ‘E:\data\MSSQL\BACKUP\’ + @dbNameToRestore + ‘.ldf’
      SET @pathRestore = @pathBakFile

      /* Remove device, if exists */
      /*
      IF EXISTS(SELECT * FROM sys.backup_devices WHERE name = @dbNameToBackup)
      BEGIN
      exec sp_dropdevice @dbNameToBackup
      END
      */

      /* Generate Backup */
      BACKUP DATABASE @dbNameToBackup
      TO DISK = @pathBakFile

      /* Mount the sql-device, if exists */
      EXEC
      sp_addumpdevice ‘disk’,
      @dbNameToBackup,
      @pathBakFile

      /* ¿Have a backup? */
      IF EXISTS(
      SELECT DISTINCT
      S.[name]
      ,DATABASEPROPERTYEX(S.[name], ‘Status’) AS [Estatus]
      ,crdate
      ,[filename]
      FROM master..sysdatabases S
      JOIN msdb.dbo.backupset B
      ON S.[name] = B.database_name
      WHERE
      S.[name] = @dbNameSource
      and CONVERT(DATETIME, CAST(crdate AS VARCHAR(16)),120) = CONVERT(DATETIME, CAST(database_creation_date AS VARCHAR(16)),120)
      )
      BEGIN
      /* Restore the Data Base with another name (year + 1) */
      SELECT @data = [name] FROM master.dbo.sysaltfiles
      WHERE [dbid] = (SELECT [dbid] FROM master.dbo.sysdatabases WHERE [name] = @dbNameSource and fileid = 1)

      SELECT @log = [name] FROM master.dbo.sysaltfiles
      WHERE [dbid] = (SELECT [dbid] FROM master.dbo.sysdatabases WHERE [name] = @dbNameSource and fileid = 2)

      RESTORE DATABASE @dbNameToRestore
      FROM DISK = @pathRestore
      WITH REPLACE,NORECOVERY,
      MOVE @data TO @pathData,
      MOVE @log TO @pathLog

      RESTORE DATABASE @dbNameToRestore WITH RECOVERY;

      /* ¿Have a restore? */
      IF EXISTS(SELECT name FROM master..sysdatabases WHERE [name] = @dbNameToRestore)
      BEGIN
      /* Yes we have it!*/
      IF EXISTS(SELECT name FROM master..sysdatabases WHERE [name] = @dbNameToRestore AND DATABASEPROPERTYEX(name, ‘Status’) = ‘ONLINE’)
      BEGIN
      /* ¿The database is online? */
      SELECT ‘Restore: yes. On line: yes.’
      END
      ELSE
      BEGIN
      SELECT ‘Restore: yes. On line: NO.’
      RETURN
      END
      END
      ELSE
      BEGIN
      SELECT ‘Restore: No’
      RETURN
      END
      END
      ELSE
      BEGIN
      SELECT ‘Backup: No’
      RETURN
      END

      SELECT ‘Now you have a backup an restore with another name’

      END

  266. Hi Pinal,

    I´m trying to restore the Database schema and objetct (wirhout data) from MS SQL 2005 script file, generated in the way you justdescribed.

    I’m trying to restore to MS SQL 2008 Express, but I got many errors when I try to run the script in the MS SQL Server Management Studio.

    I would like if there is some compatibility issues between the version 2005 and 2008, or if you have some tip about this process.

    Thanks in advance!!

  267. Hi Pinal,

    I am trying to compare store procedues from two different databases but same server in SQL Server 2005.
    I have two databases on samesever Test1 and Test2. There are few store procedures in Test1 and few in Test2. I have to compare these two databases and then make a .txt file of there store procedure. Is it possible? If possible then how?

    Thanks,

  268. pinal,

    how can i transfer the views to another views wherein the views is in the table when i import the table and views to the other database pls. help….

  269. Hi all,

    I am looking for a way to script out all of my database objects. can anyone help me out so that i can have a single script to so this task. I dont want to use generate script utility separately to generate script individually.

    Experts kindly help me out.

    BR
    Rahm

  270. hello sir
    i have one column in d_o_j column.i have 400 col in d_o_j
    example

    12-8-2010
    12-9-2001
    12-7-2007
    05-12-2001
    08-1-2010
    26-12-2009

    i want a that all the col have come date to date means
    2001,2002,2003,2004,2005,2006

    clustered index used to arrange the physical order a to z

    means date col come 2000 to 2010
    i know you are busy but if you are free plz solve my query
    thanks sir

  271. hi pinal,
    here i have a query related SQL jobs. my requirement is that job should be executed in a prescribed time duration. hoe to create job and what are the steps please let me know .

    Awaiting for your positive and soon response,
    Thanks,
    Madhu L. Chande

  272. sir,
    now i entired into .net world… and i want basics of all sql

    queries,store procedures,connection,restore and so on… so

    sent Basics of all SQL SERVER 2005 queries

  273. Hi pinal,

    we are planning to migrate from sql 2005 to 2008 so i prepared backups while searching get some interesting things in back ups i found your site and i wonder about script automations its really cool solutions , i have a query that whether scripts took in sql 2005 will run in 2008 irrespective of the version, kindly do mail how to do the automation process and idea was simply super,

  274. I’m unable to create database in sql server 2005…….the error is “CREATE DATABASE permission denied in database ‘master’.”….So,please give solution my problem !!!

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

    Great article, thanks in advance.

  276. Hello,
    I would like to know how to run scripts into a new server. I used SQL 2005 to generate scripts for each database on the old server. No I want to apply those scripts to the new server to create the database structure with no data. So that I can then restore the databases into the new database on the new server.

    Thanks

  277. Dear Pinal,

    Is it possible to generate a script file only for Stored procedures (all the procedures) in sql server 2005/2008. In sql server 2000 we can use EM to generate script file, but 2005/2008 it is difficult for me to take a complete script.

    Expecting your reply.

    Thanks in advance Pinal.

    Regards
    Kumar. NM

    • Hello DJinn,

      Pinal already answered your question, please refer to his reply on this same page, look for January 8 2010.

      Quoting Pinal’s reply,

      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

      ~ IM.

  278. good evening sir.
    i have one problem in sql server 2008 R2
    I am not able to restore the database form 2008 to 2008 R2 with data

    because the version is not incompitible

    how to do this
    Help
    me
    Thanks

  279. Hi all,
    I need to create a script to insert data from an Excel sheet(i don’t simply wont to import it i want to write a script) is there a way for that like using generate script we can directly create tables.

    regards
    aniruddha

  280. It appears that so many people want the script for automating the process of creating a script to copy the DB. Can you please post the code? If not can you please send me a copy.

    Thanks,

    Inde

  281. Hi Pinal,
    can you share with us how to automate the creation of existing SQL2005 Scheduled Jobs into T-SQL script for disaster recovery purposes please?

    Thanks.

  282. For those of you asking Pinal to show you how to preform automation, please see the CodePlex site. [ http://sqlhost.codeplex.com/wikipage?title=DPW%20Documentation&referringTitle=Database%20Publishing%20Wizard ]

    Read the Database Publishing Wizard documentation.
    There are multiple command line switches you can use:

    Command Line Syntax
    sqlpubwiz script
    (
    -C connection_string | -d local_database_name
    [ -U local_user_name ][ -P local_password ][ -S local_server_name ]
    )output_file_or_directory [ -f ]
    )
    [ -schemaonly ]
    [ -dataonly ]
    [ -targetserver server-version ]
    [ -noschemaqualify ]
    [ -nodropexisting ]
    [ -q ]
    [ -? ]

    sqlpubwiz publish
    (
    -C connection_string | -d local_database_name
    [ -U local_user_name ][ -P local_password ][ -S local_server_name ]
    )
    (
    [ -R hoster_name ]
    [ -RW remote_web_service_addr ]
    [ -RWU remote_service_user_name ]
    [ -RWP remote_service_password ]
    [ -RS remote_databse_server ]
    [ -RD remote_database ]
    )
    [ -schemaonly ]
    [ -dataonly ]
    [ -targetserver server-version ]
    [ -noschemaqualify ]
    [ -nodropexisting ]
    [ -notransaction ]
    [ -q ]

    Thanks :-)
    Scott Gleason

  283. i am just staring to work with sql 2005 server,i have to create
    sql statement for display telphone number that mean there is
    database for all telephone number but when people call us i have to
    split taht phone number like country code ,localID,PBX,Exten for example
    let say one number 0443965045 in this number 0 for country code,
    44 for localID,39650 for PBX the rest 45 for EXTEN,could you
    please help to wrtie sql for this condition and also when i get
    extension number with one digit like 1 or 2 or 3 this must be disply with 0
    that mean like 01 or 02 or 03

    could you please

    • Hi,
      i am just staring to work with sql 2005 server, for our software jobboss as backend support, where should i get the data stored in the database. how could i modify or change the data in it. please help me, thanx in advance.

  284. HI Pinal Dave,

    CUD U PLZ HELP ME ..

    I WANT TO migrate all tables from mysql to sql server. (Import all schemas to SQL server without data)..
    BUT HOW PLZ TELL ME ..

    REGARDS,
    HIMANSHU

  285. What is the best way to script schema difference and automate the execution of the script .
    for e.g. you want to release next version of your application and some modifications to the database schema. I have a copy of the old version db which I want to compare it to the new one and generate the script for the difference.

    Thanks
    Rakesh

  286. imagine i have a database “X” and database “Y”

    database X has tables “X1″ “X2″ “X3″ “X4″
    database Y has tables “Y1″ “Y2″ “Y3″ “Y4″

    i have to update table X1 with the values of Y1.

    i dont need too specific like x and y. i need a common query for doing the above mentioned method.

  287. hi

    i am using local database to my application . i want to upload daily transactions into remote database (not for total database )

    could you please tell me how to generate script of one day transactions in sqlserver 2005 and and integrate with database remote server

  288. Good Day Pinal Dave,

    Can you advice on the script for mirroring the database from MSSQL2008 as live database to MSSQL2005 as backup database?

    Thanks,
    William

  289. Dear Sir,

    I installed Microsoft SQL Server 2005 in my pc . But i can’t create a database in my system so plz help in this matter….

  290. Hi Pinal,

    please send me the same details to automate this process.
    one more thing that I’m not able to find ANSI Padding in my Scrept wizard. i am using sql server 2005

    please assist

    Thanks

    Abdul Razzaq

  291. hi, could i get the option to create script which will append data in database using sql server 2005?
    where i want to take data from one db & insert it into another(not restore)

  292. I need a query to Copy all the data from from one database to another database where the databases have same structure.
    please sennd a query ..it is urgent…