LitArk » Books » Computer

Bradley Beard - Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques

Here you can read online Bradley Beard - Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques full text of the book (entire story) in english for free. Download pdf and epub, get meaning, cover and reviews about this ebook. year: 2018, publisher: Apress, genre: Computer. Description of the work, (preface) as well as reviews are available. Best literature library LitArk.com created for fans of good reading and offers a wide selection of genres:

Romance novel Science fiction Adventure Detective Science History Home and family Prose Art Politics Computer Non-fiction Religion Business Children Humor

Choose a favorite category and find really read worthwhile books. Enjoy immersion in the world of imagination, feel the emotions of the characters or learn something new for yourself, make an fascinating discovery.

Book:
Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques
Author:
Bradley Beard
Publisher:
Apress
Genre:
Books / Computer
Year:
2018
Rating:
3 / 5
Favourites:
Add to favourites
Your mark:
- 60
- 1
- 2
- 3
- 4
- 5

Description
Author's other books
Similar books

Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques: summary, description and annotation

We offer to read an annotation, description, summary or preface (depends on what the author of the book "Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques" wrote himself). If you haven't found the necessary information about the book — write in the comments, we will try to find it.

Be guided through the techniques to back up and restore databases and files in SQL Server. Multiple techniques are presented for ensuring the integrity and consistency of database backups, as well as the reliability and manageability of restoring from backups. In todays data-driven world, the most important asset that a company has is its data. Data recovery strategies can be wide ranging; from the practically non-existent backup without verification to a very complex, distributed, and redundant system. An important objective for any database administrator is data loss prevention, and in a catastrophic data loss event, pre-defined data recovery techniques must be employed in order to get the databases back up and running efficiently and quickly. Using a tutorial method of teaching, Beginning Backup and Restore for SQL Server explores the many facets behind the principles of backing up and restoring data from your database, and then shows practical application methods for getting the work done quickly and correctly. This book is an essential guide toward discovering the correct path to protecting the data for your organization. What Youll Learn Be instructed on backup and restore techniques and principles Understand the importance of a fully implemented backup plan in SQL Server Agent Integrate backup and restore techniques into an existing environment Consider space and time requirements for backup and restore operations Master the principles for common deployment types Who This Book Is For Administrators who need to learn or refresh their knowledge of how to back up and restore SQL Server databases in various scenarios

Bradley Beard: author's other books

Who wrote Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques? Find out the surname, the name of the author of the book and a list of all author's works by series.

Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques — read online for free the complete book (whole text) full work

Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.

Light

Font size:

↓

↑

Reset

Interval:

↓

↑

Bookmark:

Make

Part I
Backups

Bradley Beard 2018

Bradley Beard Beginning Backup and Restore for SQL Server

1. Full Backups

Bradley Beard 1

(1)

Palm Bay, Florida, USA

The concept of backing up data is one of those things that should be extremely intuitive, but is often implemented poorly, if at all. I know quite a few database administrators (DBAs) that dont worry about regular, structured backups because their database server is on a SAN (storage area network) and the data is therefore backed up regularly. To me, this makes no sense at all, since there is absolutely no contingency for point-in-time restoration of data, outside of what happened to have been backed up as part of a complete Windows backup solution. If there were a catastrophic failure, and the DBA had to rebuild the database to the point of failure, it could not be easily done. The reason is because the entire Windows image would have to be rebuilt from the last Windows backup, which means that the database will only be current to that particular point of time, and not the desired point of time. For example, if the Windows image is run nightly, but the database backups are run hourly, then you will have a perfect set of hourly backups up to the point where the Windows backup is run. If Windows fails at 11:59 pm , then that entire days worth of database backups has been lost. The rule of thumb, generally, is to put backup files on a separate drive than the OS. This alleviates the issue outlined in the preceding, as long as the backup drive is not corrupted.

In the realm of database administration, I think it is best to imagine that your database server is a stand-alone system. This means to imagine that there is no SAN, no Windows backup solution, nothing like that at all. You must be able to manage the entire universe of your data as it pertains to your server. What does this entail, exactly? Thats what this book will address; how to back up and restore your most important assetthe data you are responsible for.

Note

We will also briefly cover data storage techniques in this book, although that will not be a main focus. This is because, although we will highlight specific techniques for storage, it is ultimately your decision whether or not to implement storage techniques outside of what is available from your local file system.

What Is a Full Backup?

A full backup is the entirety of the data within a database from the point in time that the backup was run. Part of the transaction log is also backed up in a full backup; this is done so a successful restore can eventually be run using the backed-up data. A backup can be saved to local disk, an available network share, or even in Windows Azure blob storage (if youre running SQL Server 2012 or later). The full backup type provides the starting point for a full restore, and also the starting point for a differential restore (covered in Chapter ). In other words, without a full backup, neither a differential nor a transaction log backup can be successfully restored.

In my first book, Practical Maintenance Plans in SQL Server (available from Apress), I briefly discussed the concept of backups in Chapter , titled Backing Up a Database. In that chapter, I went over recovery models and backup types, and then explained how to set up the maintenance task to perform these jobs automatically . For the purpose of this book, I dont think we need to go over the job creation part, but we will go over recovery models and backup types.

Recovery Models

A recovery model is how SQL Server is told to recover data. Figure shows where to look to find the recovery model configuration area. This is found by right-clicking an existing database and choosing Properties, then selecting Options from the left menu. In the example shown, I have chosen to create a new database, so the screen you see in the figure is the New Database screen.

Figure 1-1

Recovery models location in New Database screen

I am going to name this database backrecTestDB, and this is the database that we will be using throughout this book. Obviously, you will have your own databases that you will maintain separately, but this is what we will use as a reference.

Notice that we are on the General tab, as shown in the left pane of the preceding figure. Click Options, and you should see what is shown in Figure .

Figure 1-2

Options

The initial interface for the Options area is now visible.

Note

Recall that I am using SQL Server Management Studio (SSMS), which is available as a separate download from Microsoft, to administer my SQL Server 2016 instance.

At the very top of this screen, the second option down is for recovery model. You have three options for recovery models . Those options are as follows:

Full: This option lets the database recover to nearly any point in time, and is the clear choice of many DBAs.
Bulk-logged: Similar to full recovery, but this scenario allows for logging to be minimized for bulk operations (copying, specifically).
Simple: This is the choice for smaller, nonmission-critical databases. It does not allow for point-in-time restores like full, or bulk operations like bulk-logged. It simply allows for recovery using the last backup.

We are going to keep the full option selected here, because we want to be able to look at point-in-time restores later on in this book.

Backup Types

SQL Server has three unique backup types, which are discussed in later chapters. They each perform differently, and they can either work together or separately to provide a backup solution for your data. The backup types that are available to you are also entirely dependent upon the recovery model used for your database. We will get more into this shortly. For now, lets take a look at Listing , which outlines the different backup types.

Listing 1-1. Backup Types

Full backups
- A full backup will back up the entire database, which includes the transaction log. Using this method, anything can be restored up to the point in time when the backup was run.
Differential backups
- A differential backup contains any data not backed up since the last full backup.
Transaction log backups
- A transaction log backup will contain the individual transactions that affect the current state of the database since the last backup.

There are a few things to note about these different yet similar backup types.

First, a full backup is different from a transaction log backup, in that the full backup type has the entirety of the actual data within the database, while the transaction log backup only has the individual transactions through time that affected the data contained within the database.

Second, a differential backup is useless without the last full backup. The differential backup is applied to the full backup, which creates the point-in-time restore for that particular differential backup set. One important thing to remember about differential backups is that they do not contain the transaction log, so any data beyond the differential backup will not be restored without restoring the transaction log backups.

Light

Font size:

↓

↑

Reset

Interval:

↓

↑

Bookmark:

Make

Similar books «Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques»

Look at similar books to Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques. We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.

Joe Kissell

Take control of Mac OS X backups

Simon Riggs

PostgreSQL 14 Administration Cookbook: Over 175 proven recipes for database administrators to manage enterprise databases effectively, 3rd Edition

Marek Chmel

SQL Server 2019 Administrators Guide: A definitive guide for DBAs to implement, monitor, and maintain enterprise database solutions, 2nd Edition

Ahmad Osama

Professional Azure SQL Managed Database Administration: Efficiently manage and modernize data in the cloud using Azure SQL, 3rd Edition

Bradley Beard

Beginning SQL Server R Services: Analytics for Data Scientists

Mohn

Learning Veeam(r) Backup and Replication for Vmware Vsphere

Christian Mohn

Learning Veeam® Backup and Replication for VMware vSphere

Razolli Federico.

Mastering MariaDB

Munoz Alvarez

Oracle Database 12c Backup and Recovery Survival Guide

Steve Buchanan

Microsoft System Center Data Protection Manager 2012 SP1

W. Curtis Preston

Backup & Recovery: Inexpensive Backup Solutions for Open Systems

Sandeep Chanda

Beginning ASP.NET 4.5 Databases

Reviews about «Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques»

Discussion, reviews of the book Beginning Backup and Restore for SQL Server: Data Loss Management and Prevention Techniques and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.