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 SQL Server. 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 instance-wide 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.