Version 1.
0
July 2022
Protect SQL Server System
Databases with Cohesity
Cohesity Solution for Backup and Restore of SQL
Server System Databases
ABSTRACT
As databases continue to grow in number and size, data centers in today’s organizations need different
methods and strategies to protect their databases and manage their growing data. One important
strategy is having the ability to backup and restore SQL Server system databases. Restoring SQL
Server system databases is an important function for complete data protection.
2
Table of Contents
Saving a SQL Server Instance .............................................................................. 3
Steps to Protect SQL System Databases ............................................................. 4
Register SQL Server in Cohesity .......................................................................... 5
Create a Protection Group .................................................................................... 8
Recover SQL Server System Database .............................................................. 12
Upgrade Your Disaster Recovery Preparedness ................................................ 15
Take Local Snapshots ............................................................................................. 15
Replicate Backups Off-Site ...................................................................................... 16
Archive Backups to the Cloud .................................................................................. 16
Best Practices for Cohesity VDI-Based SQL Server Protection.......................... 17
Appendix A: Terminology .................................................................................... 19
Appendix B: Product Documentation .................................................................. 20
Your Feedback .................................................................................................... 21
About the Authors ................................................................................................ 21
Document Version History................................................................................... 21
Figures
Figure 1: Set Up SQL Server Data Protection with Cohesity........................................... 4
Figure 2: SQL Backups in Cohesity are Available to Replicate and Archive ................. 15
Figure 3: Cohesity CloudArchive, Cloud Recover, and CloudRetrieve Provide Disaster
Recovery ....................................................................................................................... 16
Send Feedback Protect SQL Server System Databases with Cohesity
3
Saving a SQL Server Instance
SQL Server master database corruption is one of the worst situations any SQL DBA can face.
You may need to recover your master database for several reasons. For example, you may have
accidentally removed something critical like a login, linked server, or some other system object.
Other reasons to restore the master database include recovering a dropped login or a database that
became corrupt as a result of a sudden power failure.
You can restore the damaged master database if you have a backup of your database. Make sure that
the backup database has the same SQL Server version as the corrupt database.
Send Feedback Protect SQL Server System Databases with Cohesity
4
Steps to Protect SQL System Databases
To take full advantage of the many features of Cohesity’s solution, be sure you understand each step of
the implementation.
To protect your SQL databases using Cohesity:
1. Install and deploy Cohesity’s Windows Agent on your SQL Server.
2. Register your SQL Server as a Cohesity Source.
3. Create a Cohesity Protection Group to specify the SQL data you need to protect.
4. Recover protected SQL Server databases.
5. Upgrade your disaster recovery (DR) plan to improve your enterprise’s readiness and resilience.
Figure 1: Set Up SQL Server Data Protection with Cohesity
Complete these steps to protect your SQL databases. Get started by deploying Cohesity’s Windows
Agent next!
Send Feedback Protect SQL Server System Databases with Cohesity
5
Register SQL Server in Cohesity
To protect your SQL Server databases with Cohesity, you need to register it as a Cohesity Source first.
Once it’s registered in Cohesity, you will be able to add it to a Protection Group and configure the settings
for your environment.
To register SQL Server as a Source in Cohesity:
1. Navigate to Data Protection > Sources. Then click Register and select Databases > MS SQL
Server (Physical Server).
NOTE: When you register your SQL Server, Cohesity registers both the host and the application.
Cohesity sees the host and the SQL Server application so that it has full application awareness.
Send Feedback Protect SQL Server System Databases with Cohesity
6
2. In the Register an MS SQL Server form, enter the SQL Server FQDN or IP address, and click
Register.
Send Feedback Protect SQL Server System Databases with Cohesity
7
3. The Sources page now includes your SQL Server (in our example, SQL-Iowa-VM01), available for
immediate protection.
To protect your newly registered SQL source, you’ll create a Cohesity Protection Group for it in the
next chapter.
Send Feedback Protect SQL Server System Databases with Cohesity
8
Create a Protection Group
Automation is the only way to stay ahead of the demand for backups and data management. In Cohesity,
Protection Groups combine operational requirements (objects to protect, indexing, alerts, exclusions,
inclusions, etc.) with the business requirements that are defined in a Protection Policy (scheduling,
retention, etc.). Multiple Protection Groups can use the same Protection Policy, but each group can have
only one policy. For more, see About Policies and Protection Groups in the online Help.
Automate your SQL database backups by building a Protection Group and assigning the SQL host you
registered earlier and applying the Protection Policy that meets your business requirements.
NOTE: Because SQL Server configurations can be implemented across multiple hosts, or can be part of
a Windows cluster, it’s important that you identify all the SQL Server hosts so that they can be included
in your Protection Group.
To create a Protection Group:
1. Log in to Cohesity and navigate to Data Protection > Protection. Then click Protect and select
Databases > MS SQL Server.
NOTE: You can add or remove more SQL sources to the Protection Group later if you like. In this
way, you can build onto the Protection Group to manage all your SQL Servers.
Send Feedback Protect SQL Server System Databases with Cohesity
9
2. In the New Protection form, under Source, select the SQL host(s) you registered earlier, make your
selections, and click Save Selection.
Send Feedback Protect SQL Server System Databases with Cohesity
10
3. In the same form, enter a Protection Group Name and select the appropriate Policy. Under Settings,
select the Storage Domain and set the Start Time.
TIPS:
• Give your Protection Group a descriptive name that identifies the kind of data being protected and
how it is managed. This will help you identify and manage your SQL backups as your
environment grows. Use descriptors like: production (PROD), critical, infrastructure (INFRA),
financial, sales, primary, secondary, and employees (EMP).
• For example:
o Production_Sales
o Critical_Infrastructure
o Archive_LongRetention
o DataCenter_Dallas_Production
o Production_ReplicatedTo_DRsite
o Development_User_Data
• Once you set the Policy for a Protection Group, all the sources assigned to that Protection Group
will be conveniently managed the same way.
• To create a custom Protection Policy to meet specific scheduling, retry options, log backup,
replication, and archiving needs, learn how to Create or Edit a Standard Policy in the online Help.
• For maximum space savings and security, choose a Storage Domain with compression,
deduplication, and encryption enabled. For details, see Create or Edit Storage Domains in the
online Help.
Send Feedback Protect SQL Server System Databases with Cohesity
11
4. In the same form, configure any MS SQL Settings and Additional Settings that you need to and
then click Protect. For details on the Additional Settings, see Create a VDI-based MS SQL
Protection Group in the online Help.
Your new SQL Protection Group is now active and running, and appears on the Protection page. For
more on optimizing your protection, see Cohesity MS SQL Best Practices in the online Help.
Now that you have created a Protection Group for your SQL Server databases, you can add other
SQL sources, or change the Protection Policy and settings. In this way, all your SQL sources in this
Protection Group will be managed the same way. For example, to replicate all the backups to an off-
site target, simply add replication to the policy that is assigned to this Protection Group.
TIP: In larger environments, build two or three different Protection Groups with different
configurations to manage several SQL Server hosts. This helps keep your data management simpler
even as your environment grows.
Send Feedback Protect SQL Server System Databases with Cohesity
12
Recover SQL Server System Database
For the DBA, restoring the database starts with a restore of the backup.
Cohesity makes the SQL database restore process easy.
For example:
• When recovering the system databases, Cohesity ensures that the recovered database files have the
same permissions as the SQL Server service.
• DBA attempts the system database recovery even if the SQL Server instance is down.
• DBA disables certain restore options that will conflict with a system database restore like keep CDC,
and rename the database.
• Cohesity applies a strict SQL Server version check before restoring a system database.
Database restores overwrite the original database and you cannot undo a restore operation. Go through
the following restore rules message before you go ahead with a database restore.
Important:
Note: The SQL Agent service will be stopped during the restore of a system database. You must restart
the service manually.
Send Feedback Protect SQL Server System Databases with Cohesity
13
To recover SQL Server databases:
1. Log in to Cohesity and navigate to Data Protection > Recoveries. On the Recoveries page, click
Recover and select Databases > MS SQL
2. In the New Recovery form, select your database backup. When you do, click Next: Recover
Options.
NOTE: You cannot recover user databases and system databases together. You can select Master,
MSDB, and Model together if they are in the same group
TIP: Use the keyword “system” in your search to find all three system databases at once. Otherwise,
you can search by their individual database names like master or msdb.
Send Feedback Protect SQL Server System Databases with Cohesity
14
3. In the New Recovery form, if you need a different backup (point in time), click the Recover Point to
select an earlier backup. Under Settings, select the Restore to Original Server Instance and enter
the SQL Host and SQL Server Instance names where you need to restore the backup. If you need a
different backup (point in time), click the backup listed under Recover Point to select an earlier
backup. Click Recover.
For more information on the other SQL DB recovery options, see Restore MS SQL Databases in the
online Help.
Your recovery task launches and appears on the Recoveries page.
Send Feedback Protect SQL Server System Databases with Cohesity
15
Upgrade Your Disaster Recovery Preparedness
Disaster Recovery (DR) and business continuity are closely related plans designed to proactively protect
a business’s infrastructure and data. Taking SQL backups is only one part of protecting your business;
you must also protect the data from corruption and catastrophic disaster. You can achieve this by keeping
a series of backups, replicating those backups off-site, and archiving them under a long-term retention
plan.
Cohesity gives you the foundation to build a DR plan to protect your business:
• Capture and Store. Protect your SQL databases from loss and corruption with regularly scheduled
backups.
• Geo-Redundancy. Replicate your SQL backups to an off-site location to protect from catastrophic
loss and disaster.
• Cost-Effective Archival. Archive your SQL backups to the cloud and store them on lower-cost
storage tiers for long-term retention.
Use a Protection Group to schedule regular SQL backups and assign a Protection Policy to include
archiving and replicating those backups for long-term retention and disaster recovery.
Figure 2: SQL Backups in Cohesity are Available to Replicate and Archive
Take Local Snapshots
Protect your SQL backups over time by maintaining a series of local Cohesity snapshots.
Use Cohesity Protection Groups to schedule and automate SQL backup management.
Send Feedback Protect SQL Server System Databases with Cohesity
16
Replicate Backups Off-Site
Protect your entire set of SQL backups from catastrophic loss by replicating your SQL backups to an off-
site location. Choose a Protection Policy for your Protection Group that automatically copies the SQL
backups to a second, off-site Cohesity cluster. By default, deduplication and compression are enabled for
replication, and Cohesity sends only the changed data over the network, producing a significant reduction
in network traffic and cost.
Archive Backups to the Cloud
Archive your SQL backups to the cloud as a way to address long-term data retention requirements and
simultaneously lower the cost of storage. Cohesity provides a policy-based method to archive to public
clouds (AWS, Azure, and GCP), as well as to any S3-compatible storage.
With Cohesity CloudArchive, Cloud Recover, and CloudRetrieve, your SQL backups are available for
recovery to their original Cohesity cluster or onto a different Cohesity cluster, for geo-redundancy and
disaster recovery.
Figure 3: Cohesity CloudArchive, Cloud Recover, and CloudRetrieve Provide Disaster Recovery
Send Feedback Protect SQL Server System Databases with Cohesity
17
Best Practices for Cohesity VDI-Based SQL Server
Protection
Configuring the right Cohesity settings dramatically improves the performance of your backups, and the
efficiency of your storage and archives. Manage your backups by choosing the optimal settings for
deduplication, compression, and encryption.
• Use inline deduplication. Deduplication (enabled by default) prevents duplicate blocks of repeated
data from being stored, dramatically reducing your storage consumption.
With inline deduplication, the process occurs as Cohesity is saving the blocks, instead of waiting until
after Cohesity has written the data to its Storage Domain. Cohesity recommends you use
deduplication and wherever possible, enable inline deduplication.
• Use inline compression. Compressing your data significantly reduces the space needed to store
your backups and frees up space for more backups and other important data.
With inline compression, the process occurs as Cohesity is saving the blocks, instead of waiting until
after Cohesity has written the data to its Storage Domain. Both compression and inline compression
are enabled by default, and Cohesity recommends you take advantage of them. For details, see
Create or Edit Storage Domains in the online Help.
• Use encryption. When a platform governs access to data across the systems in your environment, it
is crucial to protect the data it manages. Cohesity recommends you enable encryption — at rest, in
flight, and in the cloud — for all your SQL Server backups. For more, see Cohesity Security Features
in the online Help.
• Keep multiple snapshots to guard against corruption. Cohesity recommends you maintain five to
seven local snapshots of your backups.
When you capture and store your backups like this, you protect your data from corruption over time.
By taking and maintaining several snapshots, you are in a position to recover data from its state prior
to being corrupted. Snapshots are efficient because they capture just the changed blocks of data, and
then use deduplication and compression.
Cohesity recommends protecting all your SQL Servers with regularly scheduled backups, and then in
turn moving some of those backups off-site and archiving them under a long-term retention plan for
disaster recovery.
• Validate the backups. Cohesity recommends a periodic restore of a SQL Server database in a test
environment from its backup. This is an important step in the overall backup strategy because it tests,
verifies, and validates the integrity of the backup. Restore a sample from the snapshot set to a non-
production server and evaluate it. In addition to confirming that the right data is backed up properly,
this practice also ensures that you have already validated your method of restoring objects before a
critical event necessitates it.
• Verify logging and auditing operations. It is very important to log and audit changes on a SQL
Server. Cohesity logs its recovery process. In Cohesity, navigate to Data Protection > Recoveries
and click into your SQL database recovery task to view detailed logs.
• Shelter in the cloud. Cohesity recommends archiving to the cloud for low-cost, long-term storage
and protection from regional disasters.
Send Feedback Protect SQL Server System Databases with Cohesity
18
Cohesity provides a policy-based method to archive to public clouds (AWS, Azure, Google Cloud
Platform) or any S3-compatible storage. This makes it easy to change policies, meet regulatory
requirements, and retrieve your data to different geographical locations.
• Use replication to defend against site disaster loss. Protect your entire set of SQL Server
backups from catastrophic loss by replicating them to a different geographical site. Cohesity can
automatically replicate the SQL database backups stored in the Cohesity cluster to a second, off-site
Cohesity cluster.
Cohesity replication always performs source-side deduplication and compression first and sends only
the changed data over the network for cost-effective disaster recovery. As such, Cohesity replication
is an essential part of every disaster recovery (DR) plan.
• Be prepared before disaster hits with a DR plan. One of the best things you can do to protect your
SQL Server is to include it when you create your DR plans.
Send Feedback Protect SQL Server System Databases with Cohesity
19
Appendix A: Terminology
There are several concepts and terms that are important to understand as you learn how to take
advantage of all of Cohesity’s features for SQL database protection.
• Protection Group. A collection of objects from your registered sources that share a recurring backup
schedule of Protection Runs. Use a Protection Group to identify which SQL databases to protect.
When you create a Protection Group, you associate it with a Cohesity Protection Policy.
• Protection Policy. A reusable collection of settings that define how and when objects are backed up,
replicated, and archived.
• Cohesity Replication. Replication automatically makes copies of snapshots captured by Protection
Runs on one Cohesity cluster and puts the copies on a second Cohesity cluster.
There are three types of database backups that can be taken with SQL VDI: full, differential, and log.
Having a combination of these backup types on hand will protect your SQL database.
• Full Backup. The full backup is a complete backup of a database. The full backup contains all the
data in a database and can be used to do a complete restore of the database to the point in time that
the full backup completed.
• Differential Backup. Used only in conjunction with a full backup, a differential backup specifies that
the backup file should consist only of changes in the database since the last full backup. A differential
backup typically takes up far less space than a full backup. Note, however, that a differential backup
is not independent and must be based on the latest full backup of the data. That means there must be
a full backup as a base, then the differential can be applied. Optionally, you can then also use log
backups to bring it to the appropriate point in time.
NOTE: In Cohesity, a VDI-based differential backup is referred to as an incremental backup.
• Log Backup. The transaction log backup is a sequential set of backup files. They comprise a record
of all the transactions that have been performed against the database since the transaction log was
last backed up. With transaction log backups, you can recover the database to a specific point in
time.
Each log backup captures that part of the transaction log that was active when the backup was
created, and it includes all transactions that were not backed up in a previous log backup. An
uninterrupted sequence of log backups contains the complete (unbroken) log chain of the database.
Send Feedback Protect SQL Server System Databases with Cohesity
20
Appendix B: Product Documentation
Review our SQL Server product documentation for in-depth details:
• MS SQL Requirements
• Cohesity MS SQL Best Practices
• Key Concepts
• Protect MS SQL (VDI-based)
Send Feedback Protect SQL Server System Databases with Cohesity
21
Your Feedback
Was this document helpful? Send us your feedback!
About the Authors
Scott Lorenz is a SQL Solutions Engineer at Cohesity. In his role, Scott focuses on business-critical
applications, MS SQL Server databases, cloud storage, and enterprise data protection. Scott has over 26
years’ experience as an enterprise DBA.
Other essential contributors include:
• Bart Abicht, Senior Technology Writer and Editor
Document Version History
VERSION DATE DOCUMENT HISTORY
1.0 June 2022 First release
Send Feedback Protect SQL Server System Databases with Cohesity
22
ABOUT COHESITY
Cohesity radically simplifies data management. We make it easy to protect, manage, and derive value
from data -- across the data center, edge, and cloud. We offer a full suite of services consolidated on
one multicloud data platform: backup and recovery, disaster recovery, file and object services, dev/test,
and data compliance, security, and analytics -- reducing complexity and eliminating mass data
fragmentation. Cohesity can be delivered as a service, self-managed, or provided by a Cohesity-powered
partner.
Visit our website and blog, follow us on Twitter and LinkedIn and like us on Facebook.
© 2022. Cohesity, Inc. All Rights Reserved. The information supplied herein is the confidential and proprietary information of
Cohesity and may only be used (a) by the intended recipients and (b) in conjunction with validly licensed Cohesity software and
services. Find the terms of Cohesity licenses at [Link]/agreements.
Cohesity, the Cohesity logo, SnapTree, SpanFS, DataProtect, Helios, and other Cohesity marks are trademarks or registered
trademarks of Cohesity, Inc. in the US and/or internationally. Other company and product names may be trademarks of the
respective companies with which they are associated. This material (a) is intended to provide you information about Cohesity
and our business and products; (b) was believed to be true and accurate at the time it was written, but is subject to change
without notice; and (c) is provided on an “AS IS” basis. Cohesity disclaims all express or implied conditions, representations,
warranties of any kind.
Send Feedback Protect SQL Server System Databases with Cohesity