January 1, 2004 at 10:31 am
Hello,
A developer and I are in the process of migrating legacy data to a new 2000 DB. The migration was run on a Staging DB. Now I have to promote the Staging DB to the Production DB.
I have tried three methods and they all seem to work. I just wanted to check with some experienced DBA's on sqlservercentral.com to see what pitfalls may avoid by using one of the three methods over another.
Here are the three methods I have tried:
1. Using DTS, copy all objects from the Staging DB to the Production DB.
2. Take a backup of the Staging DB and restore it using a force on to the Production DB.
3. Detach the Staging DB, rename the .mdf and .ldf to the Production DB name and Attach it.
The application which uses this DB is written in ASP using VBScript Server Side, JavaScript Client/Browser Side, and of course HTML. All connections are done using ADO via an ODBC. The application works with all three methods of promotion.
Can anyone suggest one method over the other? Or maybe a different approach.
Thanks in advance for your time,
Steve
Nashua, NH
January 2, 2004 at 4:03 am
hi sdidomenico,
all of your options will work, but you have to be aware of some caveats !, i.e if you use your option 1) DTS, then your tables, data, logins, users etc can be created by DTS, however DTS will not move your DTS packages, scheduled jobs , link servers & some other objects.
if you use your option 2) backup/restore, again you will not migrate your DTS packages ( as they exist in the msdb db ), jobs, link servers etc
if you use your option 3) detach/attach the same as for 1 & 2, no DTS packages, jobs etc will be moved either - also watch out for correctly renaming the physical files (.mdf & .ldf ) but remember that the logical filenames might need to be amended as well ! - use ALTER DATABASE name MODIFY FILE(NAME=oldname, NEWNAME=newname ).
The method we use is to script the Db ( as we use a design/modelling tool to design & baseline our Db's ), implement the script to build the Db, including logins, users, link servers, stored procs, views etc, then either with DTS move the data or use 'bcp to unload to flat files & reload or even use temp tables to unload to/load from.
So there's many different ways & I'm not sure if any one is the 'correct/preferred' way, all you have to remember is that using some methods means that not all objects will be migrated & some extra manual intervention is required.
Hope that helps !!
Regards.
j
January 2, 2004 at 9:55 am
Tend to agree with the above recommendation. Backup and restore works fine for me as a one time shot, but for continual movement of changes, I like to have a process that works.
http://qa.sqlservercentral.com/columnists/sjones/vcspart3.asp
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 4, 2004 at 9:36 pm
You could use any of the methods you suggested. They will all work. I find it easiest to backup/restore when I move stuff from Dev to Test to Prod, but that's just me. I have also scripted the databases, but it was a bit of a hassle. Guess it depends how big your DB is. Do whatever method you feel most comfortable with.
Cheers,
Angela
January 6, 2004 at 12:32 pm
Thanks so much for all of the input.
As a newly appointed (inexperienced) DBA, I find great comfort knowing that there are experienced DBA’s on SQLServerCentral.com willing to help.
One thing I forgot to mention was that we have only one instance of SQL Server 2000 running. Therefore the databases reside within the same SQL Server 2000 RDBMS. This being the case, I believe the “DTS packages, scheduled jobs, link servers & some other objects”
are all okay, as they did not have to move. To be honest I don’t know what “link server and & some other objects” are.
Here is what I did. First I created a new Production Database. Then the method I used to promote from Staging to Production was a DTS Import on the Production Database. Using the Wizard on the Specify Table Copy or Query panel, I selected “Copy objects and data between SQL Server Databases.” Next, on the Select Objects to Copy panel, I took all of the defaults. To finish I ran the DTS Package.
All worked fine and the ASP application is working great.
Thanks again.
January 9, 2004 at 3:00 am
Hi,
It's much easier when your migrating to the same server/instance of Sql as the 'link server & some other objects' already exist, & by some other objects I was referring to logins, roles, server jobs etc.
Pay attention though if your prod db is on the same server as the one your promoted it from as it's incredibly easy & occurs often when under stress, for people to access the wrong db ( after all its internal structure [ i.e table names, column names ] is the same ), i've seen it happen many times !!.
Regards
j
January 23, 2004 at 8:30 am
Hi jima,
Thanks for defining "some other objects" as I was hoping you were referring to logins, roles, server jobs etc.
Also, thanks for the reminder, as I do understand the need to pay close attention when the Dev, the QA, and the Prod Databases are all running on the same SQL Server Instance.
Steve DiDomenico
Nashua, NH
August 30, 2004 at 11:46 am
The problem I've had using DTS is that it doesn't keep things like identity columns and foriegn keys configured... Maybe I didn't use the right options.
-J
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply