A Fine Slice Of SQL Server
Chad Callihan backs that database up:
Keeping on the recent PowerShell trend, let’s use PowerShell to accomplish a primary task of any database administrator: backups. With PowerShell and dbatools, you can do a simple backup or add a range of options to fit your needs.
I’d also like to call out that it’s really easy to set configuration options with dbatools, such as buffer count and max transfer size.
Comments closedJose Manuel Jurado Diaz diagnoses an error:
Today, we worked on a service request that our customer got the following error message: BACKUP WITH COPY_ONLY cannot be performed until after the next automatic BACKUP LOG operation [SQLSTATE 42000] (Error 41937) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013), running a manual backup.
Click through to learn when you might see this error and what you can do about it.
Comments closedGilbert Quevauvilliers has us synchronize our watches:
In this blog post I am going to detail how to create a schedule, test and confirm that the backup for the Power BI Premium dataset is successfully backed up.
This follows on from my first blog post: Automating the backup of Power BI Premium datasets/databases – Part 1
Read on for a walkthrough of what you need to do in order to create a schedule runbook.
Comments closedGilbert Quevauvilliers shares the first part of a two-part series:
The first part in this 2-part series I am going to explain how configure the Azure Runbook so that you can then re-use it for multiple different Power BI datasets.
I am confident that most people have more than one dataset that needs to be backed up.
Before starting, please make sure that you have connected your Power Per User or Premium App Workspace to Azure Storage
Read on for a high-level overview of how to create a runbook in Powershell, as well as the runbook code.
Comments closedMatthew McGiffen helps us recover from a big oopsie:
If you don’t have the backups of the certificate and private key from the old server, as well as the password used to encrypt the private key backup then you could be in a lot of trouble. There is one scenario where you have a way out. I’m going to assume you don’t have the possibility to recover your old server from a complete file system backup – if you do then you can do that and access all the keys you require. If the two following things are true though then you can still recover your database:
Read on to see what those requirements are and how you can, in specific circumstances, recover that database.
Comments closedBen Miller begins a series on Ola Hallengren’s maintenance solution:
I recommended creating a database to use for this solution or even installing it into an existing DBA function database. I usually create a DBA database and use it for this purpose and others as well. With this new database, you configure the Database in the header of the maintenance solution SQL file, whether to create jobs, retention time and backup directory for the jobs.
This first post acts as a primer for those who might have the solution but haven’t investigated it in any detail.
Comments closedMatthew McGiffen notes how to restore a database with transparent data encryption:
When encrypting a database with Transparent Data Encryption (TDE), a vital consideration is to make sure we are prepared for the scenario where something goes wrong. For instance, if the server hosting our SQL instance goes belly-up, can we recover the data that we have encrypted with TDE?
Click through to learn what you’ll need to have.
Comments closedAndrew Pruski talks snapshots:
When we talk about snapshots of SQL Server there are two types, application consistent snapshots and crash consistent snapshots.
Application consistent snapshots require freezing IO on a database allowing for a checkpoint to be performed in order to write all dirty pages to disk.
In the past, application consistent snapshots relied on the third party software to call the SQL Writer service but now with SQL Server 2022 T-SQL snapshot backups we have the ability to use T-SQL commands to freeze IO on a database in order for us to take an application consistent snapshot of the database.
Read on for more detail on the topic. With SQL Server 2022, I’ve softened a bit on using snapshots for data retention—prior to that, I was not a fan of the idea.
Comments closedGilbert Quevauvilliers has a hefty backup:
When using Power BI Premium or Premium Per user you get the option to backup the database, there can be occasions when you try and restore the backup and it fails.
The reason that it could fail is because when a restore happens it can consume additional memory which would take you up and over the memory limit.
Below I will explain a new option which allow this to restore successfully!
Gilbert includes a copy of the error message and one new option in the post.
Comments closedChad Callihan tells us a story:
Many years ago, a friend was using a backup/recovery tool for managing their backups. This particular tool on its own wasn’t necessarily bad. But in this case, it didn’t work well. In fact, it barely worked at all. Backups were slow to complete and restores were even slower. Attempting to restore even one database could take 10-15 minutes just to navigate a GUI and start the restore process.
It…was…very…slow…
Read on for the rest of the story. Most of the time, when I see two products used for backups, I typically see a bunch of redundant backups, with both products taking full backups.
Comments closed