table restoration question

Question

table restoration question

joshua.aanderson

Grasshopper

Points: 15
More actions
July 24, 2009 at 7:54 am

#223886

I had a customer take tables from a test environment and import them to there live environment.
Is there an easy way to undo that without comparing the data in both tables and possibly missing something? Tricks / tips ? Thanks.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

joshua.aanderson (7/24/2009)
I had a customer take tables from a test environment and import them to there live environment.
Is there an easy way to undo that without comparing the data in both tables and possibly missing something? Tricks / tips ? Thanks.

I suppose you had some records in ur test database and then u populated it further by importing data from production. If this is the case and you can connect the production from the test, here are three ways:

1. create a linked server and delete matching records from production

delete from yourTable where id in (select id from prodServer.prodDB.dbo.myProdTable).

2. import the table again into the test database under a different name and delete matching records from this table.

delete from yourTable where id in (select id from NewlyCreatedTable)

3. If your test database is in full recovery mode and full and tran log backups happen regularly, do a point in time recovery.

I've assumed there are no duplicates in test and prod tables in first two examples above.

Pradeep Singh

lkennedy76 SSChampion Points: 13683 More actions · Answer 2

I would export them out to flat files and use ultredit or wincompare (ithink that's was it is called) find out what you don't need and delete them out. easy peasy, I have had customers do that to me before and it's just easier to me to export and remove, delete conteins of table and reimport. have fun! 🙂

MCSE SQL Server 2012\2014\2016

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Data Compare from Red Gate will help if you need to sort things out.

lkennedy76 SSChampion Points: 13683 More actions · Answer 4

It would write you a script and delete out what you do not need so yes it would be the quicker way. Just look over the script before excuting it. 🙂

MCSE SQL Server 2012\2014\2016

joshua.aanderson Grasshopper Points: 15 More actions · Answer 5

thanks for all the tips.

IT was much simpler then that.

Turns out the customer uploaded the table however (now get this and if anyone knows how this is possible let me know) in SQL mgmt there were two tables listed table1 2x's.

I deleted the one with the newest date and the web app was working back to normal.

I never saw that before. I would think that should be impossible. nice bug maybe.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

I'd guess that they were in different schemas. Was this 2000 or 2005?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

tosscrosby SSCoach Points: 18045 More actions · Answer 7

Jack Corbett (8/3/2009)
I'd guess that they were in different schemas.

Had to be if the names were identical. Don't necessarily believe I would've deleted one (maybe renamed??). Also, I don't believe I'd be doing something like this in production without testing it in a dev envionment. Iron out all the wrinkles first - but that's just me! 😉

-- You can't be late until you show up.

vannguyenthi.ggs Newbie Points: 7 More actions · Answer 8

I suppose you had some records in ur test database and then u populated it further by importing data from production. If this is the case and you can connect the production from the test, here are three ways:
1. create a linked server and delete matching records from production
delete from yourTable where id in (select id from prodServer.prodDB.dbo.myProdTable).
2. import the table again into the test database under a different name and delete matching records from this table.
delete from yourTable where id in (select id from NewlyCreatedTable)
3. If your test database is in full recovery mode and full and tran log backups happen regularly, do a point in time recovery.
I've assumed there are no duplicates in test and prod tables in first two examples above.

I'm very interested! I would love to find out more inforamtion related to this topic. Thanks in advance.

me too, I need more detailed info

comparatif simulation taux pret auto - taux pret auto differe selon la prise en compte ... calculent automatiquement le taux pour un prêt automobile donne.comparatif simulation taux pret auto

ps. One Orange Chip Points: 29422 More actions · Answer 9

vannguyenthi.ggs (8/6/2009)
I'm very interested! I would love to find out more inforamtion related to this topic. Thanks in advance.
me too, I need more detailed info

for point 1, please refer sp_addlinkedserver in Books Online.

For point 2, please refer export/import using SSIS.

Refer http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm%5B/url%5D

For point 3, please refer point in time restores.

Refer [url]http://msdn.microsoft.com/en-us/library/ms190982(SQL.90).aspx"> http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm%5B/url%5D

For point 3, please refer point in time restores.

Refer http://msdn.microsoft.com/en-us/library/ms190982(SQL.90).aspx

Pradeep Singh