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.
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.
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
dbcreatorgrants 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.
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.
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.
Kevin Feasel
2019-07-19
Backups, Cloud
No Comment