Solved

old database records reappearing after import to new database

Posted on 2007-11-21
15
260 Views
Last Modified: 2010-04-21
I'm trying to migrate our website, including a SQL Server database, from our current webhost to a new webhost. The database was developed in SQLServer2000. My new webhost has SQLServer2005. Because it's the only way I can get to work, what I'm doing is exporting the entire database out of my old host onto my PC. Then I import the entire database from my PC onto my new hosts database server.

What I'm finding is that old records, or old versions of records, are reappearing in addition to what I think should be there. For instance, I have a "user" table that my old host shows as having about 8900 records and that appears to be correct to me. When i export this table to my PC, I check again at it still shows the same amount of records - 8900. When i then import this table up to my new host, i suddenly have about 33000 records for that exact table.

Looking at the data i see what appears like multiple copies of what should be single records. What's worse, I do have an indentity column on this table that assigns unique user ids yet so i'm seeing multiple records that show the same userid. it looks almost like old versions of records that I've modified are showing up... for instance, i see one record where I know i changed the status code of one of our internal users that quit last month and i see both the "current" version of that record with the correct code but also the "old" version of that record with the old code. I changed that code directly in Enterprise Manager.

Any ideas on what the heck is going on? Thanks!
0
Comment
Question by:jm14638
  • 4
  • 4
  • 3
  • +2
15 Comments
 
LVL 51

Expert Comment

by:tedbilly
ID: 20330932
So you aren't using SQL backup/restore to copy the database?
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20330967
mmmmm maybe it is appending....
0
 

Author Comment

by:jm14638
ID: 20330991
no... with my current host, when i try to 'backup' the database, it doesn't let me designate my PC as the destination - the destination options are all on the hosts servers. i've tried several times to figure out a way around that with my current host's "support" team but outside of offereing to burn a copy of my database to DVD and send it to me for $50, they've not offered any help. compound that with me trying to leave the comfort of enterprise manager and transition to SQLServer Management Studio developer edition and no - i'm not able to use that method. I'm sure it would work exponentially better were i able to.
0
 

Author Comment

by:jm14638
ID: 20331026
that's what it looks like sort of... but on the "new" host, this is a brand new database that hasn't had any of my tables on it before.

i thought that it might be appending during my middle step, when i copied the database to my PC, but looking at the data on my PC, i just don't see the duplicate records there... i've tried looking not by looking at the raw table data nor when I do a select statement.

i thought that during the import i even saw the progress log showing where it was dropping the current tables before adding them back new... is it just a matter of deleting every table at the destination everytime before I import?
0
 
LVL 35

Accepted Solution

by:
David Todd earned 240 total points
ID: 20331176
Hi,

to backup to from WebhostServerA to your pc requires you to map a disk from your PC - use xp_cmdshell with the net use command.

Otherwise, back up the server to itself, then copy that to your PC, then copy that to WebHostServerB, and restore it.

Or take the DVD although $50 seems a bit steep - but think of the effort that that will save, and the time to transfer over the network(s).

I'd suggest that you are importing into an old database with an append, rather than a truncate/insert. You need to look into your import procedure.

HTH
  David
0
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 20 total points
ID: 20331259
very strange... could you run a asp script to download all as csv
0
 
LVL 51

Assisted Solution

by:tedbilly
tedbilly earned 240 total points
ID: 20331502
You know it almost seems like the export is including records from the transaction log.  That is why I asked about the Backup/Restore.

Have you thought of using a 3rd party tool like SQL Delta from www.sqldelta.com  It can copy schema's and data.  It's a useful tool for development anyway so it might be worth it.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20340711
Do a backup/restore...If your db is smal (say less than 50Go) my advice to you is to not waste time on exports and crappy third party tool...

Hope this helps...
0
 
LVL 51

Expert Comment

by:tedbilly
ID: 20340771
Racimo: I'm considering reporting your comment to the administrators.  If you have experience with a 3rd party product and don't like it's features that is fair.  But to simply brush it off with a rude comment is not respectful nor professional.

Everybody I have ever shown that product too in person has ended up buying it and I have no affiliation with the company.

The author has already mentioned that a hosting provider is preventing direct access to the database and cannot use backup/restore without paying $50 to get the backup file burned to DVD and delivered.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20340917
could you run a asp script to download all as csv or a compact scrt it is what i do with access online, so should be able to design soething for sql
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20340950
<<Racimo: I'm considering reporting your comment to the administrators.  If you have experience with a 3rd party product and don't like it's features that is fair.  But to simply brush it off with a rude comment is not respectful nor professional.>>
I guess I should have phrased in other terms.  Apologies if that sounded offended.

I was suggesting third-party connectors are a waste of time (configure the tools) and money(license).  .   Being *professional* is also learning to accept contradictory comments, especially if these comments are directed at products not to persons.

<<The author has already mentioned that a hosting provider is preventing direct access to the database and cannot use backup/restore without paying $50 to get the backup file burned to DVD and delivered.>>
Come to think about it...The questionner's should see if spending the time to code a script or buying a third party license worthes 50$  If the answer is YES, the questionner's should buy the CD/DVD

Regards...
0
 
LVL 51

Expert Comment

by:tedbilly
ID: 20341210
Racimo: I think the reason I was concerned regarding your comment is that it wasn't constructive criticism.  Ultimately there are developers like us that worked hard on those products and it's not fair or respectful to them to make comments without being fair.

I'm assuming the author will be doing this more than once to do development work offline with production data.  So a long term solution is required.

I've been managing web development teams for years and I won't let developers touch production databases directly.  What we do is develop locally then setup a staging environment were we do user acceptance testing and practice deployments.  When deploying updates to the schema we use tools like SQL Delta to prepare scripts for the deployments.  Red Gate software has an excellent comparable product.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20342277
<<Ultimately there are developers like us that worked hard on those products and it's not fair or respectful to them to make comments without being fair.>>
Years ago, I relied on these tools as well (SQL Delta, SQL Compare) to help me manage multiple instances in administration and found out strong liimitations:

> When the schemas/backup strategies you compare/deploy are striped onto several specific filegroups/partitions that need to be kept up-to-date, the tools sometime generate dangerous scripts.
> The tools rarely take into account that specific schema ownership chains are in place.  As a consequence, lots of schema info are simply skipped especially when they connect with mimited privileges.
> The tools skip vital informations when limited access is given to systems databases: jobs, jobs steps and assemblies.
> As the number of instances to be managed increases,  the number of regressions increase as well.  

At some point in time, a dba (ex developper) has to take action to protect the system.  That is called version consolidation. For 80% non sensitive databases, I think tools can be useful but for 20% of the db's, I just can not rely on these tools opting for a much safer truncate/backup/restore procedure.

<<I'm assuming the author will be doing this more than once to do development work offline with production data.  So a long term solution is required.>>
Quite frankly, I do not think a technical solution can truly be efficient into this context.  The primary project stakeholder needs to make sure the appropriate/sufficient permissions/FTP accesses are in place to allow the developper to do his/her work.  In this case it is up to the stakeholder to get the CD/DVD to the developper or to set up an FTP account to allow him to get the backup.  

<<I've been managing web development teams for years and I won't let developers touch production databases directly.>>
Quite respectfully, you should not have to make such decisions (and hard work ;)).  I  work with developpers every day and once a security strategy is in put in place *once*, the schemas are 100% safe.

Regards...
0
 

Author Comment

by:jm14638
ID: 20343071
Hello All. I've gotten thru the initial problem... I ended up deleting all the tables from both my PC and the "new" host and doing the export/import again twice - it worked the second time. Still don't understand why it happened in the first place.

So at this point this has become an educational exercise and hopefully the advice posted will help me and others as some point in the future. I am a one-man IT shop for a small business and my budget is usually a couple thousand per year for all of the IT expenditures (from web development down to buying toner cartridges) so I frequently have to "roll my own" solutions versus buying software.

Part of the reason I chose the export/import, versus a programmatic solution to put the data in a CSV or XML, was so that I would not have to recreate the tables and table structure on the new host manually. If there is a way to carry the table structure over, outside of a backup/restore which I can't do using my current host and outside the export/import which I do today, then I'd be very interested in knowing how.

Thanks to everyone for their input. I'll leave this thread open for another day or two to see if anyone else has other ideas and, barring a specific solution I'll try to split the points up fairly among several of you in appreciation of your insight.
0
 

Author Closing Comment

by:jm14638
ID: 31414083
please know that i did value your input and that the B grade is purely based on the grading tips listed on EE.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now