Database Reference
In-Depth Information
FOREIGN KEY([PublishedDateKey])
REFERENCES [dbo].[DimDates] ([DateKey])
GO
In this exercise, you created a date dimension table in the data warehouse and added foreign key constraints to
the tables that reference it. We will fill the table with data during the ETL process, so for now we leave it empty.
Getting Organized
Excellent, we now have the database and all the tables, and we can get started with the ETL process. Before we
do, however, we need to organize our hard work by performing a few simple tasks.
Backing Up the Data Warehouse
We have already talked about how it is sometimes necessary to back up a data warehouse database. Well, this
is one of those times! The reason to do so now is to have a copy of the data warehouse in its empty state so that
you can hand it over to your testers and other developers for review. These team members can easily restore
the backup and do their work simultaneously with yours. Also, a database backup is a simple way to save your
progress thus far.
The backup and restore process is easy. Listing 5-13 exhibits code that does both.
Listing 5-13.
Backing Up and Restoring a Database
BACKUP DATABASE [DWPubsSales]
TO DISK =
N'C:\_BISolutions\PublicationsIndustries\DWPubsSales\DWPubsSales_BeforeETL.bak'
GO
RESTORE DATABASE [DWPubsSales]
FROM DISK =
N'C:\_BISolutions\PublicationsIndustries\DWPubsSales\DWPubsSales_BeforeETL.bak'
WITH REPLACE
Go
■
The SQL backup statement will not create folders if they do not exist. Consequently, before you
execute the backup command, it is important to create any folders indicated in the backup path. If you try running
the code in Listing 5-13 without the folder, you can expect an error.
Important
Scripting the Database
Another way that you can preserve your work is by using the database scripting tool. SQL Server has long
provided a tool for scripting the objects in the database, and SQL 2012 is no exception. You can launch the
scripting tool by right-clicking the database in Object Explorer and selecting Tasks
➤
Generate Scripts from the
context menu. The selection launches the Generate and Publish Scripts Wizard you see in Figure
5-31
.
■
You may notice that there is also a Script Database menu item, but this option only generates code to
create the database and not all the tables within it.
Note
