Database Reference
In-Depth Information
Listing 5-1.
Creating a New Database
/* (Note the following code is expected to error out and is only a demo) */
USE [master]GO
CREATE DATABASE [DWWeatherTracker] ON PRIMARY
( NAME=N'DWWeatherTracker'
, FILENAME=N'
D:\
_BISolutions\DWWeatherTracker.mdf' -- On the D:\ hard drive
, SIZE=10MB
, MAXSIZE=1GB
, FILEGROWTH=10MB )
LOG ON
( NAME=N'DWWeatherTracker_log'
, FILENAME=N'
F:\
_BISolutions\DWWeatherTracker_log.LDF' -- On the F:\ hard drive
, SIZE=1MB
, MAXSIZE=1GB
, FILEGROWTH=10MB)
GO
EXEC [DWWeatherTracker].dbo.sp_changedbowner @loginame=N'SA', @map=false
GO
ALTER DATABASE [DWWeatherTracker] SET RECOVERY BULK_LOGGED
GO
Estimating the exact size of the data warehouse files can be tricky. You do not have to be exact, however, just
close. SQL Server databases have the capacity to grow in size automatically as more data is added, but it is considered
best practice not to rely on this feature. An easy way to estimate the data warehouse size is by looking at the source
databases or source files to identify what is going to be imported and how much space it currently takes up.
If your source is a SQL Server OLTP database, the process is greatly simplified by a number of reports
included in SQL Server Management Studio. To access these reports, right-click an object in the treeview of
Object Explorer and look for the Reports menu option. From there you see a list of standard reports, a selection
for custom reports and a list of currently used reports. Upon selecting a particular report, such as the one
shown in Figure
5-13
, a report is generated for you. You can estimate the size of your data warehouse tables by
Figure 5-13.
Estimating the size of the data warehouse database
