locked
Using change scripts 101? RRS feed

  • Question

  • So, as will become obvious I'm no SQL Server expert... so please bear with me ;) Some of the details I'm going to recite are probably superfluous but I want to make sure I ask for the appropriate help...

    I have a project built using Entity Framework, and as such I have my local db that I used for development and the production copy, which I deployed using backup.  Now I need to both add a column to an existing table, as well as an entirely new table.  I'm OK doing this on my local instance, and running updating my EF model.  I understand theoretically that I need to generate a change script and then run it on my remote host (however that's done... in SSMS I guess), but I've never had to do that and obviously I'm terrified to not somehow overwrite my records or any of the other nightmares I've been having.

    Any kind soul willing to walk me through this, or point me in the right direction?

    Sunday, March 10, 2013 7:00 PM

Answers

  • Hi PaulBinCT,

    Just so that you have some peace of mind before performing this task, I would recommend you take a backup of your production database. This will ensure you have a database recovery plan in case any data is accidently overwritten . Please follow this link for instructions on how to take a backup - http://msdn.microsoft.com/en-us/library/ms187510.aspx

    Altering a table to add a column is as simple as writing this command ; 

    ALTER TABLE table_name
    ADD column_name datatype

    This can get a bit more involved , if the column you want to add is a NOT NULL columns. Please follow this link for instructions on how to do the same - http://www.tek-tips.com/viewthread.cfm?qid=1304391

    you would use SSMS to do these tasks.

    hope this helps


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Monday, March 11, 2013 4:31 PM
  • There isn't a "rollout the change from dev to operations" button. (Unless you are using SQL Server replication, which I don't think you are doing and which I don't think you need.)

    I think the best solution for you, is to create a script for the change. Something using the ALTER TABLE command. Sanil provided the general syntax. Something similar to:
    ALTER TABLE dbo.T1 ADD NewCol varchar(20) NULL;
    Test the script against your development database. When you are confident it works properly, run the script against the operation database. In the SSMS dialog box where you can make design changes to your table, there is a "Generate Change Script" button on the toolbar to help you create the script.

    Sanil's advice to backup the operational database before major changes is also good.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by PaulBinCT Tuesday, March 12, 2013 4:53 PM
    Tuesday, March 12, 2013 3:20 PM
    Answerer

All replies

  • HI there,

    you altered the schema. simply added a column to the table.  alter table script won't overwrite your data.

    the newly added column will fill up with null values.

    if you are not confident simply run the select statement against the table. if you able to see the data then you will be fine.

    cheers

    Kumar

    Monday, March 11, 2013 12:17 AM
  • Thanks Kumar, but never having done it I'm hoping someone can walk me through the mechanics of doing this, or point me to an appropriate tutorial. 
    Monday, March 11, 2013 1:04 AM
  • Using SSMS is a good idea. Both UI and Transact-SQL are described in Add Columns to a Table (Database Engine) http://technet.microsoft.com/en-us/library/ms190238.aspx

    You should practice on a test table to gain confidence before running it against your main table.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Naomi N Monday, March 11, 2013 4:53 PM
    Monday, March 11, 2013 4:31 PM
    Answerer
  • Hi PaulBinCT,

    Just so that you have some peace of mind before performing this task, I would recommend you take a backup of your production database. This will ensure you have a database recovery plan in case any data is accidently overwritten . Please follow this link for instructions on how to take a backup - http://msdn.microsoft.com/en-us/library/ms187510.aspx

    Altering a table to add a column is as simple as writing this command ; 

    ALTER TABLE table_name
    ADD column_name datatype

    This can get a bit more involved , if the column you want to add is a NOT NULL columns. Please follow this link for instructions on how to do the same - http://www.tek-tips.com/viewthread.cfm?qid=1304391

    you would use SSMS to do these tasks.

    hope this helps


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Monday, March 11, 2013 4:31 PM
  • Thanks for that link Rick... it's very helpful. My experience with SSMS is very limited... so please excuse what's undoubtedly a stupid question.  If I add a column (or a table, later) using the design surface in my local db in order to update my code first, how do I  "publish" (if that's the correct term) to my remote instance? 

    Thanks for all the hand holding ;)

    Monday, March 11, 2013 5:29 PM
  • I'm not entirely sure of your environment, but...

    SSMS connects to an instance of the SQL Server Database Engine. The query (or UI action) acts upon the instance that you are connected to. If you want to change the table (or insert data into the table) in two different databases, then you connect to each database separately with SSMS and perform the action twice.

    SQL Server does has a replication model with a publisher and subscriber, but I don't think that's what you are talking about. I think you have two separate and unrelated databases.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, March 11, 2013 8:53 PM
    Answerer
  • Rick... Again, thank you very much for your help on this.  I'm sorry because I'm undoubtedly not explaining myself adequately, so if at the risk of imposing I'll try to clarify.  I have two instances that have the same schema but different values.  One was used locally for development, the other is in operation.  I would add the columns, and the additional table, I need locally for testing and to update my code (I'm using EF).  Normally I do this in VS, although I gather it's preferable to do so in SSMS in this case.  I'm OK doing it, at least using the designer as my query "experience" such as it is, is mainly using LINQ.  Once I've made the changes locally, and updated and tested my code I'd like to replicate those changes (additional columns and an a new table) to my remote host.

    Once more, I'm very grateful for your help and patience!  Thank you...


    • Edited by PaulBinCT Monday, March 11, 2013 11:49 PM
    Monday, March 11, 2013 11:49 PM
  • There isn't a "rollout the change from dev to operations" button. (Unless you are using SQL Server replication, which I don't think you are doing and which I don't think you need.)

    I think the best solution for you, is to create a script for the change. Something using the ALTER TABLE command. Sanil provided the general syntax. Something similar to:
    ALTER TABLE dbo.T1 ADD NewCol varchar(20) NULL;
    Test the script against your development database. When you are confident it works properly, run the script against the operation database. In the SSMS dialog box where you can make design changes to your table, there is a "Generate Change Script" button on the toolbar to help you create the script.

    Sanil's advice to backup the operational database before major changes is also good.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by PaulBinCT Tuesday, March 12, 2013 4:53 PM
    Tuesday, March 12, 2013 3:20 PM
    Answerer
  • OK... thanks to you both, Rick and Sanil.  I'll do some reading and light a candle ;)
    Tuesday, March 12, 2013 4:52 PM