SQL Server Instance Level Objects Backups
One of the important and common responsibility of a SQL Server DBA is to make sure a perfect backup/recovery plan is in place and that the plan can be implemented as per the SLA’s. Periodic testing is important to ensure that the plan works in case of unexpected disaster. In most of the environments, the backups of SQL Server databases are performed which include system and user databases, however there can be many scenarios where in one of the database may be corrupted and has to be recovered, in which case the restore of the database backups works great, but in some cases it is also a possibility that certain server level objects have to be recovered and restoring system databases is not an option and a time taking option. In such cases have backup of server level objects separately will serve greatly.
Thus it becomes important for a DBA to also consider performing backups of instance level objects periodically, so that the level of restores or control on the objects which required to be restored will be available. Also this will act as a secondary option in case the primary option of system database backups does not work in some situations. Below are the important SQL Server instance level objects which are to be backed up regularly and can be used later when required.
– SQL Server Instance Configuration properties: these setting can be backed up by saving the output of sp_configure values or the values of select * from sys.sysconfigurations.
– Database properties: The database properties gets backed up by the database backups, however saving these settings in a text document will be useful to keep track of settings and can be verified or compared if the values are changed by someone and we had to identify what were the values DBA team planned for.
– DDL triggers: There are server level triggers which should be backed up and will be useful if someone deletes the triggers.
– Backup Devices: During a backup operation on a SQL Server database, the backed up data (the backup) is written to a physical backup device. This physical backup device is initialized when the first backup in a media set is written to it. The backups on a set of one or more backup devices compose a single media set.
– Endpoints: SQL Server endpoint is the point of entry into SQL Server. It is implemented as a database object that defines the ways and means in which SQL Server may communicate over the network. SQL Server routes all interactions with the network via endpoints and each endpoint supports a specific type of communication.
– SQL Server Agent Properties: These settings are important and the backup of these settings are useful in cases where these values are changed by someone and we want to know what are the correct ones.
– SQL Server Agent Jobs: Very important to have backup of the jobs, so that we can recreate them easily without having to restore the MSDB database. It is important to keep in mind that some jobs will run DTS/SSIS packages which have be backed up separately where the packages have to be saved separately.
Linked Servers: Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.
SQL Server Logins: Logins allow access to users to connect to SQL Server, so important to keep a backup, so that we can recreate the required logins if they are accidentally deleted.
Other SQL Server instance level objects which can be backed up include SQL Server Agent operators, SQL Server Agent alerts, SQL Server Server Roles, SQL Server Audits, etc
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings