Database Reference
In-Depth Information
Resetting Your Destination Database
Whenever you test your ETL process, it is important to verify that the destination database (in this case the
DWPubsSales data warehouse) is currently in a preload state, as it would be if the SSIS package was scheduled to
run automatically. Over the years, we have seen a number of errors stemming from this issue.
To understand this better, let's go back to an example mentioned earlier. Consider a database with tables
and foreign key constraints and an SSIS package that drops the foreign key constraints, clears out the tables,
and replaces the foreign key constraints—in that order. Everything should run smoothly if the database is in its
preload state (where the foreign key constraints exist before the execution of the SSIS package begins). If, while
testing your work, you ran an Execute SQL Task that was configured to drop the foreign key constraints, the
database would no longer be in its normal, preload state.
At this point, if you try to execute the entire SSIS package, the task that drops these constraints fails because
the foreign key constraints are now missing! In fact, that is how we forced the error in Figure
7-38
to appear.
You can resolve this problem by replacing the foreign key constraints and rerunning the SSIS package again,
but occasionally these issues are less obvious and you find troubleshooting is taking an exorbitant amount of
time. When this happens, resetting the database to its preload state is the quickest way to resolve these issues.
There are two common ways to reset a database: running a SQL script or restoring a database backup.
In Chapter 6, we wrote a SQL script that creates the DWPubsSales database and all of the objects inside of
it. Running this SQL script resets the database to its normal, empty state: resetting the database. The downside
of this approach is that the script will not normally place data into the tables. Therefore, if your SSIS package is
expecting at least some data to be present, you have to find a way to add it. In that case, the script can be modified
to insert data, but you might want to consider restoring a copy of the database from a SQL backup instead.
To create a SQL backup, you can open a SQL Query window in SQL Server Management Studio, as we did in
Chapter 6, and execute code similar to Listing 7-1.
Listing 7-1.
Creating a Backup Copy of a Data Warehouse for the Developer Team
BACKUP DATABASE [DWPubsSales]
TO DISK=N'C:\_BISolutions\Publications Industries\DevBackup.bak'
WITH INIT -- Initialize the backup file by clearing its contents
This code copies all of the tables, constraints, views, stored procedures, and other objects in the database
along with all of the data in the tables and indexes. A common scenario is for a SQL administrator to make a
backup for the developer team to use in their development and testing process and then to place it on a network
share that the development team has access to. They then either restore the backup on a development server, let
each developer restore a copy to their local computers, or both.
Listing 7-2 is an example of the code used to restore a database.
Listing 7-2.
Restoring a Backup Copy of a Data Warehouse for the Developer Team
RESTORE DATABASE [DWPubsSales]
FROM DISK=N'C:\_BISolutions\Publications Industries\DevBackup.bak'
WITH REPLACE -- replace the existing DB files as needed
Ti
■
Being able to back up and restore a sQL database is a very useful skill. we recommend you do some more
research on this subject when you can. A place to start is by performing an internet search on the keywords “TsQL
backup command.”
