New

Long-Term Learning

Learn efficiently and remember over time.

Start Long-Term Learning

Get personalized study reminders at intervals optimized for better retention.
Track your progress on this set by creating a folder
Or add to an existing folder

Add this set to a folder

  • The purpose of a database backup is ___

    to have something to restore if data is lost during a business's daily routine.

    SQL Server offers three recovery models for each database. They are: ___

    • Simple Recovery
    • Full Recovery
    • Bulk-Logged

    Simple Recovery requires the ___ since the transaction log backups are truncated on a regular basis.

    least administration

    Full Recovery allows you to ___

    restore to a point in time since the logs files record all SQL transactions and the time they were performed.

    The disadvantages of Full Recovery mode is that the logs ___

    can grow a lot.

    Bulk-Logged ???

    Allows good performance while using the least log space.

    Backups that SQL Server supports: ???

    Full, differential, and incremental backups.

    Full backup: ___

    Contains all the data in a specific database or set of filegroups or files to allow recovering that data.

    Differential backup only backs up data since ___

    the last full backup.

    A differential backup contains only the data that has changed since the ___

    differential base.

    Usually, a differential base is ___

    used by several successive differential backups.

    At restore time, the full backup is restored ___, followed by the most recent differential backup.

    first

    An incremental backup is based on the ___

    last backup of the data.

    An incremental backup contains only the data that has changed since the ___

    last full or incremental backup.

    Incremental backups are smaller and faster than ___

    full backups and differential backups.

    At restore time, the ___ , followed by each incremental backup following the full backup.

    full backup is restored first

    Over time, as a database is updated, the amount of data that is included in differential backups increases. This makes the backup slower to create and to restore. Eventually, another ___ to provide a new differential base for another series of differential backups.

    full backup must be created

    Types of database backups supported by SQL Server: ___

    Database backup & Differential database backups

    Database backup ???

    A full backup of the whole database.

    Differential database backups ???

    A backup of all files in the database. This backup contains only the data that were modified since the most recent database backup of each file.

    Types of partial backups supported by SQL Server: ___

    Partial backup & Differential partial backup

    Partial backup ???

    A backup of all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups. A partial backup of a read-only database contains only the primary filegroup.

    Differential partial backup ???

    A backup that contains only the data that were modified since the most recent partial backup of the same set of filegroups.

    File backups should be used only where they clearly add value to your ___

    restore plan.

    Types of file backups supported by SQL Server: ___

    File backup & Differential file backups

    File backup ???

    A full backup of all the data in one or more files or filegroups.

    Differential file backups ???

    A backup of one or more files that contain data extents that were changed since the most recent full backup of each file.

    You can ___ new backups to any existing backups on a device, or you can overwrite any existing backups.

    append

    You can append new backups to any existing backups on a device, or you can ___ any existing backups.

    overwrite

    During a backup operation, SQL Server copies the data directly from the database files to the ___

    backup devices.

    Performing a backup operation has minimal effect on ___

    transactions that are running;

    You can perform a SQL Server backup with minimal effect on ___

    production workloads.

    Transactions that are running during the backup are never ___

    delayed

    Restore scenarios possible in SQL Server include the following: ___

    Complete database restore, File restore, Page restore, Piecemeal restore, Recovery only, Transaction log restore, Create a mirror database, Create and maintain a standby server.

    Complete database restore ???

    Restores an entire database, beginning with a full database backup, which may be followed by restoring a differential database backup (and log backups).

    File restore ???

    Restores a file or filegroup in a multi-filegroup database. After a full file restore, a differential file backup can be restored.

    Page restore ???

    Restores individual pages.

    Piecemeal restore ???

    Restores a database in stages, beginning with the primary filegroup and one or more secondary filegroups.

    Recovery only ???

    Recovers data that is already consistent with the database and needs only to be made available.

    Transaction log restore ???

    Under the full or bulk-logged recovery model, since the logs record each transaction, restoring from log backups is required to reach a desired recovery point.

    Create a mirror database ???

    When information is written to one server, it is automatically replicated to the second server.

    When you have a ___ , you have duplicate databases on multiple servers.

    mirror database

    Create and maintain a standby server ???

    When the active server fails, the passive server will become the active server, allowing for minimum downtime.

    When you have a ___ server, you are using an active-passive cluster that consists of two or more servers.

    standby

    To restore data through the graphical interface tool, follow these steps: ___

    Expand Databases »» Right-click the database, point to Tasks, then click Restore »» Database »» the name of the restoring database appears in the To database list box. To create a new database, enter its name in the list box (etc)

    In the Restore options panel, you can choose any of the following options : ___

    • Overwrite the existing database.
    • Preserve the replication settings.
    • Prompt before restoring each backup.
    • Restrict access to the restored database.

    The Transact-SQL RESTORE command enables you to perform the following restore scenarios: ___

    • Restore an entire database from a full database backup.
    • Restore part of a database.
    • Restore specific files or filegroups to a database.
    • Restore specific pages to a database.
    • Restore a transaction log onto a database.
    • Revert a database to the point in time.

    Complete restore ???

    Restore an entire database from a full database backup.

    Partial restore ???

    Restore part of a database.

    File restore ???

    Restore specific files or filegroups to a database.

    Page restore ???

    Restore specific pages to a database.

    Transaction log restore ???

    Restore a transaction log onto a database.

    Revert a database to the point in time captured by a ___

    database snapshot.

    To restore the AdventureWorks database using the C:\AdventureWorks.BAK backup, you would execute the following command: ___

    RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AventureWorks.BAK'

    Generically, to restore the database using the specified file, you would execute the following command: ___

    RESTORE DATABASE name_of_database FROM DISK = 'name of backup'

    A permission is used to grant an entity access to an ___

    object.

    A login or logon is ___

    the process by which individual access to a computer system is controlled by identification of the user through the credentials he or she provides.

    The most common login method involves supplying a ___

    username and password.

    A user account is ___

    a logical representation of a person within an electronic system.

    A ___ is a logical representation of a person within an electronic system.

    user account

    Each object's permission is assigned by ___

    granting, revoking, or denying user login permissions.

    Authentication is the act of ___

    establishing or confirming a user or system identity.

    Windows Authentication mode is superior to ___ because users don't need to learn yet another password and because it leverages the security design of the network.

    mixed mode

    The «sa account» is the built-in SQL administrator account associated with ___

    SQL authentication.

    SQL Server includes fixed, predefined server roles. Primarily, these roles grant permission to ___

    perform certain server-related administrative tasks.

    SQL Server includes ___ . Primarily, these roles grant permission to perform certain server-related administrative tasks.

    predefined server roles

    The sysadmin role can perform any ___ in the SQL Server installation, regardless of any other permission setting. The sysadmin role even overrides denied permissions on an object.

    activity

    The sysadmin role can perform any activity in the SQL Server installation, regardless of any other permission setting. The sysadmin role even overrides ___ on an object.

    denied permissions

    The ___ can perform any activity in the SQL Server installation, regardless of any other permission setting. The ___ even overrides denied permissions on an object.

    sysadmin role

    The ___ is a fixed role, but it can have object permissions like a standard role. Every user is automatically a member of the ___ and cannot be removed, so the ____ serves as a baseline or minimum permission level.

    public role

    The public role is a fixed role, but it can have object permissions like a standard role. Every user is automatically a member of the public role and cannot be removed, so the public role serves as ___

    a baseline or minimum permission level.

    Users must be explicitly granted access to ___

    any user database.

    The ___ is a special role that has all permissions in the database.

    db_owner

    The db_owner is a special role that has ___

    all permissions in the database.

    An ___ is a database-specific role intended to allow an application to gain access regardless of the user.

    application role

    An application role is a database-specific role intended to allow an ___

    application to gain access regardless of the user.

    ___ only backs up data since the last full backup.

    Differential backup

    Differential base or base of the differential ???

    Latest full backup of the data

    Regardless of an organization's size, the end result of its ___ should be to ensure that users' assigned rights and responsibilities are enforced through a security plan.

    database security

    It is important to be aware of the ___ associated with each object in a database because it's possible to inadvertently grant administrative rights to objects or users that should not have them.

    rights and permissions

    Within SQL Server, users are assigned to roles, which may in turn grant permission to objects... each object has an owner, and ___ also affects permissions.

    ownership

    Within SQL Server, users are assigned to ___, which may in turn grant permission to objects... each object has an owner, and ownership also affects permissions.

    roles

    Within SQL Server, users are assigned to roles, which may in turn ___ permission to objects... each object has an owner, and ownership also affects permissions.

    grant

    Within SQL Server, users are assigned to roles, which may in turn grant ___ ... each object has an owner, and ownership also affects permissions.

    permission to objects

    In the security model for a SQL Server, there are three different methods by which a user can be initially identified: ___

    • Windows user login
    • Membership in a Windows user group
    • SQL Server-specific login (if the server uses mixed-mode security)

    At the SQL Server level, where the database resides, users are known by their ___ . This can be a SQL Server login, a Windows domain login, or a username login.

    login names

    if you add a user to the sysadmin role, that user now has ___ for that server.

    full access to every server function, database, and object

    The sysadmin role is a powerful one, and you must be sure not to grant it to the ___ user login.

    wrong

    It is possible for users who have not been granted direct access to a database to gain access using the ___ user account - and with this account, they can make limited changes within the database server.

    "guest"

    Even though a user may belong to a fixed database role and have certain administrative level permissions, he or she still cannot access data without first being granted permission to ___ (e.g., tables, stored procedures, views, functions).

    database objects

    Each object's permission is assigned through ___ , denying, or revoking user login permissions.

    granting

    Each object's permission is assigned through granting, ___ , or revoking user login permissions.

    denying

    Each object's permission is assigned through granting, denying, or ___ user login permissions.

    revoking

    Granting permission means that a user can ___

    access the object.

    Denying permission overrides ___

    a granted permission.

    Revoking a permission ___ the permission that has been assigned, regardless of whether it was a denied permission or a granted permission.

    removes

    Revoking a permission removes the permission that has been assigned, regardless of whether it was a ___ permission.

    denied or a granted

    A user may have multiple permission ___ to an object (e.g., individually, through a standard database role, and through the public role). If any of these ___ are denied, then the user is blocked from accessing the object.

    paths

    A user may have multiple permission paths to an object (e.g., ___ ). If any of these paths are denied, then the user is blocked from accessing the object.

    individually, through a standard database role, and through the public role

    A user may have multiple permission paths to an object (e.g., individually, through a standard database role, and through the public role). If any of these paths are denied, then the user is blocked from ___

    accessing the object.

    Because SQL Server is an environment within the Windows Server system, one of your primary security concerns should be ensuring that the Windows Server itself is ___

    secure.

    Three different types of accounts are available for the SQL Server service account: ___

    Local user account, Local system account, Domain user account.

    A local user account cannot be used outside the ___

    server environment.

    Domain user account is the recommended login account because the SQL Server can then use the ___ specifically created for it.

    Windows account

    Windows Authentication is very robust in that it will authenticate not only Windows users, but also users within ___

    Windows user groups.

    The Windows SID (___) is passed to SQL Server.

    security identifier

    Using Windows Authentication means that users must have a ___ in order to be recognized by SQL Server

    valid Windows account

    Access, roles, and permissions can be assigned to the Windows user group, and they will apply to ___

    any user in that group.

    Local users can be managed by selecting ___

    Control Panel > Administrative Tools > Computer Management.

    Domain users are managed with tools such as the ___

    Active Directory Users and Computers snap-in.

    Once users exist in the Windows user list or the Windows domain, SQL Server can ___

    recognize them.

    To create a login using Transact-SQL syntax so that you can add a Windows user or group run the ___

    CREATE LOGIN command.

    Transact-SQL syntax so that you can add the windows username: cbastiao from domain name: WS2008

    CREATE LOGIN [WS2008\CBASTIAO] FROM WINDOWS

    A Windows login can be removed from SQL Server through SSMS...this doesn't
    delete the user from ___ ; it only removes the user from SQL Server.

    Windows

    To remove a Windows user or group from SQL Server, you can also use the ___

    DROP LOGIN command

    SQL servers also support mixed mode, which allows you to connect to a SQL server using ___

    Windows authentication or SQL Server authentication.

    A SQL Server login account and related passwords are defined on the SQL server and are not related to ___

    Active Directory or Windows accounts.

    Because SQL Authentication is less secure than Windows logins, avoiding mixed mode is ___ ; however, it is available for backward compatibility.

    recommended

    Because SQL Authentication is less secure than Windows logins, avoiding mixed mode is recommended; however, it is available for ___

    backward compatibility.

    There are three kinds of database server roles: ___

    fixed roles, the public role, and user-defined roles.

    There are three kinds of database server roles: fixed roles, the public role, and ___

    user-defined roles.

    Fixed server role: Bulkadmin ???

    Can perform bulk insert operations.

    Fixed server role: Dbcreator ???

    Can create, alter, drop, and restore databases.

    Fixed server role: Diskadmin ???

    Can create, alter, and drop disk files.

    Fixed server role: Processadmin ???

    Can kill a running SQL Server process.

    Fixed server role: Securityadmin ???

    Can manage the logins for the server.

    Fixed server role: Serveradmin ???

    Can configure the server-wide settings, including setting up full-text searches and shutting down the server.

    Fixed server role: Setupadmin ???

    Can configure linked servers, extended stored procedures, and the startup stored procedure.

    Fixed server role: Sysadmin ???

    Can perform any activity in the SQL Server installation, regardless of any other permission setting. The sysadmin role even overrides denied permissions on an object.

    The one user that SQL Server automatically creates during installation of the software is ___

    BUILTINS/Administrator

    The BUILTINS/Administrators user can be deleted or ___

    modified as desired after installation.

    If you add a user to the sysadmin role group, that user must reconnect to the SQL Server instance in order for the full capabilities of ___

    the sysadmin role to take effect.

    Fixed server roles are set in SSMS in the Server Roles page of the ___

    Login Properties dialog box.

    the following code adds the user login ''XPS\Lauren'' to the sysadmin role: ___

    EXEC sp_addsrvrolemember 'XPS\Lauren', 'sysadmin'

    user-defined roles are typically employed for users who need to perform ___ but to whom you don't want to grant a role that would permit them do more than what they need to.

    specific database functions

    user-defined roles are typically employed for users who need to perform specific database functions but to whom you don't want to grant a role that would permit them do ___

    more than what they need to.

    You cannot modify ___ to a fixed server role.

    the permissions assigned

    You cannot modify the permissions assigned to a ___

    fixed server role.

    You can manage database access from either the login side or the ___

    database side.

    You can manage database access from either the ___ or the database side.

    login side

    To grant access to a database from the login side using Object Explorer, use the ___ of the Login Properties form.

    User Mapping page

    To grant access to a database from the login side using Object Explorer, use the User Mapping page of the ___.

    Login Properties form

    To grant access from the database point of view, use the ___ under the Database > Security > Users node to open the Database User-New form.

    New User Context Menu command

    A Transact-SQL command to grant database access to a user: ___

    CREATE USER [LRN]
    FROM LOGIN [WS2008\cbastiao]

    To remove Lauren's database access, the system-stored procedure DROP USER requires her database username, not her ___

    server login name.

    Transact-SQL command to drop user: ___

    DROP USER userTeste

    Any user who wishes to access a database but who has not been declared a user within the database is automatically granted the privileges of the guest user, as long as ___

    the guest user account has been created.

    Any user who wishes to access a database but who has not been declared a user within the database is ___

    automatically granted the privileges of the guest user, as long as the guest user account has been created.

    The guest user account is not actually created when a___

    database is created;

    See More

    Please allow access to your computer’s microphone to use Voice Recording.

    Having trouble? Click here for help.

    We can’t access your microphone!

    Click the icon above to update your browser permissions above and try again

    Example:

    Reload the page to try again!

    Reload

    Press Cmd-0 to reset your zoom

    Press Ctrl-0 to reset your zoom

    It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

    Please upgrade Flash or install Chrome
    to use Voice Recording.

    For more help, see our troubleshooting page.

    Your microphone is muted

    For help fixing this issue, see this FAQ.

    Star this term

    You can study starred terms together

    NEW! Voice Recording

    This is a Plus feature