A Fine Slice Of SQL Server
Kevin Hill learns a new trick:
How long has this worked, and why didn’t anyone tell me?
A typical restore statement from a FULL backup and a LOG backup:
Click through to see what Kevin means and how you might be able to make your life a little easier.
Comments closedAndy Yun digs into a SQL Server backup file:
This first came up during my onboarding with Pure Storage. Anthony Nocentino (b|t) taught me that a SQL Server backup file is a byte-for-byte copy of your data, as materialized in SQL Server MDF files (assuming no backup compression or backup encryption). And that would make sense – how else would SQL Server store a copy of your data in a backup file? It does not make sense for SQL Server to alter your data when it writes it down to a backup file (again, with NO backup compression/encryption) – that’s a waste of compute and effort.
Well, I had a conversation with someone who was unclear about that assertion. I tried some Google-fu to present some supporting materials, but could not actually find any documentation, official or otherwise, to back it up. So here we are.
Click through to dive into a backup file with Andy.
Comments closedTaiob Ali takes us through a new DMV:
There is a new DMV currently in preview which returns information about backups of Azure SQL databases except for the Hyperscale tier. Microsoft official documentation is here.
If you run the example query as-is from the above documentation some of the columns do not make sense.
Taiob includes a better query which provides the type of information you’re used to in on-premises SQL Server.
Comments closedGreg Dodd diagnoses a problem:
I hit an error recently on a server that caused backups to fail. The database was backing up to a UNC path. Looking in the SQL Log file and Event Viewer, I found the following error:
The operating system returned the error ‘121(The semaphore timeout period has expired.)’ while attempting ‘DiskChangeFileSize’ on ‘\\uncpath\folder\databasename.bak’.
Read on to see what caused this error.
Comments closedTom Collins has a backup to restore:
I have a SQL Server with TDE enabled and the user databases are TDE configured. I need to take a backup and restore the TDE enabled database to another SQL Server Instance . Could you take me through the steps including prerequisites?
The answer is yes. And Tom is so kind as to show the answer rather than giving a flippant response, which is my modus operandi.
Comments closedJoey D’Antoni troubleshoots a problem:
We had a case over the weekend where our automated restore process at client got hung up on this wait type, for a single database. What was the unique characteristic about this relatively medium (2-300 GB) database? It had a lot of filestream data–it seemed like the file count wasn’t that high, but my guess is the filestream data was the majority of the data in that database. When the job hung up, the restore had been waiting on PREEMPTIVE_OS_FILEOPS for over a day and still had a null value for percentage complete.
Click through to see what the problem was.
Comments closedAbhishek Shaha and Ahmed Mahmoud enumerate techniques to export an Azure SQL Database:
Export Azure SQL Database is a common request for Azure SQL DB customers, in this article we are going to list down some advanced scenarios, on how this can be achievable through various tools not limited to Azure Portal, Azure CLI and PowerShell. In addition, this article will provide alternative methods when it comes to private endpoints and deny public access.
Click through for several options.
Comments closedMarc Lelijveld backs that thing up:
Basically for every data model, but in particular for large and enterprise-grade data models, it might be wishful to perform backups. Power BI Premium offers a way to perform backup and restore operations for Power BI datasets. This feature, coming from Azure Analysis Services, helps you to take a backup of your dataset logic as well as the data itself and the ability to restore in case your dataset gets corrupted or backend processes have failed. Furthermore, this backup and restore process is also a way to migrate your datasets from Analysis Services to Power BI Premium.
In this blog, I describe what is needed for Power BI backup operations, how you can perform them and how to script / automate them.
Read on for the process.
Comments closedJoey D’Antoni is not impressed:
While some services include other really useful features (for example the query data collected by the Azure SQL Database and Managed Instance platforms), I wanted to focus on the common value adds to PaaS systems across providers. I made the last two of these bold, because I feel like they are are the most important, especially in scenarios where the vendor doesn’t own the source to the applications. Like Amazon RDS for SQL Server.
Click through for Joey’s thoughts on the topic.
Comments closedBrendan McCaffrey offers up some guidance:
With Availability Groups there has always been a common theme across the industry to offload your database backups to a secondary replica. It sounds great in theory. I know people that have done this. I also know people that “thought” they were doing this. But the question is, should you be doing this?
Your scientists spent so long figuring out if they could that they forgot to ask if they should.
Comments closed