December 30, 2008 at 10:11 am
We are in the mist of moving a 3rd party vendor's SQL 2k database to a new server. According to the vendor it's our responsibility to move their database and re-install their software because they are against the move. They instructed us to do a complete rebuild and not do a backup and restore of their database on the new server. So we have to figure out how to move our data from the existing server to the new one.
One option we thought of was DTS but there are about 500 tables with referential integrity constraints all over the place. Another option is to restore a backup with "force restore over existing database" but wouldn't that be then the same as if we just did a restore on another server? it's going to overwrite all the empty tables. The last thought is setting up a replication between both servers.
Any thoughts would be gratefully appreciated.
December 30, 2008 at 10:43 am
John Kandrovy (12/30/2008)
We are in the mist of moving a 3rd party vendor's SQL 2k database to a new server. According to the vendor it's our responsibility to move their database and re-install their software because they are against the move. They instructed us to do a complete rebuild and not do a backup and restore of their database on the new server. So we have to figure out how to move our data from the existing server to the new one.One option we thought of was DTS but there are about 500 tables with referential integrity constraints all over the place. Another option is to restore a backup with "force restore over existing database" but wouldn't that be then the same as if we just did a restore on another server? it's going to overwrite all the empty tables. The last thought is setting up a replication between both servers.
Any thoughts would be gratefully appreciated.
Why cant you challenge this thought of NOT restoring from a backup? what's issue in that?
December 30, 2008 at 10:49 am
The easiest ways are to either use restore from backup to the new server,
or dettach the database and attach it at the new server
If you are unable to do this, then have a look at SQL compare from Red-Gate as this will help you move the structure across and will keep and the constraints.
December 30, 2008 at 10:51 am
ps (12/30/2008)
John Kandrovy (12/30/2008)
We are in the mist of moving a 3rd party vendor's SQL 2k database to a new server. According to the vendor it's our responsibility to move their database and re-install their software because they are against the move. They instructed us to do a complete rebuild and not do a backup and restore of their database on the new server. So we have to figure out how to move our data from the existing server to the new one.One option we thought of was DTS but there are about 500 tables with referential integrity constraints all over the place. Another option is to restore a backup with "force restore over existing database" but wouldn't that be then the same as if we just did a restore on another server? it's going to overwrite all the empty tables. The last thought is setting up a replication between both servers.
Any thoughts would be gratefully appreciated.
Why cant you challenge this thought of NOT restoring from a backup? what's issue in that?
Agreed. Assuming this is a test environment - I'd have to think this would be the way to go. As in - run the install (which may create the new blank DB), then restore the database on top of the shell that just got created.
At worse - it won't work right while you test, in which case then I go looking for plan B (how to move the data).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 30, 2008 at 10:53 am
Before I suggested any other way to do this I really would want to know why backup & restore is not sufficient.
We really cannot tell you what else would work if we do not know why that does not work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 10:55 am
RBarryYoung (12/30/2008)
Before I suggested any other way to do this I really would want to know why backup & restore is not sufficient.We really cannot tell you what else would work if we do not know why that does not work.
Agreed - if it doesn't work, then that starts begging the question of how exactly would you recover from a disaster......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 30, 2008 at 11:07 am
Right, which leads to my strategy for dealing with block-headed vendors like this:
Question 1: What is your supported strategy for Disaster Recovery, that we should be implementing?
Question 2: Why can't we use that same strategy to migrate?
That usually brings them back in line.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 11:12 am
RBarryYoung (12/30/2008)
Right, which leads to my strategy for dealing with block-headed vendors like this:Question 1: What is your supported strategy for Disaster Recovery, that we should be implementing?
Question 2: Why can't we use that same strategy to migrate?
That usually brings them back in line.
(I can't help myself)
Barry - I find you lack of faith in (other) vendors, disturbing....:)
(actually - not - to the contrary)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 30, 2008 at 11:16 am
RBarryYoung (12/30/2008)
Right, which leads to my strategy for dealing with block-headed vendors like this:Question 1: What is your supported strategy for Disaster Recovery, that we should be implementing?
Question 2: Why can't we use that same strategy to migrate?
That usually brings them back in line.
Since the vendors opposed the most basic DR mechanism - Backup-Restore, i guess they were expecting users to maintain 500 excel sheets to duplicate data. Do two entries, one via application, other in excel sheet. so that in case of disaster you import those sheets :crazy:
December 30, 2008 at 11:33 am
It was highly recommended from the vendor to reinstall from scratch because they have 10 different filegroups splitting all the tables and their 1000 indexes. So if we did a detach move attach we might lose data or the filegroups wouldn't be correctly created. We've been looking into sql compare as an option.
December 30, 2008 at 11:36 am
Matt Miller (12/30/2008)
(I can't help myself)Barry - I find you lack of faith in (other) vendors, disturbing....:)
(actually - not - to the contrary)
Heh. Yeah, that "Crushing Their Trachea With The Force" trick never works over the phone. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 11:37 am
John Kandrovy (12/30/2008)
It was highly recommended from the vendor to reinstall from scratch because they have 10 different filegroups splitting all the tables and their 1000 indexes. So if we did a detach move attach we might lose data or the filegroups wouldn't be correctly created. We've been looking into sql compare as an option.
Database backup should be able to handle this just fine though.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 12:01 pm
So if we use "Restore database with Force restore over existing database" then we should be all good? If we did a straight restore, I believe the physical paths will not be the same.
December 30, 2008 at 12:24 pm
I call shenanigans on the vendor.
Doesn't matter where the files or filegroups exist on the server.
A backup/restore is totally valid.
December 30, 2008 at 12:25 pm
Yes. And with that many Filegroups, you should check the file assignments & locations carefully when you do the restore.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply