Press "Enter" to skip to content

Category: Backups

Developing In The Cloud

Richie Rump has some nice pointers about developing for Azure or AWS:

Since we’re a bunch of data freaks, we wanted to make sure that our data and files are properly backed up. I set out to create a script that will backup DynamoDB to a file and copy the data in S3 to Azure. The reasoning for saving our backups into a different cloud provider is pretty straightforward. First, we wanted to keep the data in a separate cloud account from the application. We didn’t make the same mistakes that Code Spaces did. Secondly, I wanted to kick the tires of Azure a bit. Heck, why not?

I figure this script would take me a day to write and a morning to deploy. In the end it took four days to write and deploy. So here are some lessons that I learned the hard way from trying to bang out this backup code.

This is a must-read if you’re starting to look at using cloud providers for services.

Comments closed

Filegroups And RTO

Raul Gonzalez explains the importance of filegroups in minimizing RTO:

So if we don’t create additional filegroup[s] in our databases not only all the data will go to the same logical container but also in case we have to restore that database from a backup, we will have to wait until all of it it’s restored.

Imagine you have a lot of historical data for instance and there is a disaster, if you had different filegroups, one for current data and another for the historical, you would be able to get your live data first and quickly (to get you up and running), and then restore all the historical which is not critical.

To show you how, I’m going to create a database with different filegroups so you’ll see how we can do.

Click through for the scripts, as well as more information.

Comments closed

Restoring An Azure SQL Database

I have a post on restoring a database in Azure SQL Database:

You will need to select your restore point as well.  In this case, I decided to restore back to midnight UTC on a particular date.  Note that the dates are UTC rather than your local timezone!

After selecting your restore point, you pick the target server and can decide a couple of things.  First, you can put this database into an elastic database pool, which makes cross-database connections a lot easier.  Second, you can choose a different pricing tier.  Because I only needed this database for a few minutes, keeping it at P2 Premium was fine; the total restore time meant that we spent less than a dollar bringing the data back to its pristine condition.

Be aware of the time for restoration; it can be very slow.

Comments closed

Review Your Process

Chris Sommer wants you to think about why you follow certain processes:

We’re still dealing with the same problems because we’re dealing with the problems in the same way.

I think it can be cultural and can propagate from the senior level DBA’s right on down to the new hires. Sometimes it’s just lack of knowledge or understanding. Sometimes it’s just pure laziness to not want to do a deep dive and find a better solution to a recurring problem.

Here is a pretty extreme example but I think it portrays all of these.

Given some of the things I’ve seen, I’d say his example is not at all extreme.

Comments closed

Test Restores

Steve Jones implores you to test those database backups by restoring them somewhere:

What do you do? Hopefully you recognize the issue and can fix the issue. Maybe more importantly, you have a backup of the missing certificate.

Most people don’t deal with encryption, but you never know when your backup job might start failing, perhaps writing to a damaged file that appears to work (if you write as a device) but really isn’t capturing the backup file. Perhaps you don’t know that your backups are being written to a location and deleted a day later, but the process that is supposed to copy them to tape or a remote file share is broken.

Any number of things can happen. The point is that you want to be sure that you are actually getting useable backup files.

That means testing restores.

Read the whole thing.

Comments closed

Speeding Up Database Restores

Paul Popovich gives tips on how to speed up restores from a DataDomain device:

Recently we had to restore our 5TB prod database to rebuild an AG node. Here’s some things we learned along the way to hopefully help you speed up your restores.

Backup your VLDB to multiple files. We found 12 to be the sweet spot in our setup. Make sure you’re going to 10gig NICs on both ends of the transfer.

In terms of folder directories on that thing we learned to go wide and small. Let me explain. Our setup is this:
\\datadomain\sql\sqlbu\\\Full or Tlog

Click through for more tips.  This is independent of optimizing your restore scripts themselves.

Comments closed

Restoring All Databases

Kevin Hill builds a script to reload all databases at once:

We are doing a major upgrade this weekend, so like any good DBA, I have planned for a full backup the night before and needed the ability to quickly restore if it goes sideways and needs to roll back.

The catch is that there are close to 300 identical databases involved.

This is easy enough to do if you know where info is stored in MSDB related to the last backup.

Click through for the script.

Comments closed

Restoring An Azure SQL Database

Arun Sirpal discusses ways to restore a database within Azure SQL Database:

You won’t have the ability to use the same name of the restoring database and the database that you want to replace; if you try you get the screen shot below: To get around this I think you would need to drop the old one once the new one has restored then do a rename.

This is a big difference compared to the on-prem version, so be sure to practice this before you find yourself in a crisis.

Comments closed

Migrating To Azure SQL Database

Mike Fal discusses BACPACs, DACPACs, and migrating on-prem databases to Azure SQL Database:

SQL Server Data Tools(SSDT) have always had a process to extract your database. There are two types of extracts you can perform:

  • DACPAC – A binary file that contains the logical database schema and possibly the data. This file retains the platform version of the database (i.e. 2012, 2014, 2016).

  • BACPAC – A binary file that contains the logical database schema and the data as insert statements. This stores the platform version, but is not locked into it.

Mike also walks through SqlPackage.exe.

Comments closed

Creating BACPAC Files

Kenneth Fisher needs a new BACPAC:

Why are we talking about it?

Well there are two reasons. First because I’m studying how to move databases from SQL Server to Azure SQL Database and back. My first blog on the subject was using the Deploy Database to Microsoft Azure SQL Database option to move a SQL Server database to Azure SQL Database.

Kenneth shows you how to do this through the UI as well as through SqlPackage.exe.

Comments closed