SQL Server Instance Level Objects Backups

SQLServerF1

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

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

3rd Party Backup and Restore/Recovery Tools for SQL Server

SQLServerF1

One of the most important responsibility of SQL Server Database Administrators (DBAs) is planning, implementing and testing Backups and Restore/Recovery in SQL Server. There could be sudden disaster or corruption in which case the database needs to be restored and recovered with minimal data loss, thus it make the planning and testing of Backup and Restore process very important.

SQL Server ships with database backup capabilities, but it is on server to server basis and there is no central solution where backups and restores can be managed from a central server. Thus there are various Backup, Restore and Recovery tools for SQL Server are available in the market with various features. More information regarding choosing best backup/restore tool can be found in this article

Below are some of the popular Backup and Restore/Recovery 3rd party tools for SQL Server.

NetVault LiteSpeed from Quest – LiteSpeed tool is one of the most mature, feature-rich SQL Server backup products available. It is very widely used around the world by many organizations in almost all the industries. They also provide an excellent support and regular updates to their product. It provides integration with TSM, Adjustable Backup compression ratios, backup Encryption, • Central backup repository, supports Object level restores, etc. There are some discounts of about 20-20% offered for bulk licenses. Price is on higher side, but has many good features and is a reliable product.

SQL Safe from Idera – SQL Safe is cost effective with the features provided. This tool supports TSM integration, and is capable of object-level restores, central management for backups, etc. Object level recovery is not supported with this tool.

SQL Backup and HyperBac from RedGate – RedGate has two backup/restore products which are SQL Backup and HyperBac. same licensing applies for both the products. These are highly cost effective backup products available in the market and can be bundled with other tools in your environment. There are some limitations to the tools as it does not provide TSM integration.

There are other backup/restore tools available from CommVault which is Simpana, “SQL Server Backup” from DataBK.

What does SQL Server Backups Consists of? – SQL Server Backups copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup. This copy of SQL Server data can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. Recovery model of the database determines what kind of Log backups can be performed on a database, thus affects the restore process and amount of data that can be recovered after a failure.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Tips to Choose Best SQL Server Third-Party Backup and Restore Products or Tools

SQLServerF1

Backups in SQL Server are one of the most important responsibility of SQL Server Database Administrators (DBAs). There could be sudden disaster or corruption in which case the database needs to be restored and recovered with minimal data loss, thus it make the planning and testing of Backup and Restore process very important.

What are SQL Server Backups: – SQL Server Backups copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup. This copy of SQL Server data can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. Recovery model of the database determines what kind of Log backups can be performed on a database, thus affects the restore process and amount of data that can be recovered after a failure.

Although Backups and restores can be performed using SQL Server Management Studio (SSMS) and using T-SQL queries, but in many environments, to make the Backup and Restore process more manageable they use Third-Party Backup tools to perform backups and restores on SQL Server databases. Although there are many third-tools it is important to choose the tools which suits best for our environment.

Below are some of the parameters which can be used to determine the best Backup and Restore/Revoery tools for your environment.

Integration with TSM – One of the important part of a Backup/Testore product is its integration with the TSM which allows you to backup and restore directly to the TSM Server. Backing up directly to TSM allows saving disk space and IO bandwidth savings. Another benefit is that the direct restores can be performed on to other servers which saves time and space.

Backup Compression – Starting with SQL Server 2008, backup compression feature was introduced. However backup tools provide their own compression methods where in the backups can be further compressed and also backups are compressed on SQL Server versions which does not have this feature available.

Backup Encryption – Encryption of SQL Server Backups was introduced with SQL Server 2014, but there are many backup tools which provide custom encryption features which encrypts and stores the SQL Server backups.

Usability – Although various backup and restore tools provide variety of features, but it makes lot of difference when it comes to the ease at which DBAs can install, configure, manage and use these tools. If Backup tool installation or configuration is complicated then it will not be adopted by DBAs as they will not be comfortable using it or will have to spend lot of time on managing it.

Product Support – One of the key factor evaluated before making a decision to but the Backup or Restore tools is the level and kind of support option available for the product. Some of the tools which provide very good support often are mostly used in the organizations.

Price/Cost of the Product – Depending on the features provided the price or cost of the tools vary. This is a very important factor to decided weather to go ahead in buying the product. Over a period of time many new backup and restore tools were developed by various organizations which brought more competition this reduced the cost or price of these tools. Also various discounts are provided based on number of licenses brought and kind of servers the tools are being used in.

Degree of Restores – SQL Server provided Backups or restores to be performed at database, files or filegroup level, but the 3rd party backup and restore tools along with standard will also support object level restores, where in one can restore a particular table or stored procedure, which is more useful and helpful in Development or Test environments.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.