Sql-server – Does a database restore remove existing stored procedures

You can use server side trace (different from using Profiler GUI that incurs more resources) during your testing or your business cycle and capture only stuff related to SP's. Then you can load that in a table or excel for further analysis.

Second approach, is to use DMV sys.dm_exec_procedure_stats (with limitation that if sql server is restarted, then the data is flushed).

You can even schedule a job to capture DMV data to a table to keep it persisted.

 -- Get list of possibly unused SPs (SQL 2008 only)
    SELECT p.name AS 'SP Name'        -- Get list of all SPs in the current database
    FROM sys.procedures AS p
    WHERE p.is_ms_shipped = 0

    EXCEPT

    SELECT p.name AS 'SP Name'        -- Get list of all SPs from the current database 
    FROM sys.procedures AS p          -- that are in the procedure cache
    INNER JOIN sys.dm_exec_procedure_stats AS qs
    ON p.object_id = qs.object_id
    WHERE p.is_ms_shipped = 0;

Refer to :

• How to find unused Stored Procedures in SQL Server 2005/2008
• Identifying Unused Objects in a Database

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.