Migrating a Database
-
Hello everyone. My name is Michael.
First of all you should know that I am extremely new to SQL in general but not to programming (mostly in a language called PowerBasic, which is very similar in syntax to Microsoft flavors of Basic - but a lot better).
Anyway, it is a very real possibility that my organization will be uprooted soon and moving; and that includes the IT support we've been getting from our parent company.
Ever since it was announced that we might be moving, the big question on my mind was "what the hell are we gonna do with the database?". There is no IT person working for us (I'm it) and I have no idea if the new parent company will step in to handle the database conversion.
I need a general outline and helpful hints about migrating data from an existing database to a new one and my biggest "gotchya" is how to maintain relationships between tables/records when doing so.
The good news here is that there is very little data and very few tables to convert. In fact, the whole database is has no more than 80,000 records and literally there only about 15,000 that we need to keep in an "active" database - the rest are so obsolete (read: dead people) that I intend to archive them and NOT import them into the new database. On top of that, the data I need to migrate consists of nothing more than basic stuff: i.e. Name, address, phone, email, a little demographic info and no more than 2 financial transactions.
But what is the best way to go about exporting this data (most likely from a flat file) into a new database and repopulating the SQL database - keeping the relationships intact? (by the way, I'm using SQL Express 2008 because I'm learning and I have no idea what platform we might end up on)
I can conceptualize most everything except how to maintain the relationship between tables as you move the data..because obviously, the primary key will end up not being the same, so what is the strategy for maintaining the relationships?
I would think that a script that reads the data from each table, inserting it in order to a new, predefined table, might work but I'm clueless as to how to construct a script to do this..I'm too new.
what if I managed to reconstruct the database in Access (2003) and then link that to SQL Sever?
Then read the data (using SQL) into the new database? And how would I maintain data integrity while doing so?
I KNOW this is sort of a huge question so feel free to tell me to FO or refere me to links that might help.
Can anyone point me to some resources dealing with database conversion?
Really, any help is appreciated so whatever information is offered, I'm thankful for.
-Michael
-
SQL server has the ability to create a backup file of the database; that is ideal for disaster recovery and creating copies for developers to not fiddle with the live data.
That is the recommended way to get copies of your table structures and data; it is built in and tried and true method.; the backup contains everything related to the database, up to the point in time it was created; nothing is lost by doing it in this way.
After creating a backup, you can then RESTORE the same backup file as a different database name, or as you need to do, re-create it on another server.
As a developer, I'd hope you are somewhat familiar with SQL server Management Studio;
in that application, you want to right click on the database in question, select Tasks, and choose Backup.
sounds like your group has been lucky so far; a regular backup plan for disaster recovery should have been in place already; maybe you are just not aware of it?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell,
Thank you very much for your reply. Yes, the situation is a little odd here.
I do know that the database is backed up daily. However, I failed to mention that it is currently running on a Sybase SQL Anywhere server (version 5x).
This database is so old that I cannot even find reference to it on the internet. It's called 'CMS' (yes, that's really the name) created by BR Blackmar and Associates. It's last version update was 1994 (Jees!).
From what I've been able to surmise from prodding around in various files, it appears the underlying database uses only standard ANSI SQL. I do not have access to the actual data tables through any kind of interface except by importing them into Access. We actually have a subset of tables linked to Access for reporting purposes. The front end was written in Watcom C++ (irrelevant).
I have tried copying the the .DB file to a flash drive (it's only 130 +- MB and a single file) and although the file sizes match when done, I always get an error when I first start the copy procedure; and I apologize that I cannot remember the exact error right now but it was really odd. Something to do with some functionality being lost with Apple <something>.. This led me to believe that the server was running on a Mac OS but one of the IT people assured me it was a Windows machine. --anyway, I have not been able to find any program that can read or import the tables from the copied file - and I've tried a LOT of things (including SQL Server).
I may get more help in about 3 weeks. Part of the problem here is that I'm not allowed to ask the IT people about this because our parent company (and that's a misnomer.. we're only loosely associated) does not yet know that we are about to affiliate ourselves with someone else. After the 23rd of this month that will change.
By the way, yes I'm getting familiar with SSMS. I've been playing around with it since January when I first found out about this possible move and realized that no one but me seemed concerned about the database (we are a very small organization..only 6 people).
And who knows, our new "sponsor" is a very, shall we say, wealthy organization. They may offer to do the conversion for us. I just hope I get a say in what goes on because although our needs for this database are really pretty simple, it would be great to have a custom written solution that does exactly what we need as opposed to our data being plopped into a pre-fab'd database that's only customized "just enough" to do what we need.
But one last question. I don't expect you to answer that here; it would be too much. I've searched but have not really found a white paper on the theory of doing a conversion like this (across different servers). I'm really curious about the technique used to migrate the data and maintain the relationships between tables and individual records (tables .. not so hard but individual records and primary/foreign keys is a little more daunting). Do you know of anything you could point me to?
Sorry for the long post but thank you for listening.
-Michael.
-
ok completely different situation than i thought.
Sybase is a database engine, the same way SQL Server is; you'd want to create a linked server in SQL Server to connect to Sybase;and use that connection to migrate/copy all the data from one system to another. I believe Sybase was the precursor to SQL Server,and MS bought rights to it and improved on it from there...
that's probably why you cannot copy the file...it is in use by the Sybase engine and thus has a file lock on it.
I'm sure there is some sort of GUI or application to show you all the Sybase databases and structures, although i'm not at all familiar with it. that application may give you the ability to send data to file for import/export to other apps.
hopefully someone who has migrated Sybase to SQL can chime in.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yes, I think you're correct. I was unable to even copy the file unless my instance of the database was shut down (my computer is the "gateway", so to speak). My computer starts an instance of the database on start-up via a script. If my computer isn't on, then no one else in my group can log in to the membership database. But that does not mean the database itself is shut down, so yes, there is probably a lock on it.
There is an interface to view the tables and their structure. I found that two days ago but it requires a username/password and apparently mine is not valid (I tried). So like I said, I'm stuck until we make an official announcement about our organization moving. Not allowed to tip our hat yet.
There is just so little time, dang it.
Thank you, Lowell.
Lowell (5/3/2010)
that's probably why you cannot copy the file...it is in use by the Sybase engine and thus has a file lock on it.
I'm sure there is some sort of GUI or application to show you all the Sybase databases and structures, although i'm not at all familiar with it. that application may give you the ability to send data to file for import/export to other apps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply