Database Reference
In-Depth Information
Listing 19-1.
Restoring a SQL Server Backup File from a Network Share
-- Check to see if they already have a database with this name...
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DWPubsSales')
BEGIN
-- If they do, they need to close connections to the DWPubsSales database, with this code!
ALTER DATABASE [DWPubsSales] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
END
-- Now now restore the Empty database…
USE Master
RESTORE DATABASE [DWPubsSales]
FROM DISK = N'\\RSLAPTOP2\PubsBIProdFiles\DWPubsSales_BeforeETL.bak'
WITH REPLACE
GO
Another way to deploy the database is to run a SQL script that creates the database and all its objects, like we
did in Chapter 2 (Exercise 2-2). But, restoring the backup includes data, whereas running the script does not. This
is both good and bad. It is good because you do not have to run the SSIS process to the data to test your reports
and SSAS processing, but it is bad because the backup file may be very large.
You will have to decide what is practical for your situation. Both options work well for deploying most data
warehouses, so you really cannot go too wrong choosing one over the other.
You can run SQL code from a command prompt using Microsoft's
sqlcmd.exe
utility. This utility is useful for
creating deployment batch files and scripts. For example, the code in Listing 19-2 sets the focus to the directory
when the
sqlcmd.exe
utility is located and then executes a SQL file located on a network share called
\\RSLAPTOP2\PubsBIProdFiles.
Listing 19-2.
Executing a SQL File from a Network Share
CD "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn"
SQLCMD.exe -S RSLaptop2\SQL2012 -E -i \\RSLAPTOP2\PubsBIProdFiles\RestoreDWPubsSales.sql
running sQlCMD.exe With a BatCh File
This code can be directly typed into a Windows command prompt or placed into a text file with a
.bat
extension (batch file). The batch file option is preferred, because one batch file can be used for multiple
deployments.
Working with the command prompt and batch files was much more common in the early days of PC
computing than it is now. We mention batch files several times in this chapter because they are so useful for
automating deployments. If you are not familiar with batch files, we recommend searching the Internet for
more information; they are really useful!
Deploying the SSIS ETL Process
After the database has been deployed to the production server, the ETL process must be executed by running
the SSIS package. his can be done manually using Visual Studio as you have done in this topic, or you can
execute SSIS code using a command prompt utility called
dtexec.exe
. Listing 19-3 shows an example of how
this utility is used.
