Kenneth Fisher explains what the LOADHISTORY option means when you run a RESTORE VERIFYONLY command:
So, first of all, it only works with RESTORE VERIFYONLY. RESTORE VERIFYONLY does some basic checking on a backup to make sure that it can be read and understood by SQL. Please note, it does not mean that the backup can be restored. It will check things like the checksum, available diskspace (if you specify a location), the header and that the backup set is actually complete and readable. Basically enough to see if it will start restoring, but it could still have errors later on.
As for what LOADHISTORY actually does? It causes you to write an entry to the restore history table. You can tell which record this is because the restore_type is set to a V. Really, the only benefit here (as I see it) is that you can do reporting on what backups you’ve verified.
Click through for a demo.
Jen McCown walks us through how to restore a database on a different server with Minion Backup:
Today we’ll look at configuring a common, repeatable scenario: take the latest backup of MyDB from ProdServer1 and restore it to DevServer1. There are four basic steps to the setup and execution:
Configure Minion Backup and let it run on ProdServer1.Restoring with MB requires at least one full backup taken by MB. (Note that you don’t need Minion Backup on DevServer1 for this scenario.)
Configure restore settings paths. You know how sometimes a restore requires one or more “WITH MOVE” clauses? Configure this once for your source-target pair, and MB takes care of it from then on.
Configure the restore tuning settings (optional). Oh yes, we tune our backups AND our restores!
Generate and run the restore statements.
It’s a good walkthrough if you’re a Minion Backup user. If you’re not and you’re not particularly happy with your backup solution, I recommend giving it a try.
Hugo Almeida has some hints for backing up Azure Data Lake Store data using Azure Data Factory:
Our Hadoop HDP IaaS cluster on Azure uses Azure Data Lake Store (ADLS) for data repository and accesses it through an applicational user created on Azure Active Directory (AAD). Check this tutorial if you want to connect your own Hadoop to ADLS.
Our ADLS is getting bigger and we’re working on a backup strategy for it. ADLS provides locally-redundant storage (LRS), however, this does not prevent our application from corrupting data or accidentally deleting it. Since Microsoft hasn’t published a new version of ADLS with a clone feature we had to find a way to backup all the data stored in our data lake.
We’re going to show you How to do a full ADLS backup with Azure Data Factory (ADF). ADF does not preserve permissions. However, our Hadoop client can only access the AzureDataLakeStoreFilesystem (adl) through hive with a “hive” user and we can generate these permissions before the backup.
Read the whole thing if you’re thinking of using Azure Data Lake Store.
David Fowler shows how to back up SQL Server directly to an AWS S3 bucket:
I’ve been having a little play around with AWS recently and was looking at S3 (AWS’ cloud storage) when I thought to myself, I wonder if it’s possible to backup up an on premise SQL Server database directly to S3?
When we want to backup directly to Azure, we can use the ‘TO URL’ clause in our backup statement. Although S3 buckets can also be accessed via a URL, I couldn’t find a way to backup directly to that URL. Most of the solutions on the web have you backing up your databases locally and then a second step of the job uses Power Shell to copy those backups up to your S3 buckets. I didn’t really want to do it that way, I want to backup directly to S3 with no middle steps. We like to keep things as simple as possible here at SQL Undercover, the more moving parts you’ve got, the more chance for things to go wrong.
So I needed a way for SQL Server to be able to directly access my buckets. I started to wonder if it’s possible to map a bucket as a network drive. A little hunting around and I came across this lovely tool, TNTDrive. TNTDrive will let us do exactly that and with the bucket mapped as a local drive, it was simply a case of running the backup to that local drive.
Quite useful if your servers are in a disk crunch. In general, I’d probably lean toward keeping on-disk backups and creating a job to migrate those backups to S3.
Your transaction log is full. Both Microsoft, and about 100 articles and blogs have covered this topic, but let’s take a quick look anyway. Because, you know, it comes up all the time.
Summary:
This error message points to a lack of log backups.
Make sure using sys.databases.
Start backing up the log.
You can shrink the log if necessary.
A note on SIMPLE mode, and why it’s often a terrible idea.
This is a good summary of the problem and various solutions.
Kevin Ngo announces a new feature in Azure SQL Data Warehouse:
Previously, SQL DW supported only automated snapshots guaranteeing an eight-hour recovery point objective (RPO). While this snapshot policy provided high levels of protection, customers asked for more control over restore points to enable more efficient data warehouse management capabilities leading to quicker times of recovery in the event of any workload interruptions or user errors.
Now, with user-defined restore points, in addition to the automated snapshots, you can initiate snapshots before and after significant operations on your data warehouse. With more granular restore points, you ensure that each restore point is logically consistent and limit the impact and reduce recovery time of restoring the data warehouse should this be needed. User-defined restore points can also be labeled so they are easy to identify afterwards.
Creating a user-defined restore point is a one-liner in Powershell, and it’s something you could do after each warehouse load, for example.
Jovan Popovic announces an improvement to Azure SQL Database Managed Instances:
Azure SQL Database Managed Instance enables you to create a database as a copy of another database at some point in time in the past. This is known as point-in-time restore feature, and up till now you could perform point-in-time restore only within the same instance.
The latest release of Azure SQL Database Managed Instance enables you to perform point-in-time restore of a database from one instance to another. This might be useful if you need to be sure that you could easily restore a database to another instance if there is some issue on the original instance, or if you need a database for testing or auditing purposes on the test instance and you want to use copy of some of the existing database on another server.
Click through for the current requirements and limitations, as well as a sample.
The history of TDE and backup compression is that until SQL 2016, they were great features that didn’t play well together – if TDE was in play, backup compression didn’t work well, or at all.
However, with the release of SQL 2016, Microsoft aimed to have these two awesome features get along better (the blog post announcing this feature interoperability is here). Then there was this “you need to patch” post, due to edge cases that might cause your backup to not be restored. So if you haven’t patched in a while, now would be a good time to do so, because Microsoft says those issues have been resolved (although that seems to be disputed here).
My sympathies definitely lie toward backup compression over TDE if forced to choose between the two.
Ned Otter gives us some options for reducing the size of full backups:
SPARSE columns
IF a column contains mostly NULLs, then depending on the data type, you can achieve space savings by using the SPARSE property (documentation here). SPARSE columns can be used with filtered indexes to theoretically reduce storage space and increase query performance. But there are a boatload of gotchas, such as issues with query plan caching (filtered indexes), and the fact that if you use SPARSE columns, neither the table or indexes can have any form of compression (the documentation is clear about not supporting table compression, but does not mention index compression being an issue – but it is).
As the documentation clearly states, when converting a column from non-sparse to sparse, the following steps are taken:
- Adds a new column to the table in the new storage size and format
- For each row in the table, updates and copies the value stored in the old column to the new column
- Removes the old column from the table schema
- Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column
For large tables with even a few columns that you wanted to convert to SPARSE, this process would take forever, because you must do this for each column you want to convert.
I don’t like sparse columns at all, but I do like the rest of Ned’s options.
Clive Strong tells a tale about a mental flub while restoring a backup:
Our automated restore process works really nicely. We take full backups on Saturday and differential backups through the week. We also take log backups through the day, but we were not going to be restoring those for this task. We have a number of internal platforms we restore to in full (or in part following a cut down process) so which gives us good validation of our backup files on a regular basis. We also have regular test restores from tape just for good measure.
However, a while ago I was asked to build a new server and restore the databases up to a specific date. We didn’t need a point in time restore, just to a specific day, so I pulled the full and differentials and wrote the script to do the restore for me. The script restored the full backup and the differential backup for Sunday, Monday, Tuesday, Wednesday and Thursday. I gave it the once over and executed the script. A while later and I came back and it was unexpectedly, still running. I eventually left the office and noted it finished in the early hours and ran for many hours longer than I had anticipated.
Read on for Clive’s more detailed explanation of the whoopsie moment.
Kevin Feasel
2018-07-20
Backups
No Comment