SQLShack SQL Server training Español 

Newsletter
An overview of the process of SQL Server backup-and-
restore Enter your email

March 1, 2018 by Prashanth Jayaram

Subscribe

Follow us!
  

In a manner of speaking, planning and implementing a SQL Server backup design is an art. Backup, Restoration,
Recovery, Business Continuity Plans (BCP), and Disaster Recovery (DR) are different phases of data revolving
around the discussions involving data backup. In other words, it’s about how we ensure to retain the business
data through any sort of situation that’s thrown at us.

Backup is probably the simplest and the most familiar process in most situations. A backup is a copy of the data
derived from the production copy, stored at a location that’s different from where the production data is stored.
A backup copy is used toThis website
recover datauses cookies.
needed By continuing
to restart to usecorrectly,
an application this site and/or clicking
after certain the of
types "Accept"
failures.button you Accept
are providing
Using various tools and techniques, theconsent
data is replicated to different geographies and is made available all the
time. Some may even argue that having multiple working copies of the data somewhere else removes the need
Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you
for having backups, but in a not-so-perfect
register world,or
on our websites backups are do
when you stillbusiness
requiredwith
to make sure
us. For youinformation
more can always about our
Privacy Policy and our data protection efforts, please visit GDPR-HQ
recover from data mishaps.
Popular
CASE statement in SQL
What is a backup? SQL Convert Date functions and formats

SQL PARTITION BY Clause overview

The word backup refers to copying and archiving the data so that it may be used to restore in case of an event
SQL Server table hints – WITH (NOLOCK) best
of data loss. In general, you should back up any work or data that can’t be replaced easily. practices

What is the difference between Clustered and Non-

A database backup is a copy of data from the database, which can be used to reconstruct the data. A database Clustered Indexes in SQL Server?
backup is a process to protect the data from various disasters. Disasters cannot be completely prevented; the Multiple options to transposing rows into columns
least we can do is to ensure we have everything that’s needed to get up and running as soon as we can. How to copy tables from one database to another in
Database backup is also performed and managed to ensure that an organization complies with business and SQL Server

government regulations. For instance, for legal reasons in the US, correspondences should be saved for at least Using the SQL Coalesce function in SQL Server
seven years in the manufacturing industry. The process of backing up data is pivotal to a successful disaster Six different methods to copy tables between
recovery (DR) plan. databases in SQL Server

Different ways to SQL delete duplicate rows from a

SQL Table
Organizations target for backup, the data they feel is vulnerable to:
How to implement array-like functionality in SQL
Server
Corruption
SQL replace: How to replace ASCII special characters
Failed hardware including media failure, software failure, and OS errors
in SQL Server
Attacks on the network or other malicious actions
Working with the SQL Server command line (sqlcmd)
Manual errors or other inadvertent actions
How to implement error handling in SQL Server
Unauthorized changes
How to move SQL database files (MDF and LDF) to
and other undesirable events
another location

SQL varchar data type deep dive

All about locking in SQL Server

How often should I run my backups (frequency of How to identify slow running queries in SQL Server

backups)? SQL WHILE loop with simple examples

SQL Server Transaction Log Backup, Truncate and

Shrink Operations
The frequency to run your backups depends on how often the data changes. You may find it helpful to consider
the following points when planning your backup schedule:

Business data that changes frequently may need daily or hourly backups
Data that changes every few days could use a weekly or even monthly backup
For some data, you might want to run a backup whenever you make a change

Predefined policies and Service Level Agreements (SLAs)govern the frequency and redundancy (or replicas) of
the backup.

Backup destination
You can store backup files in one of the following locations:

1. Local storage is used if you want to save the backup on a removable storage device attached to the
computer or on a local computer drive.
2. Shared folder is used if you want to save the backup in a network shared folder
3. Tape devices are used in case robust archival is required.
4. Cloud backup
Public cloud storage: this type of storage is often used to backup the data as part of the Disaster
Recovery where users send the data over to public cloud services such as S3. The providers generally
charge based on monthly usage of storage. Some providers also charge for data transfers and access
Private cloud storage: is referred to as internal cloud storage. In this case, the data is backed up to
servers within the environment, based on pre-determined set of policies and company’s firewall
Hybrid cloud storage: is a combination of public and private cloud.

Who should tailor your backup Trending

DELETE CASCADE and UPDATE CASCADE in SQL
Server foreign key
The backup process is handled by Backup Operators. They are specific individuals given the responsibility to
configure, schedule, initiate and monitor the backup process. How to create and configure a linked server in SQL
Server Management Studio

Step by step installation of SQL Server 2017

Understanding RPO and RTO SQL Server Statistics and how to perform Update
Statistics in SQL

The RPO (Recovery-Point-Objective) and RTO (Recovery-Time-Objective) are vital parameters to consider when SQL Union overview, usage and examples

planning the backup strategy. Query optimization techniques in SQL Server: tips
and tricks

The recovery point objective, or RPO, is the point in time that data can be restored to. For instance, if the How to install SQL Server 2014 Management Studio

most recent available backup is a week old, then we’d be able to restore to what the database looked like at that When to Use SQL Temp Tables vs. Table Variables
time the backup was taken, so you will potentially lose a week’s worth of data. On the other hand, if your most INSERT INTO SELECT statement overview and
recent backup was taken five minutes ago, then we can restore to that point in time and would lose only five examples

minutes of data. So the question would be how much data is the organization willing to lose—data worth hours, SQL multiple joins for beginners with examples

minutes, seconds or nothing at all? If the plan is zero data loss, then backup alone is not the solution. We need Substring function overview
to use additional tools and techniques to achieve the desired result. Multiple methods for scheduling a SQL Server
backup automatically

The other term, recovery time objective, (or RTO) is the amount of time taken to perform the restoration. A very Setting up the dark theme in SQL Server
Management Studio
large database may take several hours or even a day to restore, which in some cases this may not be
acceptable. Typically, running a restore takes slightly longer than running a backup, so if we do a backup which Overview of the SQL ROW_NUMBER function

takes 30 minutes to run, it wouldn’t be surprising if the restore ran for longer than 30 minutes. Understanding the GUID data type in SQL Server

Database table partitioning in SQL Server

Overview of SQL Server Ports

How to import data from an Excel file to a SQL

Server database

Techniques to bulk copy, import and export in SQL

Server

SQL Server stored procedures for beginners

Summary
As a general rule, the amount of time in between backups should be no more than the amount of time you are
willing to spend redoing any lost work. For example, if spending a week recreating the lost data is too long for
you, you should back up the data at least once a week.

The additional techniques that we mentioned for zero-loss model is called High Availability in most situations.
These may be very expensive for some organizations. On the other hand, backups are more economical to run.
Therefore, because of our budget constraints, sometimes we have to live with the fact that we will lose a few
minutes of data.

The recovery point objective and recovery time objective are metrics that are usually decided by the business. Solutions
Several aspects of business are taken into consideration when coming up with these metrics. Once these
Read a SQL Server transaction log
objectives are decided, these must be clearly communicated to the stakeholders (IT and otherwise) within the
SQL Server database auditing techniques
organization.
How to recover SQL Server data from accidental
UPDATE and DELETE operations

Table of contents – SQL Server backup and restore article How to quickly search for SQL database data and
objects

series Synchronize SQL Server databases in different

remote sources

Recover SQL data from a dropped table without

Database Backup and Restore process in SQL Server – series intro backups

How to restore specific table(s) from a SQL Server

database backup
An overview of the process of SQL Server backup-and-restore
Recover deleted SQL data from transaction logs
Understanding the SQL Server Data Management Life Cycle
How to recover SQL Server data from accidental
updates without backups
Understanding SQL Server database recovery models
Automatically compare and synchronize SQL Server
Understanding SQL Server Backup Types data

Open LDF file and view LDF file content

Backup and Restore (or Recovery) strategies for SQL Server database
Quickly convert SQL code to language-specific client
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
code

How to recover a single table from a SQL Server

Understanding Database snapshots vs Database backups in SQL Server database backup

Recover data lost due to a TRUNCATE operation

SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
without backups

Smart database backup in SQL Server 2017 How to recover SQL Server data from accidental
DELETE, TRUNCATE and DROP operations
How to perform a Page Level Restore in SQL Server Reverting your SQL Server database back to a
specific point in time
Backup Linux SQL databases Using PowerShell and Windows Task Scheduler
How to create SSIS package documentation
SQL Server database backup and restore operations using the Cloud Migrate a SQL Server database to a newer version of
SQL Server
Tail-Log Backup and Restore in SQL Server
How to restore a SQL Server database backup to an
older version of SQL Server
SQL Server Database Backup and Restore reports

Database Filegroup(s) and Piecemeal restores in SQL Server

In-Memory Optimized database backup and restore in SQL Server

Understanding Backup and Restore operations in SQL Server Docker Containers

Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio

Interview questions on SQL Server database backups, restores and recovery – Part I

Interview questions on SQL Server database backups, restores and recovery – Part II

Interview questions on SQL Server database backups, restores and recovery – Part III

Interview questions on SQL Server database backups, restores and recovery – Part IV

References
Backup Overview (SQL Server)
Restore and Recovery Overview (SQL Server)
Overview of Restore Scenarios

See more
For High-speed SQL Server backup, compression and restore see Quest LiteSpeed, an enterprise tool to
schedule, automate and backup SQL databases

LiteSpeed for SQL Server provides high-speed backup, compression and res…
res… Categories and tips
► Auditing and compliance (43)
Azure (24)

Azure Data Studio (22)

Backup and restore (84)

► Business Intelligence (385)

Data science (17)

► Database design (190)

► Database development (295)
DBAtools (16)

DevOps (21)

DevSecOps (2)

Documentation (17)

ETL (49)

► Features (182)
Importing, exporting (40)

Installation, setup and configuration (78)

Jobs (23)

► Languages and coding (526)

Lists (14)

Machine learning (6)

Maintenance (74)

Migration (34)

Prashanth Jayaram ► Performance tuning (628)

I’m a Database technologist having 11+ years of rich, hands-on experience on Database ► Professional development (35)
technologies. I am Microsoft Certified Professional and backed with a Degree in Master of
Recovery (27)
Computer Application.
Security (77)
My specialty lies in designing & implementing High availability solutions and cross-platform DB Server management (10)
Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and
MongoDB. SQL Azure (133)

SQL Server Management Studio (SSMS) (74)

View all posts by Prashanth Jayaram
► SQL Server versions (141)
SSIS (1)

► Technologies (144)
Related Posts: Uncategorized (2)
1. Database Backup and Restore process in SQL Server – series intro Utilities (12)
2. Backup and Restore (or Recovery) strategies for SQL Server database
3. Tail-Log Backup and Restore in SQL Server
 Helpers and best practices
4. SQL Server Database Backup and Restore operations using the Cloud
BI performance counters
5. Understanding Backup and Restore operations in SQL Server Docker Containers
SQL code smells rules

Backup and restore SQL Server wait types

11,974 Views

ALSO ON SQL SHACK

SSIS Hadoop Lever T-SQL for Table-Valued How to deploy SSAS

Connection … Pinpoint Control of … Parameters in SQL … cubes
a month ago • 1 comment 18 days ago • 1 comment 2 months ago • 1 comment 2 months ago • 1 comment
In this article, we will give a This article spotlights a This article is discussing the This article explains how to
brief introduction of Hadoop clean, efficient, pinpoint T- table-valued parameters deploy SSAS Cubes using
and how it is integrated … SQL stored procedure … usage details and it also … different methods available

2 Comments SQL Shack 🔒 Disqus' Privacy Policy 

1 Login

 Recommend 11 t Tweet f Share Sort by Best

Join the discussion…

LOG IN WITH
OR SIGN UP WITH DISQUS ?

Name

John • 2 years ago

Thanks. Is there any way to do it automatically, using any third-party tool?
3△ ▽ • Reply • Share ›

Prashanth Jayaram > John • 2 years ago

Thanks for the comment, John!. Answer to your question is "Yes". It can be done automatically using
third-party tools. Even, third-party tools in-house many in-built features to customize the backup/restore
and Recovery process.

You can refer the TOC at the bottom of this article.

Let me know if you've any specific question so that I could give more explanation.

Best Regards,
Prashanth
3△ ▽ • Reply • Share ›

✉ Subscribe d Add Disqus to your siteAdd DisqusAdd ⚠ Do Not Sell My Data

© 2020 Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy