Page 63 - DCAP508_DATABASE_ADMINISTRATION

Unit 5: SQL Server Databases

procedures, DDL, and SQL-SMO published interfaces, and works to maintain the backward Notes
compatibility of these interfaces.

Did u know? SQL Server does not support triggers defined on the system tables, because
they might modify the operation of the system.

Notes System databases cannot reside on UNC share directories.

5.1.2 Viewing System Database Data

You should not code Transact-SQL statements that directly query the system tables, unless that
is the only way to obtain the information that is required by the application. Instead, applications
should obtain catalog and system information by using the following:

System catalog views
SQL-SMO
Windows Management Instrumentation (WMI) interface
Catalog functions, methods, attributes, or properties of the data API used in the application,
such as ADO, OLE DB, or ODBC.
Transact-SQL system stored procedures and built-in functions.

5.2 User Databases

In SQL Server, you can move the data, log, and full-text catalog files of a user database to a new
location by specifying the new file location in the FILENAME clause of the ALTER DATABASE
statement. This method applies to moving database files within the same instance SQL Server.
To move a database to another instance of SQL Server or to another server, use backup and
restore or detach and attach operations.

Notes Some features of the SQL Server Database Engine change the way that the Database
Engine stores information in the database files. These features are restricted to specific
editions of SQL Server. A database that contains these features cannot be moved to an
edition of SQL Server that does not support them. Use the sys.dm_db_persisted_sku_features
dynamic management view to list all edition-specific features that are enabled in the
current database.
The procedures in this topic require the logical name of the database files. To obtain the name,
query the name column in the sys.master_files catalog view.

When you move a database onto another server instance, to provide a consistent experience to
users and applications, you might have to recreate some or all the metadata for the database.

5.3 Database Planning

Designing a database requires an understanding of the business functions you want to model. It
also requires an understanding of the database concepts and features that you want to use to

LOVELY PROFESSIONAL UNIVERSITY 57