Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Authors
About us
Contact us
Newsletters
Write for us
Keep up to date - daily newsletter:
Sign up
Back
SQLServerCentral
Register
Home
»
SQL Server 2005
»
Administering
»
how to migrate changes to production server
how to migrate changes to production server
Post reply
Like
377
Add to Briefcase
1
2
Next
how to migrate changes to production server
View
Options
Author
Message
y.koteswarrao-652921
y.koteswarrao-652921
Posted 9 years ago
#813414
Ten Centuries
Group: General Forum Members
Points: 1198
Visits: 199
Hi,
Iam a junior DBA, how to migrate changes made in the development environment to production environment.
Regards
Koteswarrao
53
Quote
maechismo_8514
maechismo_8514
Posted 9 years ago
#813432
SSChampion
Group: General Forum Members
Points: 11914
Visits: 2228
y.koteswarrao-652921 (11/3/2009)
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,
24
Quote
sejal p gudhka
sejal p gudhka
Posted 9 years ago
#813443
Ten Centuries
Group: General Forum Members
Points: 1398
Visits: 236
Ensure that you have taken production db backup before you migrate changes to production database.
27
Quote
homebrew01
homebrew01
Posted 9 years ago
#813520
SSC-Dedicated
Group: General Forum Members
Points: 35588
Visits: 9231
Krishna -878600 (11/3/2009)
y.koteswarrao-652921 (11/3/2009)
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,
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 .....)
24
Quote
SQLRNNR
SQLRNNR
Posted 9 years ago
#813589
SSC Guru
Group: General Forum Members
Points: 175039
Visits: 18671
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
47
Quote
Matt Cunningham
Matt Cunningham
Posted 9 years ago
#813672
SSC Veteran
Group: General Forum Members
Points: 264
Visits: 330
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.
41
Quote
Elliott Whitlow
Elliott Whitlow
Posted 9 years ago
#813733
SSC Guru
Group: General Forum Members
Points: 70622
Visits: 5314
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
37
Quote
robert.mcleod
robert.mcleod
Posted 9 years ago
#814232
Mr or Mrs. 500
Group: General Forum Members
Points: 525
Visits: 445
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.
29
Quote
oddiejbp
oddiejbp
Posted 9 years ago
#814304
SSC-Enthusiastic
Group: General Forum Members
Points: 181
Visits: 27
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
44
Quote
The_SQL_DBA
The_SQL_DBA
Posted 9 years ago
#814368
SSCertifiable
Group: General Forum Members
Points: 7048
Visits: 935
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."
45
Quote
Go
Post reply
Like
377
Add to Briefcase
1
2
Next
Post quoted reply
Permissions
You
can't
post new topics.
You
can't
post topic replies.
You
can't
post new polls.
You
can't
post replies to polls.
You
can't
edit your own topics.
You
can't
delete your own topics.
You
can't
edit other topics.
You
can't
delete other topics.
You
can't
edit your own posts.
You
can't
edit other posts.
You
can't
delete your own posts.
You
can't
delete other posts.
You
can't
post events.
You
can't
edit your own events.
You
can't
edit other events.
You
can't
delete your own events.
You
can't
delete other events.
You
can't
send private messages.
You
can't
send emails.
You
can
read topics.
You
can't
vote in polls.
You
can't
upload attachments.
You
can
download attachments.
You
can't
post HTML code.
You
can't
edit HTML code.
You
can't
post IFCode.
You
can't
post JavaScript.
You
can
post emoticons.
You
can't
post or upload images.
Select a forum
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
Narrow your search by forum
Explore
Home
Latest
Popular
Calendar
Members
Who's on
Moderators
Forums
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
SQLServerCentral
Register
Search
Narrow your search by forum
Unthreaded, ascending
Unthreaded, descending
Subscribe to topic
Print topic
RSS feed
Go to topics forum
Jump to page
Jump to page
Copyright © 2002-2018 Redgate. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.