A Fine Slice Of SQL Server
Andy Brownsword needs to make a call out:
One method for transferring a database to a different environment is using a Data-Tier Application – in the form of a DACPAC (for schema) or BACPAC (for schema and data).
Trying to use this approach with multi-database solutions is a challenge though as Data-Tier Applications don’t play nicely with cross-database objects.
Let’s look at how we can ease that pain.
Read on for the solution.
Comments closedRajendra Gupta takes a backup:
Azure SQL Managed Instance automatically takes backups to suit your business requirements. However, if you need a manual backup for specific requirements, does Azure MI support it? Let’s explore this in this tip.
Read on for the solution, which has several working parts due to the lack of locally accessible disk.
Comments closedGrant Fritchey restores some backups:
PostgreSQL has the capabilities to support backups as I already described in my first article on the topic. PostgreSQL also has the capability to restore to a point in time. However, that does require you to change the way you’re performing your backups. This article advances our understanding of how to better protect your PostgreSQL databases by expanding on the database backups and restores into a more full-blown disaster recovery process through point in time restores.
While the important part is the restore, in a classic chicken or egg conundrum, we can’t talk about restoring until we first have a backup, so I’ll start with how you need to backup your databases in preparation for a point in time restore.
Click through for the process and to see it in action.
Comments closedAnthony Nocentino continues a series on T-SQL snapshot backups:
In this post, the third in our series on using T-SQL Snapshot Backup, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and then perform point-in-time database restores using that snapshot backup as the base, but this time using an Azure Virtual Machine. We will explore how to manage Azure storage-level operations, such as taking snapshots, cloning snapshots, and executing an instantaneous point-in-time database restore from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and Azure Az modules to automate the process.
Read on for the script and plenty of details.
Comments closedAnthony Nocentino has a two-parter for us. First is the idea of how T-SQL snapshot backups work:
Traditional SQL Server backups can struggle with large databases, resulting in longer backup times and resource contention. T-SQL Snapshot Backup, a new feature in SQL Server 2022, addresses these challenges by allowing storage-based snapshots to be coordinated through T-SQL. This feature delivers faster, more efficient backups, especially for large-scale environments with the most aggressive of recovery objectives.
The follow-up is for point-in-time recovery from a T-SQL snapshot backup:
In this post, the second in our series, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and perform point-in-time database restores using a snapshot backup as the base of the restore. We will explore how to manage storage-level operations, such as cloning snapshots and executing an instantaneous point-in-time restore of a database from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and the PureStoragePowerShellSDK2 modules to automate the process.
Check out both posts and be on the lookout for subsequent entries in the series.
Comments closedGrant Fritchey turns back the clock:
One of the single biggest reasons to go with a Platform as a Service (PaaS) offering like AWS RDS are the things it does for you, like making it really easy to restore to a point in time. Let’s take a look at it.
Click through for a demo.
Comments closedGulcin Yildirim Jelinek explains the debate around whether pg_dump is a backup tool or not:
Recently, while writing about the vulnerability affecting
pg_dump, the topic of decommissioningpg_dumpcame up on Twitter. Unlike the nostalgic feelings many had for Pluto, there was less reluctance to seepg_dumpreclassified. In fact, some people were eager to retire it as a backup utility, and I even got a bit of pushback for still referring topg_dumpas oneI was talking to my colleague Simona the other day, and she mentioned that everybody in Postgres circles says, “pg_dump is not a backup tool,” but perhaps it’s not always explained well why it is not.
Read on for that explanation.
Comments closedJeff Iannucci announces a new tool:
Whether you are backing up your SQL Server databases with Ola Hallengren’s scripts, Maintenance Plans, or some third-party software, your backups are your lifeline for recovering your data in case of a disaster.
So…are your databases recoverable? Do you know WHERE your most recent backups were written? Do you know WHEN they were written? Did you verify the backups for recoverability? Have you backed up any encryption certificates associated with your backups?
And if you think know the answers to these questions…are you sure? And can you confirm your Recovery Point Objective (RPO)?
Read on to learn more about the procedure and grab a copy from the GitHub repo.
Comments closedDavid Fowler backs up a database:
Way back in the mists of time I wrote a post on how to backup SQL server to an S3 bucket using TNTDrive, https://sqlundercover.com/2018/06/18/backup-your-on-premise-sql-server-directly-to-an-aws-s3-bucket/.
Back then, if we wanted to backup SQL to S3 we needed to use a third party tool. Since SQL 2022 things have changed and we’ve now got the option to backup directly to S3 in a similar way that we can backup to Azure BLOB store.
And, going one step further, you can also use PolyBase to read data from S3 buckets in SQL Server 2022.
Comments closedAndy Levy shares a lesson learned:
While performing an instance migration this spring, I happened upon something I didn’t expect in
[dbatools](https://dbatools.io/). It should have been a simple backup/restore copy of the databases, with the backup files residing on a fileshare on the destination server after being copied there. I kept getting a warning that the backup files I was attempting to restore couldn’t be read, and the restores (viaRestore-DbaDatabase) wouldn’t execute.I checked permissions on the server over and over again. Both on the filesystem and for the share that I was attempting to read from. Even more curious, if I executed the
restore databasestatements directly from within Management Studio, the databases restored without issue.After doing quite a bit of digging, I managed to find the reason.
Read on to learn more about necessary permissions, as well as the issue Andy hit, as well as the solution.
Comments closed