Press "Enter" to skip to content

Category: Backups

Offloading Maintenance Operations

Published 2021-04-21 by Kevin Feasel

Taryn Pratt has a process for offloading maintenance operations onto another server:

Early on when I started working on the SQL Servers at Stack Overflow, we were taking daily backups. We had a handful of databases that were being restored for other processes, but the majority weren’t actively tested to ensure the backups were good. Since you never want to be in a situation where you need to restore a database and find it doesn’t work, my goal was to create a process to automatically restore our backups to a separate server, and then run DBCC CHECKDB on it.

This is a T-SQL-driven process and I appreciate that. If you want a Powershell-driven process, Kevin Hill has you covered.

Comments closed

Restoring a TDE Database to a Different Server

Published 2021-04-09 by Kevin Feasel

Chad Callihan reminds us of the other half of backups:

You setup TDE for your database. Great! Now you need to restore that database to a different server. Maybe you’re migrating off of an older server or maybe there is testing to be completed for an upcoming release. If we try to backup/restore or attach the database on a different server we’ll get some variation of an error stating “cannot find server certificate with thumbprint…” and the process will fail. There are a few steps to get the TDE database restored and we’re going to walk through them today.

Read on for those instructions.

Comments closed

Restoring a Database Formerly in an Availability Group

Published 2021-03-22 by Kevin Feasel

Jack Vamvas has a process for us:

Steps to restore a database from a backup device that was part of an Always On Availability Group, and now needs to be restored 

Recovery Scenario : Requesting an older database copy previously backed up 

Name of Always on Availability Group = MyAG1

Name of Always On Availability Group db = MyAGDB1

Note: this is a workflow – and there may be some slight variations depending your Availability Group set up 

Read on for rest of the workflow.

Comments closed

Continuous Backup with Cosmos DB

Published 2021-03-19 by Kevin Feasel

Hasan Savran reviews a new bit of functionality in Cosmos DB:

     Azure Cosmos DB announced Continuous Backup in Cosmos DB on March 2021. This feature is currently in public preview mode and It is not recommended to use in production. This option gives you more options for your backup requirements. You might be using Azure Data Factory to handle your custom backup needs. Azure Data Factory is the SSIS in cloud. ETL jobs can be problematic. Backing up a database is half of the problem; other half is restoring a database. Until now, we had to call Microsoft to restore Cosmos DB databases/accounts.
    By using Continuous backup, you can easily backup and restore your database. For now, this option is available only for SQL API and Mongo API. There are many limitations in this public preview version. I am sure many of these limitations will go away when it becomes generally available to everybody.

Click through for more details about the offering, as well as how to enable it. We’ll have to wait until it’s out of public preview to see how much it will cost, but it does look interesting.

Comments closed

SSD Bit Rot

Published 2021-02-23 by Kevin Feasel

Jonathan Kehayias explains why SSDs aren’t good long-term backup devices:

Back in 2012, I wrote a post titled Looking at External Disk Performance using USB 3.0 and eSATA with SSD, where I tested a number of external drive caddy’s with SSD’s that I had replaced and just had sitting around. Ultimately I started to use those SSD’s for storing information that I didn’t really need to have on my laptops and when they were full, they ended up in my desk drawer, where they have sat, unplugged and “safe” for the last 7-8 years. Or so I thought. With cheer competitions season in full swing, one of the things I love to do is shoot photos of my kids and their teammates competing, and storing RAW files that are 25-35MB per photo when you shoot 1000+ photos in a weekend across four different teams starts to take up a lot of space, so I figured I would pull out the old SSD’s and see what was on them that was worth keeping, delete what wasn’t and I could then move last years RAW files over to them and archive them for safe keeping. WRONG!!!  Of the four SSD’s I had stored data on, 100% of them had data loss due to a phenomenon known as bit rot. One of them wouldn’t even show up in Disk Manager in Windows and had to be low level formatted and reset using diskpart’s clean command due to partition table corruption.

This is a nasty scenario. Read on to learn more about how you can detect the issue and keep in mind the 3-2-1 rule: 3 copies of your data in at least 2 storage media, at least 1 of which is offsite.

Comments closed

Long-Term Backups on Azure SQL Database

Published 2021-02-05 by Kevin Feasel

Arun Sirpal takes us through a fairly new feature in Azure SQL Database:

There is a new (ish) interface to looking and configuring backups for your Azure SQL Database. This can be found within the settings section of the SQL Server.

As you can see, by default we have 7 days retention to allow for PITR – Point In Time Recovery, anything longer you will need to setup long term retention.

Click through to see how to set this up.

Comments closed

Saving Money on Backups to Azure Blob Storage

Published 2021-01-15 by Kevin Feasel

John McCormack has a few tips for saving some cash:

You have 5 databases on a SQL Server Instance. You take daily full backups of each database on your instance. You also take log backups every 15 minutes as each database is in full recovery mode. This means in 1 week, you will have 35 full backups and 3,360 transaction log backups. This multiplies to 1,820 full and 174,720 t-log backups over 52 weeks. Multiply this for 7 years or more and the costs can get very expensive.

Click through to see how you can save a considerable amount with a bit of planning.

Comments closed

Building a Backup Plan

Published 2020-11-24 by Kevin Feasel

Greg Larsen takes us through the steps of developing out a backup plan:

You might be wondering why you need to develop a backup plan. Can’t a DBA just implement a daily backup of each database and call it good? Well, that might work, but it doesn’t consider how an application uses a database. If you have a database that is only updated with a nightly batch process, then having a daily backup of the database right after the nightly update process might be all that you need. But what if you had a database that was updated all day long from some online internet application. If you have only one backup daily for a database that gets updated all day online, then you might lose up to a day’s worth of online transactions if it was to fail right before the next daily backup. Losing a day’s worth of transaction most likely would be unacceptable. Therefore, to ensure minimal data loss occurs when restoring a database, the backup and recovery requirements should be identified first before building a backup solution for a database.

The biggest non-secret here is that backup plans are ultimately business decisions rather than technical decisions. Greg then outlines several cases and provides considerations for each.

Comments closed

Finding the Physical Path of a SQL Server Backup on a Container

Published 2020-11-17 by Kevin Feasel

Jack Vamvas is looking for love files in all the wrong places:

I’m migrating some SQL Server databases to Openshift Containers. The SQL Server is set up with persistent disk , with a dedicated persistent disk partition for the SQL Server defaultbackup directory. I don’t have access to the underlying files via command line and can only use command line. How can I get the physical disk device , which will then allow me to create a RESTORE DATABASE statement pointing to the device?

Read on for the answer, including a T-SQL script to find where these files live.

Comments closed

An Intro to Backup Strategy with SQL Server

Published 2020-11-10 by Kevin Feasel

Pamela Mooney takes us through backup and restoration as part of a DBA in training series:

A DBA’s job is getting the right data to the right people as quickly as possible.

Consider that first part – getting the right data. How do you do that? By having the data in the first place. The best way to ensure that you have the data is to look at your company’s needs, recommend availability options, and most importantly, do backups. Few things will get a DBA fired more quickly than not having backups available when they are needed. It may be years before you ever have to restore a database from a backup, but the backups had better be there when you need to do one.

Conference sessions and books have been written on the subjects of backup/restores, SLAs, and availability options, and they are so good that I’ll just give you a brief overview here. You’ll gravitate to your need-to-learns soon enough.

This is a nice overview for a beginner. One semi-related piece of advice that I picked up from Sean McCown: know that backup and restore syntax cold. Take backups manually (even if they’re just on a test server intended for this purpose) every day until you feel comfortable typing out backup and restore syntax from memory. There will come a time when three levels of management are standing over your shoulder waiting for a database to restore and SSMS’s GUI is crawling. At that point, knowing the syntax cold will be completely worthwhile.

Comments closed