Question 1.
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

Explanation:

Question 2. DRAG DROP
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.

Answer:

Explanation:

Question 3. DRAG DROP
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?

Answer:

Explanation:
By using copy_only you do not affect the backup sequence.

Incorrect answer:
trn' WITH DIFFERENTIAL;
by doing this you already affected your backup sequence and you are only backing up the transaction log.

Question 4.
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.

Answer: F

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.

Question 5.
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.

Answer: E

Explanation:

Question 6.
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.

Answer: D

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.

Question 7.
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;

Answer: B, J

Explanation:

Question 8.
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

Answer: C

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. 

Question 9.
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

Answer: G

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.

Question 10.
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.

Answer: G, I

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.

	Web www.certsbraindumps.com