Introduction

For a SQL Server DBA handling multiple databases on any given time, knowing how to set up regular backup schedules, backups with unique names on a daily basis, making backup mirrors for redundancy, cleaning up old backup files is important. Equally important is automatic confirmation that the backups have been successfully created for the databases with an email notification. There are a couple of different ways to set up email notifications which can be done from Microsoft’s SQL Server Management Studio.

Introduction

When working with a large number of databases on multiple SQL Servers, creating a foolproof disaster recovery plan can be challenging. Well organized backup and restore strategies will definitely help with this. In order to successfully implement these strategies in a larger environment, configuring automated backup and restore processes is a must. Some database administrators use the batch or power shell scripts for automation, while others prefer to use various 3^rd party solutions. In both cases, it is necessary to format database backup names properly. Properly formatted backup names make the job of organizing and maintaining the backup sets much easier. Old backup files are usually obsolete, and they can be easily identified and deleted from the drive either manually, or by using a script.

One of the most important tasks for any database administrator is to create a foolproof disaster recovery plan. This plan usually includes multiple backup and restore operations. Most of the time, opting for conventional backups should suffice, but in some cases, storing all backups on a single backup device may prove to be a bad idea. As the databases grow with time, the backups become larger, and backup devices less stable due to frequent read/write operations. If the backup device fails, all of the backed up data might be lost. To avoid this scenario, some administrators take multiple copies of their backup files, and store them on different backup devices. There are a few ways to do this:

Introduction

Running and maintaining multiple SQL Server instances can often be a formidable challenge, especially if these instances run on multiple servers. It is easy enough to set up a SQL Server agent job for each server to automate the backups, but what happens if there are 20, 30, or 100 servers that need maintenance? In this scenario, configuring agents on each server would take forever, and monitoring the entire setup would prove to be a nightmare for any administrator. Of course, there are several solutions for this scenario:

This article explains how to create filegroups and move indexes into a different filegroup, and how to backup a database without indexes with the purpose to reduce the amount of data required to perform backups thus reducing backup time and space required.

One of the most common ways to ensure that the recovery will be possible if a data-file corruption or any other disaster occurs is to create a recovery plan for this scenario. The most popular recovery plans include regular creation of database backups which can later be used to restore a database to the nearest available point in time, prior to the disaster.

A database is one of the most important parts of every information system and therefore is an often target of hackers. Encryption is the process of obfuscating data with the use of a key and/or password making the data unintelligible to anyone without a corresponding decryption key or a password.

Having a good backup and restore plan is an important part of a disaster recovery strategy. This article will describe 3 different solutions/approaches for creating a scheduled backup in SQL Server

As a part of a backup strategy, several types of backup can be used together.