Can I restore all tables but not the stored procedures?

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.

Yes, you can do this, just not by using a database BACKUP / RESTORE. Instead, you would use the SqlPackage.exe utility that is part of SQL Server Data Tools (SSDT). This utility usually assists in migrating database projects in Visual Studio to SQL Server. But, it can also extract schema and data from a database into a file, and then it can push whatever is in that file into another database.

I created a CMD script, GetDataFromProduction.cmd, that extracts the schema and data, and then publishes only the tables and data. The Stored Procedures, Functions, etc are extracted into the .dacpac file (no way around that), but they can be excluded when publishing the content into a database. The following script calls SqlPackage.exe twice, once with /Action:Extract and once with /Action:Publish. It uses /p:ExtractAllTableData=True on the "extract" to grab the data for all tables.

@ECHO OFF

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" ^
/Action:Extract ^
/SourceDatabaseName:TEMPTEST ^
/SourceServerName:(local) ^
/TargetFile:C:\TEMP\_Extract.dacpac ^
/p:IgnoreExtendedProperties=True ^
/p:ExtractAllTableData=True

ECHO Hit the "any" key to publish the extracted data
PAUSE

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" ^
/Action:Publish ^
/SourceFile:C:\TEMP\_Extract.dacpac ^
/TargetDatabaseName:TestPublish ^
/TargetServerName:(local) ^
/p:DropObjectsNotInSource=False ^
/p:ExcludeObjectTypes=Aggregates;ApplicationRoles;Assemblies;AsymmetricKeys;^
BrokerPriorities;Certificates;Contracts;DatabaseRoles;DatabaseTriggers;^
ExtendedProperties;FileTables;MessageTypes;Permissions;Queues;RemoteServiceBindings;^
RoleMembership;Rules;ScalarValuedFunctions;SearchPropertyLists;Sequences;Services;^
Signatures;StoredProcedures;SymmetricKeys;Synonyms;TableValuedFunctions;^
UserDefinedDataTypes;UserDefinedTableTypes;ClrUserDefinedTypes;Views;Audits;Credentials;^
CryptographicProviders;DatabaseAuditSpecifications;Endpoints;ErrorMessages;^
EventNotifications;EventSessions;LinkedServerLogins;LinkedServers;Routes;^
ServerAuditSpecifications;ServerRoleMembership;ServerRoles;ServerTriggers;^
ColumnEncryptionKeys;ColumnMasterKeys;Defaults;ExternalDataSources;ExternalFileFormats;^
ExternalTables;Filegroups;FullTextCatalogs;FullTextStoplists;PartitionFunctions;^
PartitionSchemes;SecurityPolicies;Users;XmlSchemaCollections;DatabaseScopedCredentials;^
Logins

PAUSE


Now, if you do not want the data for all tables, then you can specify individual tables to get. Please note that there is no option to instruct SqlPackage to get all tables except one or two. If you want all but one or two, then you need to specify all of the tables to grab. When specifying individual tables, you need to first specify /p:ExtractAllTableData=False (it was True in the example above) and then add a new "property" to specify each table in "SchemaName.TableName" format:

/p:TableData="dbo.Table1" ^
    /p:TableData="Orders.Order" ^
    /p:TableData="Orders.OrderItems"

As you can see, you can specify /p:TableData="SchemaName.TableName" as many times as you need. Unfortunately it does not appear that there is any ability to specify a wild-card for "SchemaName.%" or anything like that (because that would make things too easy, and what's the fun in that?). However, you can use the following to generate that list of tables to just copy-and-paste into the CMD script:

SELECT N' /p:TableData="'
+ SCHEMA_NAME(tbl.[schema_id])
+ N'.'
+ OBJECT_NAME(tbl.[object_id])
+ N'" ^'
FROM sys.tables tbl;


Please also note that while this method does do exactly what is being requested here, I have no basis for estimating at what data volumes this becomes unmanageable. I suppose if a database is too large for /p:ExtractAllTableData=True, then you can break the migration into pieces by specifying /p:ExtractAllTableData=False and then specifying a subset of tables that is manageable, and repeating that across 2 or several of these scripts.

That being said, it does handle the data sync quite nicely in that it will make whatever changes are necessary to get the existing schema and data to match whatever is in the .dacpac file. You don't need to mess with truncating the tables first or anything like that.

If you do not already have Visual Studio and/or SSDT installed, you can get SSDT by itself from: Download Latest SQL Server Data Tools

On a related topic:


We often edit\change or update the stored procedures in the testing DB to test the data compared to the production's stored procedures!

Why are you changing the Stored Procedures? Doing this, or needing to do this, might point to a problem in your release process and/or architecture that could be improved. Generally speaking, you shouldn't be making code changes to match a different set of data. That implies that you are either hard-coding Customer info in the code (

StackExchange Database Administrators Q#119795, answer score: 10