Press "Enter" to skip to content

Category: Backups

Verifying Database Backups

Lori Brown reminds us to perform checksums and verify backups on completion:

I found out that I have been missing something from our regular database backups that I had no idea that I should have been using all along.  I know about verifying your backup file and have incorporated into our standard maintenance routines one that will periodically test backups by restoring using VERIFYONLY.  However, I totally missed also having CHECKSUM specified when creating backup files.  Ugh!!  Not sure how that happened but I am totally onboard with it now.  Better late than never!

Lori does explain what the consequences are in terms of time and CPU utilization so that you’re aware of the tradeoffs when enabling these options.

Comments closed

Auditing Database Backups

Jovan Popovic shows how you can audit who is taking backups on an Azure SQL Managed Instance:

One mechanism to ensure that nobody can take the COPY_ONLY backup of your database is to use Transparent Data Encryption that automatically encrypts all backups. In that case you would need to use Customer-managed (BYOK) TDE where you will keep your encryption key in Azure Key-Vault. User-initiated COPY_ONLY backups are (currently) not allowed if you are using Service-managed TDE.

If you don’t use TDE on the database or there is a risk that someone can remove TDE from database and then take a backup, Managed Instance provides auditing mechanism that enables you to track who performed a backup and when. This way you can always track if some unauthorized action is happening.

Read on for the solution. Looking through it, it seems like conceptually it’d work equally well with on-prem/IaaS SQL Server as with Managed Instances.

Comments closed

Test Those Restores

Bob Pusateri wants you to test your restores, probably right now:

I had a client that was hit by a ransomware virus, encrypting several of their systems including the database server. Not to worry, though, they had “full backups” of all the affected machines, done by a third-party backup utility. After taking a day to cleanse their network, they restored these backups onto their servers. Now it was just a simple matter of bringing all the applications back online, right? Well, not exactly…

Just because a backup completes with no error code doesn’t mean it’s really a successful backup.

Comments closed

Using AZCopy for SQL Backups

John McCormack shows how you can use AZCopy to move SQL Server backups into Azure Storage:

AZCopy is a useful command line utility for automating the copying of files and folders to Azure Storage Account containers. Specifically, I use AZCopy for SQL Backups but you can use AZCopy for copying most types of files to and from Azure.

In this blog post example (which mirrors a real world requirement I had), the situation is that whilst I need to write my SQL backups over a network share, I also want to push them up to Azure Storage (in a different region) to allow developers quicker downloads/restores. This is why I need to use AZCopy. If I only needed my backups to be written to Azure, I could have used BACKUP TO URL instead.

Read on to see how John did it.

Comments closed

Estimating Backup Footprint

John Morehouse has a Powershell script to estimate how much disk space you’re using with backups:

Recently, I needed to be able to determine the total size for all the most recent full backups for certain servers. Luckily for me these particular servers were using Ola Hallengren’s Maintenance Solution which simplified things since each back up had it’s own specific directory. Powershell has the flexibility to easily provide what I need with little effort.

Let’s take a look!!

John shows specifically the latest full backup, but you can extend this to include other sets as well.

Comments closed

Testing dbcreator Privileges

Shane O’Neill wants to know whether the dbcreator built-in role has the ability to back up the databases it creates:

I knew from reading the documentation that dbcreator grants permissions to create, alter, drop, and restore databases. My question was does it give permission to backup databases?

It seems to give everything else so is backup databases just missing there? Or is it intentionally left out?

To find out whether it does, click on through.

Comments closed

TDE + Copy-Only Backups

Jovan Popvic shows how you can take a copy-only backup of a TDE-protected database in Azure SQL Managed Instance:

We are recommending to rely on automatic backups only, with the build-in restore functionality to restore a database from a point-in-time, restore a database to another instance (for instance from production to dev)or Geo-restore functionalities to move your database. These automatic backups can be kept up to 35 days. These built-in automatic backups are secure and enables you to be fully compliant. In this scenario COPY_ONLY backups are only in some specific cases.

Strict TDE protection don’t allow you to take your own custom backups. If you need a backup of a TDE protected database, you would need to temporary disable TDE, take a backup, and then enable TDE again.

It’s not really a Managed Instance-specific thing, but rather TDE: if you want to take a non-encrypted backup of an encrypted database, you’ve got to kill encryption first.

Comments closed

Quick Hits on Managed Instance Backup / Restore

Jovan Popovic has some pieces of advice for backing up and restoring databases on Azure SQL Managed Instances:

Managed Instance takes automatic backups (full backups every week, differential every 12 hours, and log backups every 5-10 min) that you can use to restore a database to some point of time in past within the retention period, restore accidentally deleted database. For more information, see Automated backups. Managed Instance also enables you to restore a database from a backup file placed on Azure Blob Storage, backup a database to Azure Blob Storage. Managed Instance currently don’t support backup retention longer than 35 days, but you can use backups to blob storage as an alternative.

If you are experiencing some issues with any backup or restore operation, the following troubleshooting steps might help you to identify the issue.

Click through for those hints.

Comments closed

Backing Up Database to Azure Blob Storage

Jamie Wick shows us how we can back up database directly to Azure Blob Storage:

Azure storage, as a backup destination for SQL backups, is a great option for organizations that are contemplating replacing older on-prem NAS appliances or improve their Disaster Recovery functionality. The tiered storage pricing, along with local and global redundancy options, can be much more cost-effective than many traditional backup options.

In this post, we’re going to look at some of the key concepts and restrictions, along with how to back up an SQL database to an Azure storage location.

Click through for the demo.

Comments closed

Downgrading a SQL Server Database

Dave Mason goes against the flow:

One of the recurring questions I see on Stack Overflow is “How do I restore a SQL Server backup to a previous version of SQL Server?” The answer, of course, is you don’t. Upgrading a database to a newer (major) version is a one-way ticket–at least as far as the database files and subsequent backups go. I recently found myself in a similar position as all those hapless Stack Overflow questioners. I had a customer that had migrated to a newer version of SQL and they wanted to roll back to the previous version. What to do?

A couple of thoughts immediately came to mind. There’s the SQL Server Import and Export Wizard and the Generate and Publish Scripts Wizard. Neither of these sounded convenient. In particular, generating a script with both schema and 500 GB of data sounded like a fruitless endeavor. Two other options sounded much more appealing. So I focused on those.

Dave has a couple of creative methods effectively to downgrade a database.

Comments closed