I have a testing database that I restore regularly from a production backup file in SQL Server 2008 R2 to get new data. We often edit\change or update the stored procedures in the testing DB to test the data compared to the production's stored procedures!
Is there away to only refresh\restore the table(s) and not the stored procedures? so I don't have to re-edit them back to the changes that I need?
Both of these databases (Production and Testing) are in one server.
My DB size is about 5 GB and we can't afford to have third party tools.
Best Answer
In SQLServer, there is no option to backup/restore just the tables. You could create an SSIS package to import data from specific/all tables and schedule it using the SQL Agent or run manually.
As both the databases Production and testing are on the same server, the data load may be faster compared to pulling data from a remote server. To minimize the load on the production database, restore the backup to a new database and then import the data to the Test database.
If you drop and recreate the tables for the refresh make sure the indexes are created after the data import.
Or if you choose to bulk import on to an existing table, drop the indexes and recreate them later and change the recovery model to simple or bulk logged.
If you have few very large tables, you can probably consider moving to separate file groups and do a partial backup and restore.
You can also try third part tools like Dell Litespeed, Idera virtual database or Apex SQL restore etc. for object level restore.