SQL backup and restore

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 »
Miroslav Dimitrov

How to perform backup and restore operations on SQL Server stretch databases

September 7, 2016 by

The Stretch database is one of the latest and most-exciting features that has been released with SQL Server 2016. In the previous article, we examined the concept behind it, the steps to implement it, monitor it and how it can be disabled. As a natural follow-up, we will explore the backup and restore topic when it comes to those kind of databases.

Read more »
Aamir Syed

SQL backup reports with PowerShell

August 15, 2016 by

Let me preface this post by saying that this was a process that evolved over time. It started as a simple sql statement that emailed me a csv file. From there the actual SQL code evolved to display cleaner results. When I learned what powershell could do, I spent some time learning it during my lunch breaks at work. Even when I implemented a powershell script for the first time, it still wasn’t a clean looking report, but it was still better than what we had prior. Even now there is room for improvement but I am happy with the results and hope that it can provide some help to other SQL DBA’s out there.

Read more »
Sifiso W. Ndlovu

Meaning of the 5127 database snapshot error code

April 15, 2016 by

Long before I turned to Data Warehousing and OLTP replica environments such as Operational Data Stores as a form of data redundancy strategies, I had been using database snapshots. During that time, the analytics and reporting teams were directed towards a database snapshot as a data source for their development. Another benefit of having a database snapshot, is the ease of knowing that should unintended changes be committed in source, you could easily revert the operation by restoring source database off the snapshot.

Read more »
Shawn Melton

Analyzing SQL Server backups

March 8, 2016 by

Introduction

Database backups are important and always something you should have in any environment. Outside of needing them to restore a given database they have some information that can be useful in certain situations. One situation I found them convenient is with consolidation projects. If the databases are online you can obviously go to the source SQL Server instance to gather that information, but as a consultant I don’t necessarily have access to every environment. You may have the same issue if you are being brought into a project and your customer or department manager just wants you to advise on how you would setup the server. One easy request is to have them point you where the backups are stored and ensure you have access to the files.

Read more »
Eli Leiba

Creating a stored procedure to fix orphaned database users

January 25, 2016 by

The Problem

This problem demonstrates a very common scenario. When a backed up database is restored to a different location or server, the restored database users that exist inside of it, lose their association to the new server login objects.

This problem occurs because the server id in the user data is still the old server and must be changed to the new one.

The DBA task is then to restore the connection between the login and the database user object and to link them together again.

Read more »
Kaloyan Kosev

MS SQL Server backup optimization

January 19, 2016 by

Backup and recovery are some of the most important DBA tasks, although they look simple enough and usually you setup them, leave them running on schedule and only come back if they fail – there is a whole new world in regards to optimization you can do to make them faster, better and … smaller.

But why bother? Considering that the modern databases grow at such fast pace you may face a situation where you are not able to fit in your maintenance windows or service contract obligations. Let us take a look on the three main areas where we can work on:

Read more »
Ahmad Yaseen

Backup SQL Server database to and restoring from multiple files

January 6, 2016 by

Introduction:

The SQL Server backup provides an important solution for protecting critical data that is stored in SQL databases. And in order to minimize the risk of data loss, you need to make sure that you back up your databases regularly taking into consideration the changes applied to your data. It is a best practice to test your backups by restoring random backup files to a test environment and check that the backup files are not corrupted.

Read more »