1

On our oracle databases, we regularly run database export through expdp/impdp utility (wrapped for readability):

$ expdp user/[password] SCHEMAS=<nom_schema> DIRECTORY=expDatapump 
     DUMPFILE=exp.<schema>.dmp LOGFILE=exp.<schema>.log

Thanks to it we are able to refresh data from a specific schema on test database, for example.

I am wondering if there is such a way to do this in Microsoft SQL Server. I have tested the -> tasks -> "export data" wizard within SSMS, but that was not very helpful (useable only for very small db, PK & FK deleted etc).

Can you perform dumps from your SQL Server databases? On an other hand, is it preferable (if possible) to directly restore a specific SQL Server schema from a .bak file?

  • No, that was a question : "If i cannot use a tool like expdp for SQL Server, will it be a good idea (as an alternative solution) to restore, if it's possible, a particular schema instead of the whole db ?"" – Mickael Jan 6 '17 at 13:28
0

There is no way to restore a partial database from a SQL Server backup file. If you are looking to export and import data from various tables in one SQL Server database to another, you'll need to use one of the following methods:

  1. BCP export and import
  2. SQL Server Integration Services (SSIS)
  3. SQL Server Replication
  4. A third party tool, such as Informatica. This is not an endorsement of their product.

This question and potentially others listed in the "Related" section to the left may offer more help.

  • 1
    Refering to your list, BCP may be the only way to achieve that according to our environment - thanks – Mickael Jan 9 '17 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.