Microsoft 070-457 Value Pack (Frequently Bought Together)

You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:

Id is the Primary Key.
You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries
based on the values in EntryDateTime. Which Transact-SQL statement should you use?

• A. UPDATE TOP(10) BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0)
• B. UPDATE BlogEntry SET Summary = CAST(N' This is in a draft stage' as nvarchar(max)) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
• C. --this option was diferent im my exam UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', 0, 0) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
• D. UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM ( SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

You create a view named VwEmployee as shown in the following Transact-SQL statement.

Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view. Which Transact-SQL statement should you use?

• A. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName FROM VwEmployee
  INSERT INTO Employee(PersonID, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM VwEmployee
  End
• B. CREATE TRIGGER TrgVwEmployee ON VwEmployee FOR INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted
  INSERT INTO Employee(PersonId, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM inserted
  END
• C. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN
  DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15)
  SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName,
  @EmployeeNumber = EmployeeNumber
  FROM inserted
  INSERT INTO Person(Id, FirstName, LastName)
  VALUES(@ID, @FirstName, @LastName)
  INSERT INTO Employee(PersonID, EmployeeNumber)
  VALUES(@PersonID, @EmployeeNumber
  End
• D. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted
  INSERT INTO Employee(PersonId, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM inserted
  END

You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and backup schedule are configured as shown in the following table: At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database. You need to ensure that the databases are restored. You also need to ensure that data loss is minimal. What should you do?

• A. Restore the latest full backup. Then, restore the latest differential backup.
• B. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• C. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
• D. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
• E. Perform a partial restore.
• F. Restore the latest full backup.
• G. Perform a point-in-time restore.
• H. Perform a page restore.

You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured.
When SQL Server Agent jobs fail, the error messages returned by the job steps are truncated. The
following error message is an example of the truncated error message:
"Executed as user CONTOSO\ServiceAccount. ...0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-
2011. All rights reserved. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source
UserImport Description Code 0x00000000 Source Log Import Activity Descript... The package execution
fa... The step failed."
You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs.
What should you do?

• A. Expand agent logging to include information from all events.
• B. Configure output files.
• C. Configure event forwarding.
• D. Disable the Limit size of job history log feature.

You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. Which type of user should you create?

• A. User mapped to an asymmetric key
• B. login mapped to a virtual account
• C. Domain user
• D. SQL user without login