3

Using Microsoft SQL Server, is there a way to backup a subset of a production database? I only want the database structure, and a very small amount of transaction data for a few selected tables.

I want to use this backup file to refresh development database.

For Oracle I see this is achieved using this Powershell script:

userid="/ as sysdba"
compression=all
directory=TEST_DIR
flashback_time=systimestamp
dumpfile=XX.dmp
logfile=xx.log
schemas=XXXXX
query=SCHEMA.TABLE:"where <condition>
exclude=TABLE:"IN (x)"
4
  • 1
    I wouldn't use a backup for this, I would automate using a schema / data compare product, there are plenty out there but we have one you can look at. – Aaron Bertrand Jun 12 '18 at 17:26
  • 1
    Oracle dump != backup. – mustaccio Jun 12 '18 at 17:43
  • @mustaccio Sorry I might have been also my mistake. I don't know the Oracle terminology so well, however I think the OP wants a dump with the goal of backing his db up. :-) – peterh Jun 12 '18 at 18:32
  • 1
    I'd just like to point out that the script you are mentioning in your question is in fact the contents of a so-called .par file which contains the configuration for either a impdp or expdp in Oracle. You would store that script for example in a export.par file and then execute the command expdp parfile=export.par and if your Oracle has an export directory configured, you will be rewarded with an XX.dmp file containing the contents of an Oracle schema named XXXXX. – John K. N. Jun 13 '18 at 5:30
6

In SQL Server Management Studio (SSMS), you can generate a script that would create the structural components of your database (tables and views, as well as users, functions, and procedures). You could use the script(s) returned on your development DB.

To run this, right-click on your database in SSMS, and choose Tasks => Generate scripts.... This opens a wizard that will step you through the process, giving you the option of what you want to copy over.

For tables, there are advanced options you can set. This allows you to include indexes and triggers, and to include the data in the tables. However, this would give you all the data from the tables, not just a selected amount.

If you want all the data from selected tables, and no data from the rest, then two passes through this will let you generate two scripts.

In the first pass, select everything, script things as "DROP if exists" (assuming you want to replace anything that's already there), do not include data, and save that as a single script.

In the second pass, only select the tables that you want to copy over the data for; do not choose the "DROP if exists" option; and do include the data, saving this as a second single script (or, if you prefer, as one script per table).

NOTE: This creates scripts that you would run on your database, rather than true backups.

  • Among other things, this does mean that any objects that have been removed from the production database, will not be removed by the script; it just recreates what it knows about. If what you really want is a clean DB to start with, you may want to drop the current dev database and create a new one with the same name. make sure you've captured every object in the first script, though, or things will be missing.
  • And, if you need to populate some tables with just a part of the production data, then you would either need to manually remove extra rows from the script file, or use a different process to populate the tables.

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.