Question

Let’s assume you are a DBA and you made a whole bunch of configuration changes to the SQL Server instance. Now, let’s assume someone stole your node from the datacenter. You are now rebuilding your SQL Server system on a new machine. How are you going to restore the changes?

A) By keeping a backup of sp_configure commands in a file along with the reasons for the changes
B) By restoring the msdb database from a backup
C) Restoring the master database from a backup
D) Restoring the model database from a backup

Answer 1

To restore configuration changes to a SQL Server instance, you should restore the master database from a backup as it contains all system-level information including configuration settings.

Step-by-step explanation:

If you were a DBA (Database Administrator) and you needed to restore configuration changes made to a SQL Server instance after physical theft of the machine, the correct course of action would be to restore the master database from a backup. The master database holds all the system-level information for an SQL Server instance, including instance-wide configuration settings. Hence, restoring the master database would also restore all the configuration settings to their state at the time of the backup.

Restoring the msdb database would recover jobs, schedules, and other service-specific data, while restoring the model database would affect the template for new databases, but would not restore server-wide configuration changes. Keeping a backup of sp_configure commands in a script file is a good practice for documentation and auditing purposes, but it is not a substitute for actual database backups.