How to restore a Huge table from one server to another server ( Table Refresh )

Table Refresh 

1. Generate table script with data

      You can use this method if your table size is small, This is very easy method to restore a table quickly. Follow the below steps to script out a table with data

• Right click on database and click on tasks then click on generate scripts option, Then you will see below window

• Click on Next select Specific database objects then expand the tables below and select your table and then click on next 

• Then click on Advanced option as like below, If you want you can change the file path location beside file name.

• Then select an option Data Only for Types of data to script as like below

• Then click on OK and click on NEXT and click on finish. Now your file is ready to execute it on another server.

• Now copy that file into another server and open it in SSMS and execute it. Now your refreshed table is ready.

If your table size is huge this way will not work as we cannot execute huge scripts in SSMS. I found one easiest way as below to restore a table from one server to another

• Create one new temporary database and copy the required table into this database using below command

 Example command : Select * into Employee_temp from Test.dbo.Employee

• Now your table has been created in new database with data.
• Take the backup of that new data base and copy that backup file into another server.
• Then restore that backup into another server, now a new database will get created now execute the following command on your original database where you need to refresh a table

Example command : 

INSERT INTO [dbo].[Employee] SELECT * FROM [Test_Temp].[dbo].[Employee_temp]

This is very easiest method if you have very big table with GB's of size. There are few another methods are also there to restore but I found this is very every and quick.

Hope this post is useful to all DBA folks.