Post reply

Upgrading Production SQL 2005 Express Database

  • September 28, 2008 at 6:23 pm

    #382876

    I hope that this is a simple "newby" question!

    I have made significant changes to an SQL Express Database here in the lab which includes many changes to tables and stored procedures (along with this I have made VB application changes also).

    Now it's time to update the production database with the new table structures and stored procedures.

    I would greatly appreciate it if there was a simple way to create an empty database with the new stored procedures (actually all the stored procedures new and existing) and copy the data from the existing database into the new database. Columns that didn't exist before can be loaded with NULL which is fine!

    Any pointers on where to look for help will be GREATLY appreciated! Any scripts on this site?

    Thanks,

    Fred

  • September 29, 2008 at 12:35 am

    #878266

    Hello,

    Which version of SQL Express do you have? Does it include the "Database Copy Wizard"?

    According to the following article, it is definitely included in SP2: http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • September 29, 2008 at 9:46 am

    #878472

    Hi John:

    Thanks for your help! I forgot to mention that both databases (production and development) are SQL 2005.

    I will install Service Pack 2 on another machine today to see if the Database Copy Wizard is available for SQL 2005 to SQL 2005 migrations.

    Will this help me migrate the production database. The current version (on the production machine) does not include the new tables, relationships, and stored procedures that I have created on my development system.

    I can enter the changes manually - hopefully this will allow me to copy my existing tables into the new database.

    Please let me know if I'm on the right track!

    Thanks very much!

    Fred

  • September 29, 2008 at 11:48 pm

    #878683

    Hello Fred,

    Sorry, I didn’t quite understand what you needed first time. If you want to synchronise the schema between your Dev and Production DBs then a tool like Redgate's SQL Compare would be very useful. It compares two DBs, tells you the differences and then there is a wizard available to build a script to update the target DB with the schema changes. You can even save the script for future use. The tool isn’t free though.

    There is also DB Comparison functionality in some of the higher end versions of Visual Studio, if you have that already?

    You could also consider using the DB Copy Wizard to copy your Dev DB (as the new Production DB), then truncate all of the tables and use the Import Data Wizard to load the data from the old Production DB.

    The other alternative is the much more manual process of scripting out the new and updated objects from Dev and running them on Production. That could be a lot of painstaking work, so if you can get the budget it would be worthwhile buying Redgate's SQL Compare, or something similar.

    Regards,

    John

    www.sql.lu
    SQL Server Luxembourg User Group

  • September 30, 2008 at 9:47 am

    #878952

    John:

    Thanks very much for your very complete answer - wow!

    Fortunately I can take the database off-line over the weekend.

    So: I will bring a backup copy of the live database to my machine and script all the Stored Procedures and the creation of new tables from my development database.

    For the existing tables I will re-enter the structure changes and relationships.

    I'll test everything and if all is OK I will create the database on the production machine from a backup of my development machine.

    For the future the info you provided will be very helpful!

    Thanks again,

    Fred

  • September 30, 2008 at 9:59 am

    #878961

    Hey John:

    Just got a chance to check out the SQL utilities you described. I have downloaded a copy of the xSQL Bundle to see if it will help me.

    I'm sure it will - at the minimum it will tell me if I have my databases correctly matched.

    I will post a response when I'm completed - 1-2 weeks!

    Thanks again,

    Fred

  • September 30, 2008 at 2:15 pm

    #879155

    John:

    After some poking around, the xSQL "Bundle" (which is 100% free for the lite version) did everything I needed.

    http://www.xsqlsoftware.com/

    I made a copy of the "new" and "existing" database and the utility generated the scripts to upgrade the "existing" database to the new schema.

    New tables were added, existing tables were modified, new stored procedures were added, existing stored procedures were modified.

    It's FANTASTIC!

    This seems too good to be true so I'm going to play with this some more!

    Fred

  • September 30, 2008 at 11:47 pm

    #879319

    Hello Fred,

    Thanks for the feedback.

    I'm fortunate at work as the company pays for a license of the full Redgate Tool Kit, but following your tip, I'm going to download the Lite version of xSQL Bundle for home use.

    Regards,

    John

    www.sql.lu
    SQL Server Luxembourg User Group

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply