Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
24 Cards in this Set
- Front
- Back
|
What command is used to changed the default file group
|
ALTER DATABASE <database name> MODIFY FILEGROUP <filegroup name> DEFAULT
|
|
|
What is the maximum transaction throughput for any database?
|
The maximum transaction throughput for any database is bound by the amount of data per second that SQL server can write to the transaction log
|
|
|
What is FILESTREAM
|
FILESTREAM allows you to associate files with a database.
|
|
|
What are the types of files that you create for databases and what are the commonly used file extensions?
|
You can create data and log files for a database. Data files commonly have either an .mdf or .ndf extension, whereas log files have and .ldf extension
|
|
|
What is the purpose of the transaction log?
|
The transaction log records every change that occurs within a database to persist all transactions to disk
|
|
|
Full Recovery Model
|
When a database is in the Full recovery model, all changes made, using both data manipulation language (DML) and data definition language (DDL) are logged to the transaction log. Loss is minimized with the full recovery model
|
|
|
What is the best practice for the Full Recovery Model?
|
Every production database that accepts transactions should be set to the Full recovery model. by placing the database in the Full recovery model, you can maximize the restore options that are available.
|
|
|
Bulk-Logged Recovery Model
|
Bulk-Logged recovery model allows certain operations to be executed with minimal logging.
|
|
|
What operations are performed in a minimal logged manner with the database set in the Bulk-logged recovery model?
|
BCP
BULK INSERT SELECT....INTO CREATE INDEX ALTER INDEX...REBUILD |
|
|
List the three types of Recovery Models
|
Full
Bulk-Logged Simple |
|
|
Simple Recovery Model
|
Logs operations to the transaction log exactly as the Full revoery model does. however, each time the database checkpoint process executes, the committed portion of the transaction log is discarded. A database in the Simple recovery model cannot be recovered to a point in time because it is not possible to issue a transaction log backup for a database in the simple recovery model
|
|
|
How can you set the recovery model in a database?
|
because the recovery model is a property of a database, you set the recovery model by using the ALTER DATABASE command
|
|
|
What is the ALTER command for setting the recovery model in a database?
|
ALTER DATABASE database_name SET RECOVERY {FULL | BULK_LOGGED | SIMPLE }
|
|
|
What are the five options for a database that enable certain actions to occur automaticall
|
AUTO_CLOSE
AUTO_SHRINK AUTO_CREATE_STATISTICS AUTO_UPDATE_STATISTICS AUTO_UPDATE_STATISTICS_ASYNCH |
|
|
What happens if thye AUTO_CLOSE option is enabled?
|
When the last connection to the database is closed the SQL shuts down the database and releases all resources associated with the database. When a new connection is made the SQL server starts up and begins allocating resources
|
|
|
How do you restrict database access to members of the db_owner role and
terminate all active transactions and connection at the same time? |
. You would execute the following command: ALTER DATABASE <database name>
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE |
|
|
What backups can be executed for a database in each of the recovery models?
|
You can create full, differential, and fi le/fi legroup backups in the Simple recovery
model. The Bulk-logged recovery model allows you to execute types of backups, but you cannot restore a database to a point in time during an interval when a minimally logged transaction is executing. All types of backups can be executed in the Full recovery model. |
|
|
How do you change the database recovery model to Full?
|
ALTER DATABASE database
SET RECOVERY FULL GO |
|
|
Page Verification best practice is
|
You should enable the PAGE_VERIFY CHECKSUM option on every production database.
|
|
|
What checks does DBCC CHECKDB perform?
|
What checks does DBCC CHECKDB perform?
|
|
|
Define Schema
|
schema provides a convenient mechanism to group objects together
within a database. A schema is also the container that owns all objects within a database |
|
|
Sytax to create a schema?
|
CREATE SCHEMA <schema name> AUTHORIZATION <owner name>
|
|
|
What does DECIMAL(8,3) mean?
|
assigning a column the DECIMAL(8,3) data type allows SQL Server to store a total of
eight digits in the column, with three of the digits to the right of the decimal point or values between -99999.999 and 99999.999. |
|
|
What are the 7 properties you can apply to a column?
|
COLLATE, IDENTITY,
ROWGUIDCOL, FILESTREAM, NOT FOR REPLICATION, and SPARSE. |
