A SQL sort of problem...

[Praetor]

Hello all,

so I have this client that deleted some data from a production database and, for some weird reason that is beyond me, continued to use that database, having now newer data on it. :wacko:

 

so now I have, from a old backup, the data that it got deleted. My question, since I'm not an SQL expert, is it possible to join this data from the backup into the production database?

 

some info:

- it's a SQL Server 2008;

- it's only four tables and it doesn't have much data on them (only a couple of hundreds of lines).

- I have a backup that has the data that got deleted and I can make a new backup of the production database just before I touch it.

- the client used a stored procedure to delete the data.

 

my biggest fear in this situation is that there are relationships between the tables and if I just insert the missing data it creates new primary keys (it is auto-incrementing), making other tables that depend on that data broken :/

 

Is there any quick solution for this mess?

[+theblazingangel MVC]

You really must gain an understanding of the data and the relationships between records if you're to fix problems like this properly.

 

Something to consider - For all we know, the unique identifiers of those old records may have been used externally to the database, for example, printed on paper documents such as invoices, or perhaps they may even be stored in records within an entirely separate database, perhaps for a system that uses more than one database. This may or may not be the case here, but you should always consider such possibilities, and it is generally best in my opinion if you can return those old records to the database with their old identifiers to ensure consistency with any such external usage  (past, present or future) in order to avoid any potential problems moving forward.

 

Assuming that you'd like to return the records with their old identifiers:

• You need to look at all fields/columns that must hold unique values. Consider whether the values for all of the new records in such fields/columns are unique from those in the old records. (As in, do any of the new records have the same ID as an old record?).
• If they are entirely unique, the way is clear for the old records to simply be re-inserted. You should be able to use SQL inserts to add the records, and when doing so you would provide the old identifier as the value for that field, rather than allowing it to auto-generate a new one.
• If any of the new records reuse unique identifiers previously held by old records, you've obviously got a problem, more so if those identifiers have been used externally at all in relation to these new records. There are several ways of proceeding - for records with no conflicts, do you reinsert with the old identifiers or use new ones; for those with conflicts, do you move the new records to new identifiers, giving them back tot he old records, do you let the new records keep them and use new ones for the old records, or do you abandon those identifiers and give new ones to both the old and new records. I cannot advise you as to which to choose, you need to assess the impact of each possible scenario, perhaps involving discussions with the owners/users of the database.

 

I imagine that the new records would only be reusing identifiers previously held by old records if the delete procedure reset the auto-incrementer. If this is not the case, then per the above, things should be pretty simple.

 

You may find it most efficient to correct the problem (assuming all is well in regard to the above) by exporting the new data, restoring the backup, and then re-issuing insert statements for the new records. I'm not sure what export options are available with MSSQL, but in the MySQL world, phpmyadmin provides one that gives a text file with SQL insert commands for re-inserting records, including whatever unique identifiers they had. Perhaps you can find something similar. Otherwise, with only a few hundred records to work with, just get stuck in and sort it out manually. Really shouldn't take that long.

[Seahorsepip Veteran]

That depends on the situation, did his new data use the auto increment id's from the old data?(If this happens or not depends on your table setup).

  On 20/07/2014 at 21:09, theblazingangel said:

You really must gain an understanding of the data and the relationships between records if you're to fix problems like this properly.

 

Something to consider - For all we know, the unique identifiers of those old records may have been used externally to the database, for example, printed on paper documents such as invoices, or perhaps they may even be stored in records within an entirely separate database, perhaps for a system that uses more than one database. This may or may not be the case here, but you should always consider such possibilities, and it is generally best in my opinion if you can return those old records to the database with their old identifiers to ensure consistency with any such external usage  (past, present or future) in order to avoid any potential problems moving forward.

 

Assuming that you'd like to return the records with their old identifiers:

• You need to look at all fields/columns that must hold unique values. Consider whether the values for all of the new records in such fields/columns are unique from those in the old records. (As in, do any of the new records have the same ID as an old record?).
• If they are entirely unique, the way is clear for the old records to simply be re-inserted. You should be able to use SQL inserts to add the records, and when doing so you would provide the old identifier as the value for that field, rather than allowing it to auto-generate a new one.
• If any of the new records reuse unique identifiers previously held by old records, you've obviously got a problem, more so if those identifiers have been used externally at all in relation to these new records. There are several ways of proceeding - for records with no conflicts, do you reinsert with the old identifiers or use new ones; for those with conflicts, do you move the new records to new identifiers, giving them back tot he old records, do you let the new records keep them and use new ones for the old records, or do you abandon those identifiers and give new ones to both the old and new records. I cannot advise you as to which to choose, you need to assess the impact of each possible scenario, perhaps involving discussions with the owners/users of the database.

 

I imagine that the new records would only be reusing identifiers previously held by old records if the delete procedure reset the auto-incrementer. If this is not the case, then per the above, things should be pretty simple.

 

You may find it most efficient to correct the problem (assuming all is well in regard to the above) by exporting the new data, restoring the backup, and then re-issuing insert statements for the new records. I'm not sure what export options are available with MSSQL, but in the MySQL world, phpmyadmin provides one that gives a text file with SQL insert commands for re-inserting records, including whatever unique identifiers they had. Perhaps you can find something similar. Otherwise, with only a few hundred records to work with, just get stuck in and sort it out manually. Really shouldn't take that long.

Exactly what you said, couldn't have said it better.

[Praetor]

  On 20/07/2014 at 21:11, Seahorsepip said:

That depends on the situation, did his new data use the auto increment id's from the old data?(If this happens or not depends on your table setup).

 

no it didn't, so my initial idea was first copy the old data into the production database, but this way is more quick (restore the old database and just insert the new data). The problem is that nobody seems to know the complete ambit on how is that data processed, not even the current programmer :/ it's a mess, really.

 

Gonna try that and let's see if it works out.

thanks guys!