question

Mohamed4053 avatar image
Mohamed4053 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.
sql-serverbackup-restore
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image Jeff Moden commented ·
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.
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mohamed4053 avatar image Mohamed4053 commented ·
Thanks for your Nice answers sir :).
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered
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/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

endizhupani avatar image
endizhupani answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.