A Fine Slice Of SQL Server
There are obviously many things to check when it comes to SQL Server security. We’re talking about a lot of those elements of security this month. Today I want to talk a little more about backups as it relates to their role in securing your SQL Servers.
When it comes to your backups and security in SQL Server –>
Read on for five tips to help ensure your backup strategy is doing what you intend it to do, but is also not a real attack vector at the same time.
Comments closedKendra Little encounters an error:
What’s it like to be a Database Administrator for managed databases in Azure? Sometimes it’s a painful guessing game when a routine, core operation– restoring a database – fails with a most unhelpful error.
In this case, if the restore is run via PowerShell, following Microsoft guidance, the error message is:
Restore-AzSqlInstanceDatabase: Long running operation failed with status ‘Failed’. Additional Info: An unexpected error occured while processing the request. [sic]
Somehow the misspelling of ‘occurred’ stings a bit more. Did anyone review the PR for this code?
I’m trying to weigh in my mind whether this error is worse than “String or binary data would be truncated.” One the one hand, the spelling is correct in the latter error message. On the other hand, it uses passive voice. On the gripping hand, they’re both nigh-useless error messages. Hopefully the SQL MI team fixes Kendra’s error message at least as well as the database engine fixed the latter.
Comments closedManvendra Singh talks about backups:
This article will explain backup storage redundancy for Azure Cosmos DB. Backups are a critical feature to keep copies of our data to ensure data protection and recoverability in case of any accidental deletion, updating, or any kind of disaster. But this is not enough to run backups only to save its copies. We must also protect those backup copies from accidental deletes or corruption and ensure their proper resiliency should be in place to keep backups safe from any unforeseen circumstances. It depends on the criticality of your data whether you want to keep them locally to want to replicate them in other locations or regions to ensure their resiliencies.
The backup process isn’t the same as with a relational database, but it’s still critical to back up your data, for the same reasons that you’d take backups of relational data.
Comments closedVlad Drumea doesn’t need no steenkin’ transaction log backups:
This brief post contains a script that can help switch a whole SQL Server instance, model and all user databases, to SIMPLE recovery.
The script is useful in case of dev/test/QA/UAT instances that have been left by accident to use the default FULL recovery model, yet do not have or need transaction log backups.
Read on for the script. It also shrinks the transaction log file after the switch-over.
Comments closedSam Garth troubleshoots an issue:
On a recent case, a customer was trying to restore a database from a storage account using a SAS token when they received the below error.
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)Additional information:
Cannot open backup device
‘https://storage.blob.core.windows.net/container/dbbackup_2024_03_21_121901.bak‘
Operating system error 86(The specified network password is not correct.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
Read on for the troubleshooting steps Sam followed to solve the problem.
Comments closedKaarel Moppel puts on the lab coat and safety glasses:
In my last post I did a quick check on the performance of the newer (lz4, zstd)
pg_dumpcompression options, which included setting up a small framework to download some openly available “real life”-ish sample datasets. And the general result was that, indeed – the new algos in lower levels provide the best value, especially zstd.But
pg_dumpis about compressing essentially text based data…but how about binary Postgres data? Thus the tool to test here additionally ispg_basebackup, with its newer (v15+) compression options. So let’s see if something stands out consistently again.
Click through for the test results.
Comments closedTanayankar Chakraborty asks, what’s in a name?:
An issue was reported recently where the customer complained that in their cost analysis report of their Azure SQL DBs, the db name appears appended with a comma(,) and a number. While they agreed with the DB name in the report, they didn’t understand the number after the comma and its significance. This is how the cost analysis report looks like:
Click through for a redacted version of the report, showing an example of the database in question, as well as an explanation of what this number means.
Comments closedSemab Tariq restores a database:
I recently had the opportunity to contribute to a customer project, where the objective was to establish a system for PostgreSQL full backups and seamless restoration. Considering Barman’s successful functionality on Linux, we decided to explore its compatibility with Windows. Secondly, no other tool claims to work on Windows to take backups and perform a restore
From official documentation it is mentioned that:
Backup of a PostgreSQL server on Windows is possible, but it is still experimental because it is not yet part of our continuous integration system.
Click through for the walkthrough.
Comments closedChad Callihan makes a comparison:
I haven’t had a MySQL post for awhile, so it’s time to add some variety to the blog.
There are a couple of different ways to export data with a SELECT query in MySQL: INTO OUTFILE and INTO DUMPFILE. Let’s use the MySQL Sakila sample database and walk through some examples to compare these two options.
Read on to see when you might want to use each of these.
Comments closedChad Callihan restores an elephant one bite at a time…or something:
The larger a database grows, the more difficult it becomes to restore it in a timely manner. When a database is young, you might be able to manage full restores in seconds. But as it matures and backup sizes go from megabytes to gigabytes to terabytes, those restore times will expand as well.
If you plan ahead, it’s not always a requirement to restore the entire database if only part of the database is necessary. This is where the idea of piecemeal restores can save you time and wasted effort.
I’ve always found piecemeal database restoration more of an interesting idea than something quite practical. The problem is, if your data is so easily separable that you can restore one set and not need the other for some reasonable length of time, why are they in the same database? I understand that there are reasonable answers to this question, but I also rarely see those scenarios pop up.
Comments closed