Test Flashcards
You work as a database administrator at ABC.com. You have just installed Microsoft SQL Server
Management Studio on one of ABC.com’s workstations and the Microsoft Business Intelligence
Development Studio. You have been requested to design a 12 table transactional package to be
stored on an offsite server’s msdb data store.
You decide that BIDS in this scenario is an overkill and you decide to uninstall it. What utility
should you run to create the requested package?
A. Use the bulk copy program.
B. Use the DTS Designer.
C. Use the Microsoft SQL Server Import and Export Wizard.
D. Use Process Control Tool.
E. Use the Package Migration Wizard.
F. Use the Process Control Tool
G. Use the ISIS.
Answer: C. Use the Microsoft SQL Server Import and Export Wizard.
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that contains a database named ABC_DB1. As part of your backup method of ABC_DB1,
you do a Full backup every Sunday at 01:00, you do a differential backup weekdays at 22:00, and
you do transactional log backup weekdays at 08:00; 12:00 and 16:00.
You have received notification that the user database data files have failed on Thursday at 15:00.
You need to ensure that ABC_DB1 are retrieve as quickly as possible with as small as possible
amount of data loss.
What steps should you take? Use only steps that apply.
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that contains a database named ABC_Prod. During the night a daily full backup of
ABC_Prod is started at 03:15, a differential backup is performed every 90 days minutes and a
transaction log backup is performed every 20 minutes.
This particular day your CEO has requested that you make an extra full backup during the lunch
break starting at 11.30. How can you meet her request while keeping the database backup files in
order for future backups and restores?
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. The SQL Server 2008 instance hosts quite a few applications. ABC also has a server
named ABC-SR43 that is used to store log files.
You have to set up a new job that stores log files on ABC-SR43. You create a new account
ABC\Log_Account. You set it up to be run by the SQL Server Agent Services.
However, your job fails to store any log files on ABC-SR43. After some investigation you notice
that the job does not have permissions to store files on ABC-SR43.
How should you remedy the situation? Select the best option.
A. You should set up ABC\Log_Accoun as a Remote Service account.
B. You should set up ABC\Log_Accoun as a Domain service account.
C. You should set up ABC\Log_Accoun as a Local Service account.
D. You should set up ABC\Log_Accoun as a Local System account.
E. You should set up ABC\Log_Accoun as a Network Service account.
F. You should set up ABC\Log_Account as a Domain account.
F. You should set up ABC\Log_Account as a Domain account
Explanation:
The service startup account defines the Microsoft Windows account in which SQL Server Agent
runs and its network permissions. SQL Server Agent runs as a specified user account. For
compatibility with earlier versions of SQL Server, SQL Server Agent can also run as the Local
System account.
In this scenario you should select a Domain account, which allows sufficient permissions and
improved security.
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that has a table named ABC_Training. ABC_ Training contains a column named
ABC_Distance. A new ABC.com training policy states that the training (ie. The ABC_Distance
column in the ABC_Training table) cannot be increased or decreased by more than 3%.
How should you implement the ABC Training Policy?
A. You should consider developing a view which rolls back non-compliant ABC.com policy
changes to ABC_Distance.
B. You should consider developing a stored procedure which rolls back non-compliant ABC.com
policy compliant changes to the ABC_Distance column.
C. You should consider developing a primary key constraint to the ABC_training table which only
contains valid values on ABC_Distance.
D. You should consider developing a create trigger which rolls back non-compliant ABC.com
policy changes to the ABC_Distance column.
E. You should consider developing an update trigger which rolls back non-compliant ABC.com
policy changes to the ABC_Distance column.
F. You should consider developing a delete trigger which rolls back non-compliant ABC.com policy
changes to the ABC_Distance column.
E. You should consider developing an update trigger which rolls back non-compliant ABC.com
policy changes to the ABC_Distance column.
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that contains a database named ABC_DB. ABC.com contains a lot of customer data
which is processed by the Web application. You need to keep the customer information safe since
it is confidential. This information includes files of data, backups, and log files.
How can this be achieved and still keep the performance and functionality of the web application?
Select one or two.
A. You should consider adding a transaction Log on ABC_DB, and set the information to be
encrypted for a fixed time.
B. Use BitLocker Drive Encryption on the hard drives on the SQL Server.
C. Use EFS (Encrypting File System) on the hard drives on the SQL Server.
D. You should consider enabling the Transparent Database Encryption on ABC_DB and back up
the transaction log.
E. You should consider enabling the Transparent Database Encryption on ABC_DB and enabling
the Transparent Database Encryption on master database.
F. Use cell-level encryption for the specific data that needs to be kept safe.
D. You should consider enabling the Transparent Database Encryption on ABC_DB and back up
the transaction log.
Explanation:
With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have
the choice between cell-level encryption as in SQL Server 2005, full database-level encryption by
using TDE, or the file-level encryption options provided by Windows. TDE is the optimal choice for
bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at
the file level, which is similar to two Windows® features: the Encrypting File System (EFS) and
BitLocker™ Drive Encryption, the new volume-level encryption introduced in Windows Vista®,
both of which also encrypt data on the hard drive. TDE does not replace cell-level encryption,
EFS, or BitLocker.
You work as a database administrator at ABC.com. ABC.com is using two SQL Server 2008
sample named ABCSmp1 and ABCSmp2. Furthermore, ABCSmp1 contains a database named
ABC_DB. A ABC.com user named Mia Hamm uses her logon MiaHamm to log on to the database
on ABCSmp1. During the week you have transferred ABC_DB to ABCSmp2. You manually
recreate the Mia Hamm login on ABCSmp2. However, when she tries to login on ABC_DB at
ABCSmp2, she received an error message stating that she is not allowed access. Mia Hamm
needs to access ABC_DB and you need to make it happen.
What T-SQL code should you use? (Each correct option is part of the answer. Choose TWO)
A. Use ABC.com;
B. Use ABC_DB; *
C. Use ABCSmp1;
D. Use ABCSmp2;
E. ALTER LOGIN MiaHamm WITH DEFAULT_DATABASE = ABC_DB;
F. CHANGE LOGIN MiaHamm WITH DEFAULT_DATABASE = ABC_DB;
G. ALTER LOGIN MiaHamm WITH PASSWORD = ‘pwd1234’ UNLOCK;
H. CHANGE LOGIN MiaHamm WITH PASSWORD = ‘pwd1234’ UNLOCK;
I. CHANGE USER MiaHamm WITH LOGIN = MiaHamm;
J. ALTER USER MiaHamm WITH LOGIN = MiaHamm; *
K. ALTER LOGIN MiaHamm ENABLE;
L. CHANGE LOGIN MiaHamm ENABLE;
B. Use ABC_DB; *
J. ALTER USER MiaHamm WITH LOGIN = MiaHamm; *
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance.
Your junior assistant is curious about on how to move a table, such as ABC_table, from its current
scheme (scheme1) to another scheme (scheme2)
How can you accomplish this in T-SQL?
A. You should consider using the following:
ALTER TABLE Schema1.ABC_Table SWITCH TO Schema2.ABC_table;
B. You should consider using the following:
ALTER AUTHORIZATION ON Schema1.ABC_Table TO Schema2;
C. You should consider using the following:
ALTER SCHEMA schema2 TRANSFER schema1.ABC_table;
D. You should consider using the following:
ALTER SCHEMA schema1 TRANSFER schema2.ABC_table;
E. You should consider using the following:
ALTER USER Schema1 WITH DEFAULT_SCHEMA = Schema2;
F. You should consider using the following:
MOVE ABC_Table FROM SCHEMA schema1 TO schema2
G. You should consider using the following:
MOVE ABC_Table FROM schema1 TO schema2
C. You should consider using the following:
ALTER SCHEMA schema2 TRANSFER schema1.ABC_table;
Explanation:
ALTER SCHEMA can only be used to move securables between schemas in the same database. To change or drop a securable within a schema, use the ALTER or DROP statement specific to that securable.
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. You have received instructions from management to allow the users at ABC.com on the
SQL Server sample to use the OPENROWSET() function to search remote information sources.
You study incomplete code in the exhibit carefully. (Line numbers are used for reference purposes
only)
Exhibit:
01. sp_configure ‘show advanced options’, 1
02. RECONFIGURE
03.
04. RECONFIGURE
05. GO
06.
07. SELECT a.*
08. FROM OPENROWSET(‘SQLNCLI’, ‘Server=Madrid;Trusted_Connection=yes;’,
09. ‘SELECT GroupName, Name, DepartmentID
10. FROM AdventureWorks.HumanResources.Department
11. ORDER BY GroupName, Name’) AS a;
12. GO
Which T-SQL statement should you insert at line 03 to make the batch meet the requirement in
this scenario?
A. sp_configure ‘Router control’, 1
B. sp_configure ‘Router control’, 0
C. sp_configure ‘Transaction Logs’, 1
D. sp_configure ‘Transaction Logs’, 0
E. sp_configure ‘Ad Lib Distributed Queries ‘, 1
F. sp_configure ‘Ad Lib Distributed Queries ‘, 0
G. sp_configure ‘Ad Hoc Distributed Queries’, 1
H. sp_configure ‘Ad Hoc Distributed Queries’, 0
G. sp_configure ‘Ad Hoc Distributed Queries’, 1
Explanation:
By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and
OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this
option is not set or is set to 0, SQL Server does not allow ad hoc access.
Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to
connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE
should be used only to reference OLE DB data sources that are accessed infrequently. For any
data sources that will be accessed more than several times, define a linked server.
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. The a SQL Server 2008 instance is used on a Windows Server 2000 server and uses
the mixed authentication mode. Management wants you to ensure the following:
• SQL Server 2008 logins must have the same password complexity rules which are enforced by
Windows Server 2000 for authentication.
• Full compliance of all users with regarding to adhering to the password complexity rules.
What actions should you take?? (Each correct option is part of the answer. Choose TWO)
A. You should consider using the ALTER LOGIN … CHECK_EXPIRATION = OFF statement to
change the entire login of all users.
B. You should consider using the ALTER LOGIN … CHECK_EXPIRATION = ON statement to
change the entire login of all users.
C. You should consider using the command ALTER LOGIN … NO CREDENTIAL.
D. You should consider using the command ALTER LOGIN … UNLOCK.
E. You should consider using the command ALTER LOGIN … ADD CREDENTIAL.
F. You should consider using the command ALTER LOGIN … DROP CREDENTIAL.
G. You should consider using the ALTER LOGIN … CHECK_POLICY = ON statement to change
the entire login of all users.
H. You should consider using the ALTER LOGIN … CHECK_POLICY = OFF statement to change
the entire login of all users.
I. Create a policy using Policy-Based Management that matches the requirements of this scenario.
J. You should consider using the ALTER LOGIN … CREDENTIAL = statement to change the
entire login of all users.
G. You should consider using the ALTER LOGIN … CHECK_POLICY = ON statement to change
the entire login of all users.
I. Create a policy using Policy-Based Management that matches the requirements of this scenario.
Explanation:
ALTER LOGIN
CHECK_POLICY = { ON | OFF }
Applies only to SQL Server logins. Specifies that the Windows password policies of the computer
on which SQL Server is running should be enforced on this login. The default value is ON.
Policy-Based Management is a system for managing one or more instances of SQL Server 2008.
When SQL Server policy administrators use Policy-Based Management, they use SQL Server
Management Studio to create policies to manage entities on the server, such as the instance of
SQL Server, databases, or other SQL Server objects.
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 Server.
You are requested to make it easier to export data from the database to Microsoft Excel 2007
format. In particular you want to be able run a command from the command line that exports the
data to Excel format.
What steps should you take?
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 instance. ABC.com contains a SQL Server 2008 server named ABC-DB01. There are also SQL Server Agent jobs on the server. Furthermore the database is backed up at a daily basis. One morning, the master database on ABC-DB02 went down. You then restore and rebuild the master database. The ABC.com managers want ABC-DB01 to work as it was in the past.
What steps should you take? (Each correct option is part of the answer. Choose TWO)
A. You should consider rebuilding the entire SQL Server Web Service for the database.
B. You should consider reserving the model database for the database.
C. You should consider reserving the Resource databases for the database.
D. You should consider reserving the tempdb databases for the database.
E. You should consider reserving the master databases for the database.
F. You should consider reserving the msdb databases for the database.
G. You should consider reserving all the Transaction Logs for the database.
H. You should consider starting from scratch and rebuilding the database of all users’ memories.
B. You should consider reserving the model database for the database.
E. You should consider reserving the master databases for the database.
You work as a database administrator at ABC.com. ABC.com is using SQL Server 2008. ABC.com contains a SQL Server 2008 server named ABC-DB04. ABC-DB04 will be used to run the Reporting Services. However, the Reporting Services must be able to run the database backups and other maintenance job automatically. Because ABC.com has no other SQL systems, you need to make use of the SQL’s DNS name.
What steps should you take?
A. You should consider using the SQL Server Web Service and the Microsoft Distributed Transaction Coordinator technology.
B. You should consider using the SQL Server Browser and the SQL Server Web Service technology.
C. You should consider using the Microsoft Distributed Transaction Coordinator and the SQL Server Browser technology.
D. You should consider using the Internet Information Services and the Microsoft Distributed Transaction Coordinator technology.
C. You should consider using the Microsoft Distributed Transaction Coordinator and the SQL Server Browser technology.
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. You have received instruction from management to migrate a web application from
Microsoft SQL Server 2005 to Microsoft SQL Server 2008. Be reminded that when a web
application is moved then some features could stop working. You need to detect such instances.
What action should you take? (Each correct option is part of the answer. Choose TWO)
A. You should consider using a SQL client-side trace.
B. You should consider using a stored procedure.
C. You should consider using server side Transaction Log.
D. You should consider using a SQL server-side trace.
E. You should consider using SQL Server 2008 UABCrade Advisor.
F. You should consider using a SQL Server Profiler.
G. You should consider capturing the Deprecation Announcement and Deprecation Final Support
event classes.
H. You should consider capturing the SQL:BatchCompleted and Exception event classes.
D. You should consider using a SQL server-side trace.
G. You should consider capturing the Deprecation Announcement and Deprecation Final Support
event classes.
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. Your junior assistant is interested in the default trace log. What T-SQL command should
she use to open this log file if we assume it is stored in the default location?
A. SELECT *
FROM fn_trace_gettable
(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\TRACE
LOG\log.trc’,
default)
GO
B. SELECT *
FROM fn_trace_gettable
(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\log.trc’,
default)
GO
C. SELECT *
FROM fn_trace_gettable
(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\log.trc’,
default)
GO
D. SELECT *
FROM fn_trace_gettable
(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\TRANSACTION
LOG\log.trc’,
default)
GO
E. SELECT *
FROM fn_trace_gettable
(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\TRACE\log.trc’,
default)
GO
F. SELECT *
FROM fn_trace_gettable
(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\BACKUP\log.trc’,
default)
GO
C. SELECT *
FROM fn_trace_gettable
(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\log.trc’,
default)
GO
Explanation:
The correct subdirectory is named LOG.
The following statement opens the default trace log in the default location:
SELECT *
FROM fn_trace_gettable
(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\log.trc’,
default)
GO
