Hi,

Iam a junior DBA, how to migrate changes made in the development environment to production environment.

Regards
Koteswarrao

You can use Backup-Restore method which is the easier one for not VLDB though. There are other methods too solely depending on your environment.

Before you migrate anything over to Production you need to get proper authorization from your management.

Thanks,

Ensure that you have taken production db backup before you migrate changes to production database.

Backup & Restore is NOT usually used to push changes to production. You will wipe out your production database.

In most cases, some stored procedures and tables need to get pushed individually, not replacing the ENTIRE production database with the development database (All tables, SPs, permissions .....)

Continuing with Homebrews comments - A backup and restore is NOT the way to go, unless the data is not important.


To roll out changes, you need to have change management in place, know exactly what changes need to be rolled, and typically a form of script is involved that can be kept in source safe.

There are also third party tools that can help roll schema or data changes (data in static tables like lookup data), however they typically generate a script in the background that the tool executes.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

x3 for not using the backup/restore method...

Generally speaking changes are migrated to production databases by duplicating the steps in prod that were made in dev. The changes should be documented ( VSS or the like ) with scripts to duplicate the changes that will be run on the prod system.

With that in mind there are MANY different ways to migrate, copy or replicate changes from one system to another and it will depend on what change control processes are in place and or what tools you are familiar with using to actually deploy the changes.

Since we have both Oracle and SQL servers in our env, we use TOAD. The SQL version was free a while back and has options for comparing schemas and scripting, but like I said, whatever you are familiar with.

If there isn’t a formal change control process in place, use this opportunity to create one. Even if the actual change is a manual process of recreating all of the changes from dev to prod, define a process or you’ll be plagued with drive by requests and end up trying to explain to management how a change was implemented that broke the system.

_______________________________________________________________________
Work smarter not harder.

x4 for not using the backup/restore method...

I agree there are lots of methods, but the ones presented above are the best (my experience).

You should know EVERY object change that is going into production, and if possible scripts built before you try to go, and depending on your development model maybe the scripts used to go from Dev to test, we always used to try and make sure that the scripts could be used throughout the process. But that was tied to our build methodology. Build a process to control the deployments so that you know, what, where, and when. What you are moving, where you are moving it to including any special instructions or inter-dependencies, and when you should start. This also helps you articulate the process and get buy-in.

If you are making minor sproc only changes and not a lot of those in one shot, I don't always do a backup before I do it. But if I didn't have the version that is on there in change control I would, everytime. For structure changes, do a backup of every affected database.

I also cannot stress change control enough. I used to dislike change control because I didn't like dealing with the limitations it imposed. But later it was handy to prevent changes that somebody wanted NOW. It allowed me to punt the request, basically saying if you can explain to management WHY you need it right this second then after you fill out the required paperwork I can move it, and you have tested it right? This threw the brakes on most requests because throwing an unscheduled change on the server could cost you your job. On occasion the change would really be necessary and the could get the approvals..

Also, do you have a source control system, SourceSafe, TFS, Vault, SVN, ClearCase(shudder in terror), if not look here:
http://en.wikipedia.org/wiki/List_of_revision_control_software
Lots of open source..

CEWII

At a high level my approach to promoting changes are as follows:

Planning:
Identify and notify stake holders of upcoming change and secure approval. If the change is coming as a request then there has to be sufficient documentation to validate the need and scope of the change.

Document what database objects are affected by the change and any possible outages (need for server reboots etc..)

Document your implementation plan. Be sure to give yourself step by step instructions. This will be a great help should you need to roll back your change. A common implementation plan for me would look something like this.
- Backup database(s) to (drive\path)
- Script out objects to be effected to file (drive\path)
- Run tsql script (script name).
- Run test query to validate change.
- Notify stake holders that change is complete

Document your roll back plan. What will you do should the change fail? How will you restore the system to it's previous state? If you have a good implementation plan then roll back should be pretty easy to plan for.

Implementation:
The most important thing about implementation is to follow your plan, just as you wrote it. If you go off-script in production you are only going to hurt yourself. If a problem with your change creeps up a week later, you'll be glad you stuck to your plan. Trust me, you will not remember what you did last week unless you document it.

Set your window and stick to it. Normally a change in production is going to be done outside of normal business hours so as to minimize impact to users. Set a start and finish time for your change as well as a back-out time. There is no point struggling all night to put in a change that is not working. It's better to roll back and revisit at another time when you are rested and thinking clearly.

Post-Change
I will typically log in to a system the day after a change and make sure that everything is OK. If it is something that won't be seen until a certain time like a new scheduled job or batch run, then I will check on it after the job is scheduled to run. It's better if you spot a problem then if you wait until your users see it.

The most important piece of change management is document, document, document. You'll be glad you did.

Our process after green flag is waved is to backup production database.

Then compare Dev DB vs Production DB using a third party tool (SQL Effects Clarity Community Edition in our case).

We also script views, stored procedures, functions, tables, etc to .sql files using sql server management Studio options (Generate Scripts). The scripts go to a SVN repository in folder next to the application code. If it is the case, we separate db scripts from trunk versions and branch versions.

Hope this idea helps.

Regards

1.Depending on the company Policy, it is better to have an approval from the management for any such change.

2. Always have a Rollback plan handy.

3. Backup and restore works good, but may NOT be the best solution on a production sever esp running 24 x 7

4. For data migration we have DTS or SSIS depending on what version of SQL Server you are using.

Thanks!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."