Backup and restore

Prashanth Jayaram

Backup Linux SQL Server databases using PowerShell and Windows task scheduler

May 22, 2018 by

This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines.

Here’s an outline of what this article is all about:

  • Introduction
  • Technical specifications
  • How to load SQL Server modules on Windows machine
  • Security – Credential Management
  • The objectives of Backup and Restore
  • Library Linkage
  • How SQL Server 2017 backup feature is used on Linux
  • And more …
Read more »
Prashanth Jayaram

How to perform a page level restore in SQL Server

May 18, 2018 by

In this article in our stairway series on SQL Server backup and restore, we shall discuss the importance of understanding the internals of the page restoration for a database administrator. Most of the time, performing a page-level restore suffices for database availability. A good backup strategy is a key to recovery or restoration, and SQL Server provides us with an option to fix database pages at a granular level. Performing a page level restore in SQL Server is a clever option wherein the restoration of the entire VLDB database backup is not required.

Read more »
Prashanth Jayaram

Smart database backups in SQL Server 2017

May 14, 2018 by

So far, we’ve discussed several phases of backup that starts with planning, creating, strategizing and implementing. In this article, we are going to see how database administrators can define the strategy to improve backup performance and efficiently manage backups in SQL Server 2017. The following are the topics of discussion:

  1. Discuss checkpoints
  2. Discuss the enhancements made in the Dynamic Management View (DMV) sys.dm_db_file_space_usage for smart differential backups
  3. Discuss the enhancements made for the Dynamic Management function (DMF) sys.dm_db_log_stats  for smart transactional log backup
  4. Understand the functioning of smart differential backup and its internals
  5. Understand the Smart transaction log backup process and its internals
  6. T-SQL scripts
  7. And more…
Read more »
Prashanth Jayaram

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

May 7, 2018 by

Data is the key to your organization’s future, but if it’s outdated, irrelevant, or hidden then it’s no good. Maintenance and administration of databases takes a lot of work. As database administrators, we often tend to automate most of these repetitive tasks. A database refresh is one of the most common tasks performed by most of the administrators as part of their daily routine.

Today, database refreshes are quite frequent because of Continuous Integration (CI) and Continuous Deployment (CD). In most of the cases, testing requires a separate but current production dataset to ensure the validity of the desired result.

Read more »
Prashanth Jayaram

Understanding Database snapshots vs Database backups in SQL Server

May 4, 2018 by

In the article, we’ll walk through the concepts to understand database snapshots, and their benefits and limitations. This article will help you decide when to use a database snapshot, and when to use a backup. In some cases, the database relying on database backup and restoration operation is not a viable option. Let’s dig into the concepts of database snapshots and see how it differs from a database backup.

Read more »
Prashanth Jayaram

Discussing Backup and Restore Automation using SQLCMD and SQL Server agent

May 3, 2018 by

Database administrators are often requested to refresh a database, mostly to create a live copy of the production database to the test or development environment. This is done so that the development or the test environment closely resembles the production environment; this way, we prevent many undesirable issues when running the application in the production environment.

Many developers, at times, are required to write and debug code on the production copy of the database, so it makes more sense to refresh the database on regular basis.

Read more »
Prashanth Jayaram

Backup and Restore (or Recovery) strategies for SQL Server database

April 26, 2018 by

One of the most important roles of a database administrator is to constantly protect the integrity of the databases and maintain the ability to recover quickly in case of a failure. In light of this, it’s critically important to have a backup-and-recovery strategy in place in order to be ready for an emergency.

A key responsibility of a database administrator is to ensure that a database is available whenever it’s needed, and prepare for various scenarios wherein the availability or the performance is impacted. Therefore, if a database, for whatever reason, gets corrupted, gets dropped, gets accidentally deleted, or goes into an unusable state, it is a database administrator’s responsibility to bring the database back up in a working state with little to no loss as per the defined service level agreements or government policies.

Read more »
Prashanth Jayaram

Understanding SQL Server Backup Types

April 19, 2018 by

SQL Server backups, in itself, is a vast subject; so vast, there are multiple books written about them. In this article, however, we are going to focus on the types of backups that are available to us, and understand how to pick what we need, and what aspects we base that decision on. This understanding would, in turn, help us decide our backup-and-restore strategy.

Following are the most common types of backups available in SQL Server:

  1. Full
  2. Differential
  3. Transaction log
  4. Tail Log backup
Read more »
Istvan Martinka

How to connect and perform a SQL Server database restore from Azure BLOB storage

April 18, 2018 by

Having things in the cloud should make life simpler but I have experienced it’s not that straightforward. Once all access / configuration is sorted out then yes, of course! But in the meantime it can be tedious (even frustrating) and the end result is something that could have been achieved with a different method.

I think a significant chunk of what Azure offers is easier to do with the more conventional methods but that separates us from the advanced Azure features that a company would like to utilize in the shorter / longer term future. So there are reasons to spend some time / effort in getting things right up there.

In my case the task I needed to accomplish sounded simple enough:

  • get a SQL Server database backup (.bak file) from Azure blob storage
  • copy it to our own environment and restore it to a SQL Server instance on an Azure Windows VM
  • or skip the copy step and restore straight from blob storage
Read more »
Prashanth Jayaram

Understanding SQL Server database recovery models

April 10, 2018 by

A recovery model is a database configuration option that determines the type of backup that one could perform, and provides the ability to restore the data or recover it from a failure.

The recovery model decides how the transaction log of a database should be maintained and protects the data changes in a specific sequence, which may later be used for a database restore operation.

Read more »
Prashanth Jayaram

Understanding the SQL Server Data Management Life Cycle

March 21, 2018 by

This is the second article in the “Backup and Restore (or Recovery) in SQL Server” stairway series (see the full TOC below). This article deals with the different phases of data management life cycle and it encompasses the following topics:

  1. Introduction to data corruption
  2. Defining data corruption and its causes
  3. Discussion on the impact of data corruption
  4. Explaining data prevention mechanisms
  5. Data protection
  6. And more…
Read more »
Prashanth Jayaram

An overview of the process of SQL Server backup-and-restore

March 1, 2018 by

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.

Read more »
Robert Seles

Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution

December 5, 2017 by

Database administrators tend to use various scripts or applications, to make the daily SQL Server database maintenance task easier.

Some more experienced administrators prefer to design and use their own scripts for these tasks. The scripts are usually designed to fit the requirements imposed by the specific environment. After the scripts are thoroughly tested, they often get included in a maintenance plan, or SQL Server agent job to automate their execution. Such solution is usually optimal for some demanding environments like high traffic servers or databases that are still in development. Designing these solutions usually take time, and require an experienced DBA skilled in T-SQL or PowerShell scripting.

Read more »
Jefferson Elias

How to choose and check the right Database Recovery Model in accordance to your backup strategy

October 11, 2017 by

Introduction

Following best practices, we should apply principles like segregation of duties. This implies the segregation of application environments. In particular, we might see for a given application that it incorporates at least two environments: a production environment and a non-production environment that exists for testing, Q/A, training, etc

Read more »
Kimberly Killian

How to automatically refresh a SQL Server database

October 4, 2017 by

As a DBA, I am often asked to automate tasks. In addition to this, I generally work with a lot of developers that need to test with data refreshing on a regular basis. A lot of the time, I am asked to either stand up a new test environment or overwrite/refresh an existing one. Now, these tasks are not difficult to complete, but why not just automate them into a SQL Agent job and just run it whenever you need it or schedule it and let the job do all the work? If you have read some of my previous articles (What is causing database slowdowns or Simple SQL Server automated user auditing), you will know I am a big proponent of automating repeatable tasks. The best part of this is if you have multiple clients like I do is that you can script out the job, change the variables and use it over and over again. I cannot tell you how many times I say “I have a job for that” when a client asks me to automate a task.

Read more »
Prashanth Jayaram

Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL

August 15, 2017 by

Introduction

Database availability is critical to every enterprise and conversely, unavailability directly can create a severe negative impact to the business in today’s world. As database administrators, it is important that we ensure we take all possible steps to minimize data loss. While it is naïve to think that our databases are invincible because of all such precautions, we can always aim to bring the databases back into operation as quickly as possible by meeting the RPO and RTO. This way, the business is unaffected, and we also meet our SLAs.

Read more »
Sifiso W. Ndlovu

Two methods for restoring a data warehouse/data mart environment

November 8, 2016 by

Implementing best data warehouse designs and practices such as data lineage reduces the need to ever have to restore an entire relational data warehouse. However, sometimes there are instances whereby you have inherited poorly designed data warehouse environments that leaves you with no other options but to perform an entire database restore in an event of a sudden disaster. I recently found myself in a similar situation of having to recover one of my data mart following a data integrity issue wherein all data of a type 1 dimension was updated/overwritten using an incorrect source file. In this article I take a look at how different approaches can be utilised to restore the compromised SQL Server-based data mart back to its “good state”.

Read more »
Page 1 of 212