Braindumps of 70-431
Microsoft SQL Server 2005 Implementation & Maintenance
Exam Questions, Answers,
Hi to all and thanks to
www.4exam.com But there's no need to have it from
both of them, only questions from only one are
sufficient I think. Here is my contribution.
application must access data that is located on two
SQL Server 2005 computers. One of these servers is named
SQL1 and the other is SQL2. You have permissions to
create a stored procedure on SQL1 to support your application.
However, on SQL2 you only have permissions to select
data. You write the stored procedure on SQL1. The stored
procedure accesses SQL2 by using the OPENQUERY Transact-SQL
statement. However, the query fails when executed. You
need to troubleshoot the cause of the error. What should
Join the two servers by using the four-part syntax of
Reference SQL2 by using an alias.
SQL2 as a remote server to SQL1.
SQL2 as a linked server to SQL1.
are preparing for a new installation of SQL Server 2005.
You need to select the protocols that client computers
might use to connect to the server. Which two protocols
can you use to achieve
this goal? (Each correct answer presents a complete
solution. Choose two.)
Virtual Interface Adapter (VIA)
Answer: A, B
configure a new SQL Server 2005 computer to use TCP/IP
with all default settings. Your corporate policy requires
that each server use a firewall. You find that you can
connect to the SQL Server instance from the local computer.
However, client computers cannot connect to the SQL
Server instance. You need to identify the most likely
cause of the connection issues. What should
you do first?
Ensure that port 1433 is open in your firewall.
Ensure that port 443 is open in your firewall.
Ensure that client computers connect by using Shared
Ensure that the server is not paused.
has multiple servers in a distributed environment. You
work with two SQL Server 2005 computers named SQL1 and
SQL2. Each server uses SQL Server Authentication and
they use different logins. You need to write a distributed
query that joins the data on SQL1 with the data on SQL2.
What should you do?
Ensure that both SQL1 and SQL2 use the same login name
as the security context for each server.
Configure SQL2 as a remote server. Write the query on
Configure SQL2 as a linked server to impersonate the
Configure SQL2 as a distributed server. Use pass-through
uses SQL Server 2005. Users report that report execution
is slow. You investigate and discover that some queries
do not use optimal execution plans. You also notice
that some optimizer statistics are missing and others
are out of date. You need to correct the problem so
that reports execute more quickly. Which two Transact-SQL
statements should you use?
(Each correct answer presents part of the solution.
ALTER INDEX REORGANIZE
Answer: C, D
responsible for implementing maintenance jobs on a SQL
Server 2005 database server.
jobs run every Sunday and other jobs run at the beginning
of every month. You need to schedule the jobs in the
way that uses the least amount of administrative effort.
What should you do?
Create a job schedule that runs every Sunday. Assign
weekly tasks to this schedule. Create a second
schedule that runs on the first day of every month.
Assign monthly tasks to this schedule.
Create a job for each task that runs once a day. Use
a Transact-SQL statement to check the date and day of
the week. If the day is either a Sunday or the first
day of the month, execute the code.
Create a job schedule that runs once a day. Assign jobs
to this job schedule. If the day is either a Sunday
or the first day of the month, execute the jobs.
Create a job for each task that runs once a week on
Sunday. Add a second job schedule that runs the job
on the first of the month.
discover that the msdb database on a SQL Server 2005
computer is corrupt and must be restored. Databases
are backed up daily. The database backup files are written
to a network share, but the file names do not clearly
indicate which databases are in each file. You need
to locate the correct backup file as quickly as possible.
The first file in the list is named
DB_Backup.bak. Which Transact-SQL statement should you
RESTORE LABELONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
RESTORE VERIFYONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
RESTORE DATABASE MSDBFROM DISK = N\\Server1\Backup\DB_Backup.bak
engineer reports that inserting new sales transactions
in a SQL Server 2005 database results in an error. You
investigate the error. You discover that in one of the
developer has accidentally deleted some data in a table
that is critical for transaction processing.
database uses the full recovery model. You need to restore
the table. You need to achieve this goal without affecting
the availability of other data in the database. What
should you do?
Back up the current transaction log. Restore the database
with a different name and stop at the point just before
the data loss. Copy the table back into the original
up the current transaction log. Restore the database to
the point just before the data loss.
Restore the database from the existing backup files to a
time just before the data loss.
Restore the database to the point of the last full
failure occurs on the storage area network (SAN) where
your SQL Server 2005 database server is located. You
need to check the allocation as well as the structural
and logical integrity of all databases,
including their system catalogs. What should you do?
Execute DBCC CHECKFILEGROUP for each filegroup.
Execute DBCC CHECKCATALOG.
Execute DBCC CHECKDB.
Execute DBCC CHECKTABLE for each table.