Latest MCTS 70-432 Dumps Real Exam Download 121-130

Ensurepass

QUESTION 121

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named DB1 in the instance. You have set the recovery model of DB1 to Full. You

deploy a new process. The process modifies 10,000 records from the Accounts table at 18:00 every day.

Any modification made by the process to the data must be reverted and database keeps online. You must

make sure of this.

So which strategy should you implement?

 

A. You should implement Differential backup

B. You should implement Database snapshots

C. You should implement Primary filegroup backup

D. You should implement Transaction log backup

 

Correct Answer: B

Explanation/Reference:

A database snapshot is a read-only, static view of a database (called the source database). Each database snapshot is transactionally consistent with the source database at the moment of the snapshot’s creation. When you create a database snapshot, the source database will typically have open transactions. Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transactionally consistent. Clients can query a database snapshot, which makes it useful for writing reports based on the data at the time of snapshot creation. Also, if the source database later becomes damaged, you can revert the source database to the state it was in when the snapshot was created. Reasons to take database snapshots include: Maintaining historical data for report generation. Because a database snapshot provides a static view of a database, a snapshot can extend access to data from a particular point in time. For example, you can create a database snapshot at the end of a given time period (such as a financial quarter) for later reporting. You can then run end-of-period reports on the snapshot. If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizational performance. Using a mirror database that you are maintaining for availability purposes to offload reporting. Using database snapshots with database mirroring permits you to make the data on the mirror server accessible for reporting. Additionally, running queries on the mirror database can free up resources on the principal. For more information, see Database Mirroring and Database Snapshots. Safeguarding data against administrative error.

Before doing major updates, such as a bulk update or a schema change, createa database snapshot on the database protects data. If you make a mistake, youcan use the snapshot to recover by reverting the database to the snapshot.Reverting is potentially much faster for this purpose than restoring from abackup; however, you cannot roll forward afterward.

 

 

QUESTION 122

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named DB1 in the instance. There is a database named DB1 in the instance.

The backup strategy for DB1 has the following requirements: every day at 9:00 full database backup to a

file named cash.bak is performed; at 9:15 a transaction log backup to a file named cash_HHMM.trn is

performed every 15 minutes.

DB1 is being used in single-user mode.

Today a user reports that a query deleted some data by accident, the query was executed at 9: 25. Since

the deleted data is quite important, the data has to be restored to its original state. You must achieve this

goal.

So what action should you perform to achieve this goal?

 

A. RESTORE DATABASE Cash FROM DISK = ‘t:backupscash.bak’ WITH NORECOVERY; RESTORE LOG Cash FROM ‘t:backupscash_0930.trn’ WITH RECOVERY, STOPAT = ‘Mar 17, 2008 9:25AM’;

B. RESTORE DATABASE Cash FROM DISK = ‘t:backupscash.bak’; RESTORE LOG Cash FROM ‘t:backupscash_0915.trn’ WITH NORECOVERY; RESTORE LOG Cash FROM ‘t:backupscash_0930.trn’ WITH RECOVERY, STOPAT = ‘Mar 17, 2008 9:25AM’;

C. RESTORE DATABASE Cash FROM DISK = ‘t:backupscash.bak’; RESTORE LOG Cash FROM ‘t:backupscash_0915.trn’ WITH NORECOVERY; RESTORE LOG Cash FROM ‘t:backupscash_0930.trn’ WITH RECOVERY;

D. RESTORE DATABASE Cash FROM DISK = ‘t:backupscash.bak’ WITH NORECOVERY; RESTORE LOG Cash FROM ‘t:backupscash_0915.trn’ WITH NORECOVERY; RESTORE LOG Cash FROM ‘t:backupscash_0930.trn’ WITH RECOVERY, STOPAT = ‘Mar 17, 2008 9:25AM’;

 

Correct Answer: D

Explanation/Reference:

To restore this database to 9:25 AM, you have to :

1.

 Restore the full backup with the NORECOVERY option.

2.

 Restore the log backup of 9:15 AM with the NORECOVERY option.

3.

 Restore the log backup of 9:30 AM with the RECOVERY and STOPAT = ‘Mar 17, 2008 9:25AM’ option.

 

 

QUESTION 123

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the

administrator of the company database.

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance. At

present about 90 GB of data is stored in DB1.

You made some rough calculations that every day about 1 GB of data is modified or inserted. You set the

recovery model of DB1 to Simple. According to the company requirements, during business hours, data

loss of more than 120 minutes worth of transactions is unacceptable.

You have to choose a backup strategy which must comply with the following two requirements:

Using as little disk space as possible

Complying with the company requirements.

So what action should you perform to achieve this goal?

 

A. A full database backup should be performed once daily. During business hour, a transaction log backup should be performed every two hours.

B. A full database backup should be performed once daily

C. A full database backup should be performed once daily. During business hours, a differential backup should be performed every two hours

D. Perform a full database backup should be performed once every week. A differential backup should be performed once daily. During business hours a transaction log backup should be performed every two hours.

 

Correct Answer: C

Explanation/Reference:

From the question, we could have the following information:

4.

 As the database recovery model is set to SIMPLE, you cannot create log backups.

5.

 Data loss is 120 min, thus two hours

6.

 Full backup every two hours will lead us to missuse of disk space.

7.

 Differential database backup will keep track of all the changes in the database since the latest full

backup. So the latest differential backup will have a maximal size of 1GB.

Thus, the backup strategy will be: one full backup daily and one differential backup every two hours.

 

 

QUESTION 124

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named DB1 in the instance. The latest differential backup is performed at 15:30, the

full back up was performed at 13:30, and database snapshots were created at 16:30 and at 17:30.

The backups and the database snapshots are stored on a different disk from the database files. At 17:05,

the hard disk containing the database files fails at 17:02 hours.

You must restore DB1 while reduce data loss to the least.

So what action should you perform to achieve this goal?

 

A. You should restore the full backup

B. You should restore the database snapshot from 16:30 hours.

C. You should restore the database snapshot from 17:30 hours

D. You should restore both the full backup and the differential backup.

 

Correct Answer: D

Explanation/Reference:

You can’t revert from the database snapshot because a snashot is a view of the db and stores only the

pages that where changed since the snapshot was taken.

Thus, the only way to get back the database is to restore the database full backup of 13:30 and the

differential backup of 15:30.

There will be a gap of 1 hour 32 minutes.

 

 

QUESTION 125

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named DB1 in the instance. There are data file and the transaction log file on the E:

drive. Now you find that there is only 6% spare space. Both files have to be moved to the V: drive.

Which procedure should you use?

 

A. You should run the following Transact-SQL statement. ALTER DATABASE DB1 SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Data, FILENAME = ‘v:SQLServerDB1_Data. mdf’); ALTER DATABASE DB1 SET MULTI_USER;

B. You should terminate the SQL Server Service. Then move the data file and transaction log file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_DB @DBname = N’DB1′,@filename1 = N’v:SQLServerDB1_Data.mdf’,@filename2 = N’v:SQLServerDB1_Log.ldf’;

C. You should run the following Transact-SQL statementALTER DATABASE DB1 SET OFFLINE WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Data, FILENAME = ‘v:SQLServerDB1_Data. mdf’); ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Log, FILENAME = ‘v:SQLServerDB1_Log. ldf’); ALTER DATABASE DB1 SET ONLINE;

D. You should terminate the SQL Server service. Then move the data file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_single_file_DB @DBname = N’DB1′,@physname = N’v:SQLServerDB1_Data.mdf’;

 

Correct Answer: C

Explanation/Reference:

The two answers with the attach database are wrong because there is NO detach action before the attach

statement.

 

 

QUESTION A is wrong because it moves only the data file (mdf). Or we should move the mdf and ldf files.

So, answer C is correct, because the two files are correctly moved.

 

 

QUESTION 126

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 Enterprise Edition instance.

There is a database named DB1 in the instance. A backup of DB1 is performed every day. You have to

minimize the size of the full database backup files of DB1.

In the options below, which Transact-SQL statement should you use?

 

A. BACKUP DATABASE DB1 TO DISK = ‘t:backupsdb1.bak’;

B. BACKUP DATABASE DB1 TO DISK = ‘t:backupsdb1.bak’ WITH COMPRESSION;

C. BACKUP DATABASE DB1 TO DISK = ‘t:backupsdb1.bak’ WITH DIFFERENTIAL;

D. BACKUP DATABASE DB1 TO DISK = ‘t:backupsdb1.bak’ WITH COMPRESSION, DIFFERENTIAL;

 

Correct Answer: B

Explanation/Reference:

We will use the compression option to minimize the FULL backup size. So, answer D is giving us one differential backup COMPRESSION: Backup compression was introduced in SQL Server 2008 Enterprise. This topic discusses the basics of backup compression, including the performance trade-off of compressing backups.

Note:

Though creating compressed backups is supported only in SQL Server 2008 Enterprise and later, every SQL Server 2008 or later edition can restore a compressed backup.

Restrictions

The following restrictions apply to compressed backups:

Compressed and uncompressed backups cannot co-exist in a media set.

Previous versions of SQL Server cannot read compressed backups.

NTbackups cannot share a tape with compressed SQL Server backups.

 

 

QUESTION 127

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named DB1 in the instance. When you are absent, a user will use a login named Mary

to log in and maintain the database snapshots. The user has to delete the database snapshots for DB1, so

you have to give the appropriate permissions to the user.

So which database permission should you give the user?

 

A. DELETE

B. CONTROL

C. DROP DATABASE

D. ALTER ANY DATASPACE

 

Correct Answer: C

Explanation/Reference:Dropping a Database Snapshot

Dropping a database snapshot deletes the database snapshot from an instance of SQL Server and deletes the physical NTFS File System sparse files used by the snapshot. For information about using sparse files by database snapshots, see How Database Snapshots Work. Dropping a database snapshot clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: “SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations”. This message is logged every five minutes as long as the cache is flushed within that time interval.

Permissions To execute DROP DATABASE, at a minimum, a user must have CONTROL permission on the database. So, for me, the answer is B, not C

 

 

QUESTION 128

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named DB1 in the instance. The DB1 database includes spatial data types. On DB1

database, you have to perform a database consistency check to include the spatial indexes.

Besides this, you must make sure that you can reduce the effect on the database concurrency to the

least.

So which Transact-SQL statement should you run?

 

A. DBCC CHECKCATALOG (DB1);

B. DBCC CHECKALLOC (DB1) WITH TABLOCK;

C. DBCC CHECKDB (DB1) WITH TABLOCK, PHYSICAL_ONLY;

D. DBCC CHECKDB (DB1) WITH EXTENDED_LOGICAL_CHECKS;

 

Correct Answer: D

Explanation/Reference:

DBCC CHECKDB Checks the logical and physical integrity of all the objects in the specified database.

EXTENDED_LOGICAL_CHECKS

If the compatibility level is 100 (SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.

 

 

QUESTION 129

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

A SQL Server Agent job is failing. You find that the job history information is incomplete and seems to be

truncated after you review it. All information produced by a job must be available for viewing.

So what action should you perform to achieve this goal?

 

A. You should enable notifications to the Windows application event log when the job completes.

B. You should enable write OEM file

C. You should enable all job steps, making them send the output to a file

D. You should include execution trace messages in the SQL Agent Error log

 

Correct Answer: C

Explanation/Reference:

In all the jobsteps, go to the option panel and select Output file to get all info from the step. Note that you

can append or overide the file. All job steps should be adapted with the option.

Output file Sets the file to use for output from the job step.

 

 

QUESTION 130

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

The instance contains a database named DB1. An application which is continuously connected uses DB1.

The application extensively uses the INSERT command and triggers the population of multiple tables. Now

you notice that the application has a poor performance. You doubt that this problem has something to do

with blocking.

You have to monitor the state of the instance at regular intervals while not affecting the application

performance further.

Which tool should you use?

 

A. You should use Dynamic Management Views

B. You should use SQL Server Resource Governor

C. You should use SQL Server Profiler

D. You should use Windows System Monitor

 

Correct Answer: A

Explanation/Reference:

Dynamic management views and functions return server state information that can be used to monitor the

health of a server instance, diagnose problems, and tune performance.

Important: Dynamic management views and functions return internal, implementation-specific state data.

Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic

management views and functions in future releases may not be compatible with the dynamic management

views and functions in SQL Server 2008.

There are two types of dynamic management views and functions:

Server-scoped dynamic management views and functions. These require VIEW SERVER STATE

permission on the server.

Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE

permission on the database.

 

Download Latest MCTS 70-432 Real Free Tests , help you to pass exam 100%.