Make changes to a production database!

debian mojo

Hello faculties,
We've a production database that is being used by one of our clients
as a backend for his website. The database size is around 1GB.

Recently we added some tables to our local database which is a
replica of the production database. Now we need to apply the same
changes at the production one aslo.

I've no clue about what steps should i implement.

Please guide me!

Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***

Jul 23 '05 #1

Subscribe Reply

1543

Simon Hayes

The ideal solution is that you take the scripts which you used to make
the changes in test from your source control system and simply run them
against the production database. For a large number of .sql files (or a
large number of target servers/databases), this can be automated using
osql.exe in batch files, or perhaps a script in your preferred
scripting language.

However, if you made the changes in some non-repeatable way (such as
using Enterprise Manager), then you would need another solution. One
possibility is to use a database compare tool of some sort to generate
a script for you, but you might still need to make other site-specific
changes such as updating data or creating new logins. So whatever you
do for now, it would be a good idea to plan on working with scripts in
a source control system in the future.

Simon

Jul 23 '05 #2

David Portas

Build a TSQL script to perform the upgrade and then test it out.

If you didn't save your change scripts when you did your own upgrade
then you'll have to re-create them (and remember to save the scripts
next time!). Enterprise Manager can help with this (right-click the
database, select All Tasks > Generate Script).

To script the data you can use this proc:
http://vyaskn.tripod.com/code.htm#inserts

or, if the data is particularly large you may find it easier to dump it
to a file and use BCP to perform the load.

It may help to drop or disable some constraints and indexes for the
duration of the upgrade - that's assuming the relevant tables aren't in
use at the time.

Also, you may want to check out some of the diff and synchronization
utlilities, such as:
http://www.red-gate.com/sql/summary.htm

Hope this helps.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #3

Erland Sommarskog

debian mojo (de*********@ya hoo.com) writes:

Hello faculties,
We've a production database that is being used by one of our clients
as a backend for his website. The database size is around 1GB.

Recently we added some tables to our local database which is a
replica of the production database. Now we need to apply the same
changes at the production one aslo.

I've no clue about what steps should i implement.

In addition to what Simon and David said, I just like to underscore
that whatever you do, first run your upgrade scripts in a test environment.
Don't deploy until production, until everything is working OK in test.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #4

Similar topics

1396

Using DTS to move data to a new table with some datatype changes

by: tdmailbox | last post by:

I have a production application that I am building some upgrades using a second (empty) copy the database. A few of the upgrades included changing the datatypes of a few fields from varchar to int(all the values in this column are already numbers) I am not trying to move the data from the production database into the blank database using DTS and the fields that had their datatypes changed are getting dropped by dts. Is there anyway...

Microsoft SQL Server

4300

UPDATE/INSERT to make One-to-Many table become One-to-One

by: serge | last post by:

I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to salvage the records from the many table and without going into detail, one of the reasons I can't do the opposite as there are records in the ONE table that I need to keep even if they don't have any child records in the MANY table. Below I created...

Microsoft SQL Server

2214

How do you manage changes from development server to production server?

by: zacware | last post by:

Hi! What started out as a simple test of mysql has turned into what is quickly becoming a replacement for our entire business system and web site The bad news is that we are now wrestling with how to we continue working on development the system without risking down time for users. If we work on a development server, how can we log the changes to table structure and then apply them to the live database on the production

MySQL Database

1288

not able to make changes in table

by: anuradha.k.r | last post by:

HI, I have created a database in MS access97.Also i have a program which would access the database and get me the required record and data.Later I upgraded to MS access 2000.Now I have two problems. 1.I am able to access the database through my program after including the version details (Afxgetstatemodule which I found from groups only).But I am not able to open the database to make changes.When I open the table I get warning that I...

Microsoft Access / VBA

3441

Novice question: how to make changes in an Access database

by: ronwer | last post by:

Hello, The title doesn't completely cover the question I have, but it's a bit more complicated problem we have. We are using a database, based on Acces, but developed by a third party developer who sells it under his own name. So, functionally it's a very specific database for a specific purpose. Now we want to make certain changes in this database, but for reasons of

Microsoft Access / VBA

2048

Need to make database read only - not executable on win2000 server

by: samuelgreene | last post by:

Hello, I have a front-end access db on a server share - the users should COPY the db to their desktop - but we know how that goes. I've tried to make it read only and have denied execute permissions on the directory but it's still runs off the server - i just get the read only message in access. Does anyone have a solution? Why am I able to execute the db? Is a temporary copy being created on the desktop computer? I've noticed

Microsoft Access / VBA

2623

Temporarily Make Web Site Completely Inaccessible

by: Jeremy S. | last post by:

Sorry if this is too far OT - I posted this in the IIS group and got no response - so here goes: What would be a good or recommended way to make a Web site in IIS6 inaccessible to users on the Internet. My situation is that I will periodically update a ASP.NET Web application. While I am making the update (which will take only a couple of minutes, max) and subsequently checking that everything works as planned after the update (which may...

ASP.NET

1809

Tracking structural changes from psql

by: Mike McGavin | last post by:

Hi everyone. I'm searching for a quick and dirty way to have psql record the SQL statements that I enter, especially those related to the database structure. My main motivation is to help keep what will probably be a production server up-to-date with my development server. I've thought a little about replication, but the current options for that seem like overkill for the relatively small database that I have. I'm particularly

PostgreSQL Database

2905

Migrating Database changes to Production

by: RichardLamont | last post by:

We often have to migrate changes to sql server 2000 databases from development to production. Normally we dump the sql from Enterprise Manager for production and development and do a diff (using CSDiff - downloadable for free). From the diff information we create some scripts to add new tables or alter existing tables as required. This is time consuming and error-prone. I am now trying the following: 1. Backup up the Development...

Microsoft SQL Server

9454

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10260

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10038

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9910

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7460

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5354

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5482

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4007

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3609

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP