Restore Individual Tables Using SQL Server BACPAC


By:   |   Updated: 2017-08-28   |   Comments (11)   |   Related: More > Restore

Problem

Microsoft SQL Server does not allow for you to restore individual tables from a backup file, but there are times when you want to refresh a subset of tables, say inside of a data warehouse that has hundreds of tables. Restoring a 1TB data warehouse to refresh a few tables is inefficient, so we will show a more efficient method in this tip.

Solution

You can use a BACPAC to export and import a subset of tables in your SQL Server database. You can also automate the export and import by using the SqlPackage.exe executable that is installed with SQL Server.

Creation of a BACPAC inside of SQL Server Management Studio (SSMS) is easy to do. However, there is no option to script the creation of a BACPAC from within SSMS.

The BACPAC (and DACPAC) functionality inside of SSMS leverage the SqlPackage.exe executable that comes installed with SQL Server. The executable can be found in the installation directory, similar to the following: [install dir]\Microsoft SQL Server\140\DAC\bin. Here is the documentation on the parameters available for SqlPackage.exe. Using that document as a guide we can create an automated export and import process.

Create the command line syntax

Letís try this with one table from the AdventureWorks2014 database. We are going to create a BACPAC file by exporting the data from the HumanResources.Employee table:

.\sqlpackage.exe /Action:Export /ssn:PORC\SQL2016 /sdn:AdventureWorks2014 /tf:"C:\TeamData\FewTables.bacpac" /p:TableData=HumanResources.Employee 
   

We are able to connect, and the process starts, but we see an error message:

create a BACPAC file by exporting the data from the HumanResources.Employee table

And thus, we see the real issue with trying to restore a subset of tables inside a relational database. When faced with this issue the traditional method is to manually remove the relations, migrate the data, and restore the relations after the migration is complete. However, with BACPAC, we can migrate the group of tables together.

Letís take a look at how that can be done using the SqlPackage.exe executable. We will export tables from the HumanResources schema along with two tables in the Person schema. My command line now looks like this:

.\sqlpackage.exe /Action:Export /ssn:PORC\SQL2016 /sdn:AdventureWorks2014 /tf:"C:\TeamData\FewTables.bacpac" /of /p:TableData=HumanResources.Department /p:TableData=HumanResources.Employee /p:TableData=HumanResources.EmployeeDepartmentHistory /p:TableData=HumanResources.EmployeePayHistory /p:TableData=HumanResources.Shift /p:TableData=Person.BusinessEntity /p:TableData=Person.Person
   

And we can run this without error:

export tables from the HumanResources schema along with two tables in the Person schema

The BACPAC file has been created.

To import the data we can use the SqlPackage.exe executable with different options, but with an import caveat: we must import into a new database. We are not allowed to import into an existing database. This behavior is not different than importing from a BACPAC through SSMS, you must specify a new database name.

.\sqlpackage.exe /Action:Import /tsn:PORC\SQL2016 /tdn:FewTables /sf:"C:\TeamData\FewTables.bacpac"
   

The result will be a new database that has an identical schema to the original, but only a subset of the data:

New SQL Server database that has an identical schema to the original, but only a subset of the data

Now, letís put this together inside a job in SQL Agent.

Create the SQL Server Agent job

We can put our code into one (or more) job inside of SQL Server Agent and automate this process for recovering a subset of tables. The process outline looks like this:

  1. Export the tables from source database.
  2. Drop the target database if it exists.
  3. Import the BACPAC into a new database.

We will create one job, with three steps.

The first step can be accomplished with two lines inside a PowerShell script. The first line will change the working directory to where the SqlPackage.exe file resides, and then we will run the executable:

cd "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin"

.\sqlpackage.exe /Action:Export /ssn:PORC\SQL2016 /sdn:AdventureWorks2014 /tf:"C:\TeamData\FewTables.bacpac" /of /p:TableData=HumanResources.Department /p:TableData=HumanResources.Employee /p:TableData=HumanResources.EmployeeDepartmentHistory /p:TableData=HumanResources.EmployeePayHistory /p:TableData=HumanResources.Shift /p:TableData=Person.BusinessEntity /p:TableData=Person.Person
   

You could put this all on one line if you wish. Or make it dynamic to locate the install directory for your instance. Whatever works for you and your needs. Hereís what the step looks like inside the job I created:

Create BACPAC in SQL Server Agent

The second step is one line of T-SQL:

DROP DATABASE IF EXISTS [FewTables]
   

The third step is to do the import:

cd "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin"

.\sqlpackage.exe /Action:Import /tsn:PORC\SQL2016 /tdn:FewTables /sf:"C:\TeamData\FewTables.bacpac"
   

This is what the job looks like when configured:

Final SQL Server Agent Job

You can now set this job to run at whatever schedule you see fit. With this method, you save time by not having to restore the original database first and then migrate data back into the original database.

Summary

Migrating the data back into the original database can be tricky. You may find that you need to do extra steps (dropping foreign key constraints, then adding them back after the data is migrated) because relations exist between tables. Other things like identity values and indexes can cause you a headache as well. Fortunately, we have more than a handful of options to migrate data between databases.

You could use the SQL Server Import/Export Wizard to move the data. The wizard allows for you to save as an SSIS package, allowing for you to schedule the data migration as part of the process we built above. This might be the best option for most databases.

Another option would be for you to manually create some INSERT statements. This would also allow for you to easily add everything into a job inside of SQL Agent. But you will also need to script out the dropping and creation of constraints, indexes, etc. as well. Sometimes it is easiest to drop the target tables, recreate them, migrate the data, then add indexes and constraints at the end.

Depending on the location of the data, and size, you may want to use BCP or BULK INSERT. Using the one weird trick as I have explained on my blog you could crack open the BACPAC file by renaming it to a *.zip file, expose the table data, and BCP right into the target database. Of course, you would still need to take care of relations, constraints, and indexes, same as doing INSERT statements.

Lastly, I donít need to remind you about the importance of having database backups, but I will anyway. You should make certain you have backups of your databases before you start migrating data around. You have been reminded.

Next Steps


Last Updated: 2017-08-28


get scripts

next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 yearsí experience.

View all my tips
Related Resources





Comments For This Article




Monday, October 09, 2017 - 12:28:36 PM - Thomas LaRock Back To Top (67117)

 KVM,

If your BACPAC only has data for the few exported tables, then that's all that is going to be imported. CHeck the rowcounts for the tables and verify that they will have 0 rowes except for the tables you exported. 

 


Monday, October 09, 2017 - 12:27:37 PM - Thomas LaRock Back To Top (67116)

 Osama Waly,

The import and export utility allows for more granularity in the objects you want to migrate. HTH

 


Monday, October 09, 2017 - 12:25:56 PM - Thomas LaRock Back To Top (67115)

 Dave Rogers,

DACPAC and BACPAC have been around for a while, not sure when it was first allowed to do an export of a subset of tables. 

 

 


Monday, October 09, 2017 - 12:25:01 PM - Thomas LaRock Back To Top (67114)

 HP,

Make sure you are trying to execute the command from the directory where the sqlpackage.exe executable resides. I list the path name in the article. If the executable is not in that directory then you will see that error. HTH

 


Monday, October 09, 2017 - 12:23:35 PM - Thomas LaRock Back To Top (67113)

 Rob,

Sorry for the late reply. You said it brought all tables. Do you mean it brought all the data, or that it created empty tables? The import will recreate the schema, but it will only be able to import that data contained in the BACPAC. The code example above only exports a subset of tables, so those are the only tables that should have data in the imported database. HTH

 

 

 


Thursday, October 05, 2017 - 3:46:59 PM - KVM Back To Top (66940)

 

I tried this a few times, though the export runs only for selected tables, the import does all the tables in new created DB... any ideas why ?


Tuesday, September 05, 2017 - 6:24:55 AM - osama waly Back To Top (65896)

What is the difference between the import and export utility ? as long as i'm depening on an up and running database on another instance ?


Tuesday, August 29, 2017 - 2:19:34 PM - HP Back To Top (65582)

Hey Rob,

 

I tried on SQL 2016 with only few tables and it worked just fine for me. Although, it wasn't work on SQL 2014.


Tuesday, August 29, 2017 - 9:06:21 AM - Dave Rogers Back To Top (65574)

Is this a new feature introduced in SQL Server 2016?


Monday, August 28, 2017 - 4:09:38 PM - HP Back To Top (65546)

Hello,

Thanks for the tip. It's really useful when database is really large and we would like to restore only subset of data or table. But, somehow when i was running the bacpac command through powershell, i am getting the below error. Can you please help?

Program: 'sqlpackge.exe' failed to run: The specific executable is not a valid application for this OS platform.

CategoryInfo: ResourceUnavailbale , applicationFailedException

FaullyQualifiedErrorID : NativeCommandFailed

 


Monday, August 28, 2017 - 12:46:35 PM - Rob Back To Top (65538)

 Thomas,

  Great post. Thank you for a great demo. One issue... When I created the Bacpac it still imported ALL the tables from ***AdventureWorks? I followed the instructions but it brought all over.

I'm using SQL Server 2016.

 



download


Recommended Reading

SQL Server Database Stuck in Restoring State

How to migrate a SQL Server database to a lower version

Make Network Path Visible For SQL Server Backup and Restore in SSMS

Automate Database Restore for SQL Server

Identify when a SQL Server database was restored, the source and backup date





get free sql tips
agree to terms


Learn more about SQL Server tools