asked
How to refresh a table alone
Hello All, Good day to all, I have a database for around 80 GB in production. As per the request I refreshed that database to UAT environment. After a week later,user is raising a request to refresh a table alone from production DB to UAT DB. The table has around more than 20 Millions records. Instead of refreshing the whole database,I want to know is there any possibility to take that table alone from the Production database and refresh in UAT Database. Note:- I tried to take that table through generate script, But i could not get that. Please need your suggestion. Thanks in advance.
As @Cyborg pointed out, there are many ways to do this. At this point, my questions would be... 1. How many of those 20 million rows are new, updated, or deleted each week? 2. What KIND of table is it? Is it an "audit" table? Is it something like the classic Invoice/Invoice detail table? The reason for my questions is because there are some serious side benefits to some methods that haven't been posted yet.
Its possible with Redgate backup, where you can do an object level recovery from the sqb backup file. Other ways with out redgate is using SSIS, BCP or BULK INSERT, where you can import\export particular table but this way you are adding load on your production server.
You can look to a comparison tool, such as Red Gate [SQL Data Compare][1], which will let you identify the differences between two tables in order to move the data across. DISCLAIMER: I work for Red Gate [1]:
http://www.red-gate.com/products/sql-development/sql-data-compare/
If there are foreign key relationships, (the data in production would have changed) then it could get quite complicated which is why Cyborg's suggestion of getting it from the original backup is a good one. The data will be the original data. If you are determined to avoid getting Redgate SQL Backup, I describe how to do this sort of thing with PowerShell, but you might end up having to disable referential integrity checks for the UAT data [Database Deployment: The Bits - Getting Data In][1] [Database Deployment: The Bits - Copying Data Out][2] [1]:
https://www.simple-talk.com/sql/t-sql-programming/database-deployment-the-bits---getting-data-in/ [2]:
https://www.simple-talk.com/sql/t-sql-programming/database-deployment-the-bits---copying-data-out/
Hi there, Another option is [xSQL Data Compare][1]. You can very well use it to compare and synchronize data between tables and in your case you can specifically select the tables you want to compare / synchronize. Also, if in the future you need to perform this synchronization again, to spare yourself of performing a full comparison / synchronization, you can even apply additional filters to synchronize only part of the table. DISCLAIMER: I'm affiliated with xSQL. [1]:
http://www.xsql.com/products/sql_server_data_compare/?utm_source=pragmatic&utm_medium=articles&utm_campaign=xsql
19 People are following this question.
