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.
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.
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.
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.
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.
Jamie Wick explains what differential backups are and when they can be useful:
SQL Server natively supports 3 types of backups: Full, Differential and Log. Full backups take a complete backup of the entire database, while Log backups take a backup of the database’s transaction log. So, What are Differential backups?Are they really necessary?
Read on to see what Jamie has to say.
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.
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.
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.
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-DbaCommandto 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.
Kevin Feasel
2019-05-24
Backups, Cloud
No Comment