You are creating a SQL Server 2008 Integration Services (SSIS) package on a SQL Server
2008 database for Company.com. In order to develop a failure recovery plan that is
published for a SQL Server. Which is the correct answer?
A.
You should back up the master database.
B.
You should back up the local database.
C.
You should back up the system database.
D.
You should back up the systemdb databse.
Explanation:
Tricky question… Obvious answer is msdb* , but if you read more carefully they are asking
for SQL Server recovery plan, not for SSIS packages… Master: The master database
contains system information and high-level information about all databases on an SQLServer. If the master database becomes damaged, SQL Server may fail to start and user
databases may become unavailable. There are many operations which change the content
of the master database – like creating and altering databases, adding and modifying logins,
creating linked servers etc. But since one cannot keep backing up master after every such
operation, schedule the master database to be backed up on a regular basis (for example:
once every night, or once every week depending on the frequency of such changes to the
database). This will backup the changes made to the user databases and SQL Server, which
can then be recovered in case of a master database corruption. Note: Only full database
backups of master can be performed. Transactional log, differential or filegroup backups of
master are not allowed. Thus if you create a Database Maintenance Plan for all the system
databases or if you select the master database and you select the Back up the transaction
log as part of the maintenance plan option, the backup transaction log step for the master
database will fail with this error message: Msdb: The msdb database is used by SQL Server,
SQL Server Enterprise Manager, and SQL Server Agent to store data, including scheduled
job information, backup and restore history information, DTS packages. Note: You will notice
that, by default, the trunc. log on chkpt database option is set to true, for the msdb database.
This helps ensure that the transaction log of the database does not fill up, and prevents
problems that may occur due to inadequate disk space. Because the msdb database
generally remains rather small, full database backups provide a fast alternative to
transaction log backups for this database. *EXAM TIP When you deploy a package to SQL
Server, the package is stored in the msdb database in a table named dbo.sysssispackages.
Therefore, if you want to back up packages that have been deployed to SQL Server, you can
back up the msdb database. The master database is the most important database of all and
the most recent version of the backup should be available in the case of disaster. Backup of
the master database should be made for:
– Changing server-level configuration settings;
– Changing database-level configuration settings; and
– Changing any logon accounts details.
The master database records all the system-level information for a SQL Server system. This
includes instancewide metadata such as logon accounts, endpoints, linked servers, and
system configuration settings. Also, master is the database that records the existence of all
other databases and the location of those database files and records the initialization
information for SQL Server. Therefore, SQL Server cannot start if the master database is
unavailable. In SQL Server, system objects are no longer stored in the master database;
instead, they are stored in the Resource database. The msdb database is used by SQL
Server Agent for scheduling alerts and jobs and by other features such as Service Broker
and Database Mail.