Press "Enter" to skip to content

Category: Backups

Downgrading a SQL Server Database

Published 2019-04-19 by Kevin Feasel

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

Tracking Database Recovery with Extended Events

Published 2019-04-11 by Kevin Feasel

Jason Brimhall takes us through the extended events which show progress on database recovery:

Recently, I wrote a rewrite of my database recovery progress report script. That script touches on both the error log and some DMVs along with some fuzzy logic to join the data sets together. That script may not be the most complex script out there, but it is more more complex than using the power of XE.

Database recovery (crash recovery) is a nerve wrenching situation under the wrong conditions. It can be as bad as a root canal and just as necessary to endure that pain at times. When the business is waiting on you waiting on the server to finish crash recovery, you feel nervous at best. If you can be of some use and provide some information back to the business, that anxiety dissipates and the business becomes more calm as well. While the previous script can help you get that information easily enough, I want to introduce the easiest method to capture that information currently available.

Click through for more information, as well as a couple of scripts.

Comments closed

Blob Storage for Database Backups

Published 2019-04-11 by Kevin Feasel

Randolph West has a couple of tools to help upload and download database backup files:

I wrote it because AzCopy was weak and inconsistent. It was fragile, needing constant attention and monitoring in case a journalling file got stuck. Also, AzCopy didn’t keep files in sync. If a file was deleted locally (as part of a cleanup to delete old backups), AzCopy was unable to delete files remotely, so it was messy to maintain files in Blob Storage containers. The uploader was written to keep files in sync, and not have to fuss with AzCopy.

The real value of this tool though, is being able to recover the latest backup files (full, differential and transaction logs where available) which are needed to recover from a catastrophic failure. Without any knowledge of the backups, just knowing the database name, it can parse the list of files in Azure, download the necessary ones to recover, and build a T-SQL script to restore them. Literally all you need to do is run the downloader, then run the restore script.

Randolph talks about how the state of AzCopy has changed and offers up some new guidance as well as tooling updates.

Comments closed

Scripting Database Restores

Published 2019-04-08 by Kevin Feasel

Max Vernon helps us out with a query to generate a database restore command:

Just point the script at an existing SQL Server Backup File, and give the new database a name, along with a target folder for the data and log files, and press F5. This script is compatible with SQL Server 2005 and higher, and has been tested on a case-sensitive-collation server.

I think building these out by hand is good practice and helps you learn, but when it’s crunch time, you really want to have a script do the work for you.

Comments closed

Restoring Databases From Azure

Published 2019-03-04 by Kevin Feasel

John Morehouse shows how we can restore a database from Azure Blob Storage:

So how do you restore from Azure storage? You do so from an URL.  Let’s take a look!

When you backup a database to Azure, there are two types of blobs that can be utilized, namely page and block blobs.   Due to price and flexibly, it is recommended to use block blobs.  However, depending on which type you used to perform the backup will dictate how the restores are performed.  Both methods require the use a credential, so that information will need to be known before being able to restore from Azure.

Click through for examples using both page blobs and block blobs.

Comments closed

When Differential Backups Grow Larger Than Fulls

Published 2019-02-15 by Kevin Feasel

Kenneth Fisher notes that differential backups can end up being larger than full backups of the same database:

The thing about DBA Myths is that they are generally widespread and widely believed. At least I believed this one until I posted What’s a differential Backup?and Randolph West (b/t) pointed out that my belief that differential backups can’t get larger than full backups was incorrect. In fact, differential backups (like FULL backups) contain enough transaction log information to cover transactions that occur while the backup is taking place. So if the amount of data that needs to be backed up combined with transactions requires more space than just the data ….

Read on for a demonstration.

Comments closed

An Overview of dbatools with Jess and Bert

Published 2019-02-06 by Kevin Feasel

Bert Wagner has a new video available:

dbatools is one of the coolest community projects I’ve seen – it is amazing how many commands are available to help make managing your SQL Server instances a breeze.

This week I had the opportunity to learn how to use dbatools to automate backups, change recovery models, and discover additional dbatools commands from dbatools contributor Jess Pomfret.

Jess Pomfret then goes into more detail on the commands in the video:

The final tip I had for Bert was how to use Find-DbaCommand to help him find the commands he needed to complete his tasks.

A lot of the commands have tags, which is a good way to find anything relating to compression.

That was a nice collaboration.

Comments closed

Could Not Clear Differential Bitmap

Published 2019-01-30 by Kevin Feasel

Jack Vamvas takes us through a reason why you might get error 3041:

An error message has started appearing in the SQL Server Error Logs during a nightly full backup.

Could not clear ‘DIFFERENTIAL’ bitmap in database ‘Database1’ because of error 9002. As a result, the differential or bulk-logged bitmap overstates the amount of change that will occur with the next differential or log backup. This discrepancy might slow down later differential or log backup operations and cause the backup sets to be larger than necessary. Typically, the cause of this error is insufficient resources. Investigate the failure and resolve the cause. If the error occurred on a data backup, consider taking a data backup to create a new base for future differential backups.

Click through for the root cause and solution.

Comments closed

Backups With Checksum Validation

Published 2018-12-19 by Kevin Feasel

Eric Blinn shows the upside to performing checksum validation during backups:

Since a full backup reads every data page it makes sense that we can ask SQL Server to calculate and compare each checksum during this operation. Even a differential backup reads all of the pages for any extent (a group of 8 data pages) if any one page in it changes so it will validate some pages that didn’t change.

Read on for a demonstration.

Comments closed