Extracting a database to a BAK file or to an MDF file is a common and useful thing to do in many cases. Loading it back at a later time allows us to go back to the point in time when we extracted the data. But we may want to load back only part of the database, and keep the rest of the database at its current state.

PartitionDB Add-in enhances the functionality of extract and load that we already know. Here is how:

  • You can load back specific tables into their original database, without overwriting the whole database.
  • You can automate your backup and restore processes by adding these commands to your scripts.

Using extract and load Add-in is quick and straightforward. Keep reading to learn more.

Extract and load to/from MDF file

In order to run the following example, and use the extract and load Add-in, please download and restore PdbLogic.

In this example we will backup some of the tables in a database, then we will change the remaining tables. Finally, we will restore the tables that we backed up. We will see that the restored tables contain the same data as they did at the time of backup, and that the loading process does not effect the tables that were not part of the backup.
First, we create a new database, TestEXTRACTDB. New tables are then added to the database: Test, Test2 and Test3.

Tables Test2 and Test3 are extracted to an MDF file:

At this point a TestEXTRACTDB_temporary database is created. It includes table2 and table3 as was passed to IncludeTables parameter.
Note that if nothing is passed to IncludeTables, all the tables in the database will be extracted.

This is the returned message:

MSG-61066 Successfully Created Temporary Database TestEXTRACTDB_temporary
MSG-60148 Successfully Loaded Table dbo.Test2 on TestEXTRACTDB_temporary from TestEXTRACTDB
MSG-60148 Successfully Loaded Table dbo.Test3 on TestEXTRACTDB_temporary from TestEXTRACTDB

Now we can continue working on database. As for dbo.Test2 and dbo.Test3, we will be able to easily go back to their original state since we extracted them before:

Now let’s try to load the backup of the tables:

The message that we get informs that the data was loaded from the temporary database to the original one:

MSG-61066 Successfully Created Temporary Database TestEXTRACTDB_temporary
MSG-60148 Successfully Loaded Table dbo.Test2 on TestEXTRACTDB from TestEXTRACTDB_temporary
MSG-60148 Successfully Loaded Table dbo.Test3 on TestEXTRACTDB from TestEXTRACTDB_temporary

Table Test2 that was just loaded is back to its original values form the time it was extracted, while table Test has not been changed.

Extract and load to/from BAK file

The same example can be used to create a BAK file instead of an MDF file. In order to use a BAK format, have @ExtractMode = 2:

Extract and load modes

The extract and load functions support three modes:

  1. Extract and load of the whole database or part of it into a MDF file, using a temporary database. The temporary database is live and you can work on it like any other database. When loading back, the temporary database will be attached. Extracting to an MDF is recommended when working within the same database.
  2. Extract and load load of the whole database or part of it into a BAK file, using a temporary database. The temporary database is stored in a backup file. It cannot be changed. Loading back will restore the backed up database. Using a BAK file fits most to cases of working with different databases.
  3. Backup the whole database into a BAK file. Extracting does not use a temporary database. Loading will create a temporary database that will assist in loading the tables back to the original database.
    If you choose not to pass an extract/load mode, the second method will be used.

Below is an example for using mode 3, backup the whole database:

exec PdbLogic..PdbextractDatabase @SourceDatabaseName='TestEXTRACTDB',@ExtractMode=3
exec PdbLogic..PdbloadDatabase @TargetDatabaseName='TestEXTRACTDB',@LoadMode=3

Extract and load from PartitionDB can serve as your database “sourcesafe” tool. You can backup and load back different versions of your database, you can restore “old versions” of tables, and protect your database form errors and data loss.

Functions’ parameters

Here is the list of parameters that can be passed to the functions. Remember the you can always use the defaults.

PdbLogic..PdbextractDatabase

Parameter
SourceDatabaseName Name of source database
SourceLinkedServerName Name of source linked server
ExtractMode The SDK supports three different modes
1 – Create temporary database and detach it
2 – Create temporary BAK file with the relevant data
3 – Create full BAK file
LocalPath The folder that the BAK file will be located at (the default is c:\shared) for mode 2 and 3 only
BackupSuffix The suffix name of the BAK file. Default is _backup in our example TestEXTRACTDB_backup for mode 2 and 3 only nvarchar(128) = ‘_backup’
TemporarySuffix The suffix name for the MDF file. Default is Temporary as in the example TestEXTRACTDB_Temporary
IncludeTables Tables to be included in the restore. Default is all.
ExcludeTables Tables to be excluded from the restore. Default is none.

PdbLogic..loadDatabase

Parameter
TargetDatabaseName Name of target database
TargetLinkedServerName Name of target linked server
LoadMode The SDK supports three different modes:
1 – Attach the temperory database and then overwrite the source table
2 – Restore the temperory BAK file and then overwrite the source table
3 – Load the full BAK file and overwrite all the tables from the source
LocalPath The folder that the bak file will be placed at (the default is c:\shared) for mode 2 and 3 only
BackupSuffix The suffix name of the bak file. Default is _backup in our example TestEXTRACTDB_backup for mode 2 and 3 only nvarchar(128) = ‘_backup’
TemporarySuffix The suffix name for the mdf file. Default is Temporary so for our example TestEXTRACTDB_Temporary
IncludeTables Tables to include in the restore. Default is all.
ExcludeTables Tables to exclude from the restore. Default is none.
IsForceTruncate Should a truncate be applied after load (before first insert)
IsDisableTriggers Should triggers be disabled on the tables when the load takes place. Default is true
IsEnableTriggers Shoulld triggers be enabled at the end of the pocess. Default is true
IsRebuildIndexes Should tables be rebuilt at the end of the process. Default is false

To use this partitioning option and other PartitionDB utilities, download our PdbLogic and attach to your database. .
To learn more about PartitionDB capabilities check out our blog.