Tag: backup

Limit the Blast Radius

You still need DBAs (that know how to back up systems and test restores). If you think you don’t, or if you manager does, then perhaps they ought to read this piece on how an AI agent deleted a production database. This wasn’t the case of an agent just running around with sysadmin access to all resources, or a lack of tests that allowed bad code to flow through a CI/CD process.

This was a system design that had a hole in it. An API call to change infrastructure that could change both staging and production. Not something an AI set up, but humans did. A hole from both PocketOS and the API vendor that allowed the AI agent to make the same type of mistake we’ve seen humans make. A mistake of not double checking, not verifying, not following the rules of getting a second set of eyes, even a second set of virtual eyes, on the code that could drop resources.

Reading this, I can imagine this is how some of the AWS and Azure outages occurred over the last decade. Not the 2025/2026 AI inspired ones, but the 2010-2015 human mistakes that didn’t expect a change to have such a far reaching blast radius,

You still need guardrails, for both humans and AIs. Don’t get slack and assume either truly knows what they are doing and deserves rights everywhere. Don’t assume that your guardrails were setup correctly. AI agents make great helpers. Use some read only ones to examine your setup and look for holes. If/When we get the next Claude Mythos model (or the equivalents from Google/OpenAI/etc.) have it look for precisely the types of holes that come from bad code that looks to reset, redeploy, or re-anything in your environment.

We separate out roles for different people to limit the blast radius of the mistakes we inevitably make. AIs aren’t necessarily smarter or better than humans. Just faster. We need separate roles, separate rights, and governance for AI agents, precisely because they can make decisions faster than humans.

There’s tremendous potential, but and tremendous danger in allowing anyone, or anything, too many rights in any organizations. RBAC, audits, and all the other things we implement to try and reduce the number of silly mistakes are still needed. At some point we’re going to see amazing social engineered emails, messages, XSS, and other items that are designed to fool the AIs just like humans have been fooled in the past.

We need to ensure we set good guardrails and limits when that starts to happen. Or we’re going to lose control much quicker than expected.

PS If you want a fun and slightly scary read on how AI could go sideways, I enjoyed The Final System recently, which made me not want to deploy any sort of AI agent beyond tightly scoped ones with very, very limited rights.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

May 20, 2026

Creating a Striped Backup Set with AI

I needed to test a striped backup, so I decided to ask the AI’s for help.

This is part of a series of experiments with AI systems.

The Problem

A customer wanted to know if we could handle a large striped backup set. In this case, 20 files. I didn’t want to try and do this myself because I was busy at the time, so I thought I could get the AI’s to help me.

I started with this prompt: for a sql server database called “Sandbox” can you generate a backup command that will stripe the backup across 32 files with “sandbox” the current date/time, and a number for the backup file name?

Local Gemma3 LLM

I started with a local model because I had that open. I got a result that looked OK.

Below this I had PowerShell code to help me:

-- Using PowerShell (example):
# $i = 1
# while ($i -le 32)
# {
#   $filename = "D:\Backups\Sandbox\_" + REPLACE(CONVERT(VARCHAR, GETDATE(), 112), ' ', '_') + "_" + $i.ToString("D3").PadLeft(3, '0') + ".bak"
#   # Execute the backup command:
#   Invoke-Command -ComputerName <ServerName> -ScriptBlock {
#       & "backup.exe" -Database "Sandbox" -BackupFile "$($filename)" -BackupType "D" -Init -Format "MEDIANAME = \"Sandbox\""
#   }
#   $i++
# }

This won’t work, proving that there isn’t enough database training in some of these small models. The model does warn me this won’t work as is.

Claude

Since the local model was slow, and didn’t get me what I wanted, I tried Claude. Same prompt, and I got this code, which is nice in that it handles the current date time whenever I run it.

DECLARE @BackupPath NVARCHAR(500) = 'C:\Backups\' -- Change this to your backup directory
DECLARE @DateTime NVARCHAR(20) = CONVERT(NVARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR, GETDATE(), 108), ':', '')
DECLARE @BackupCommand NVARCHAR(MAX)

SET @BackupCommand = 'BACKUP DATABASE [Sandbox] TO '

-- Generate 32 striped backup files
SET @BackupCommand = @BackupCommand +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_01.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_02.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_03.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_04.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_05.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_06.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_07.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_08.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_09.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_10.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_11.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_12.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_13.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_14.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_15.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_16.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_17.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_18.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_19.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_20.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_21.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_22.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_23.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_24.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_25.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_26.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_27.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_28.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_29.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_30.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_31.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_32.bak'' ' +
     'WITH COMPRESSION, STATS = 10'

EXEC sp_executesql @BackupCommand

I could easily then test this by adding a SELECT and commenting out the Backup command. When I do that, I see these results.

Note I’ve started adding some line breaks to the results so I can check things.

Prompt AI

I decided to try Prompt AI as well. I started with this code:

BACKUP DATABASE sandbox
TO DISK = ‘sandbox_20251013_x.bak’ WITH INIT

Then I opened Prompt AI and entered this: expand this backup command to create a striped backup of 32 files, replacing the x with a number for each file

You can see this below:

This got me a quick and dirty backup test.

Then we need to correct the syntax.

Now I have a command.

However, this doesn’t work as I can’t use string concatenation inside the backup command. I asked Prompt AI to generate a string for this instead (from this code) and then sp_exeutesql and got this code:

DECLARE @DateString VARCHAR(8) = CONVERT(VARCHAR(8), GETDATE(), 112) -- Format: YYYYMMDD

DECLARE @BackupCommand NVARCHAR(MAX)
-- Build the backup command as a string

SET @BackupCommand = 'BACKUP DATABASE sandbox TO '
-- Add all backup file destinations

SELECT @BackupCommand = @BackupCommand + CASE
                                              WHEN LEN(@BackupCommand) > 25 THEN
                                                  ', '
                                              ELSE
                                                  ''
                                          END + -- Add comma except for first item
     'DISK = ''sandbox_' + @DateString + '_' + CAST(number AS VARCHAR(2)) + '.bak'''

FROM master.dbo.spt_values

WHERE type = 'P'
       AND number
       BETWEEN 1 AND 32
-- Add WITH INIT

SET @BackupCommand = @BackupCommand + ' WITH INIT'
-- Execute the dynamically built command

--EXEC sp_executesql @BackupCommand

SELECT @BackupCommand

That works.

Summary

Both Claude and PromptAI worked, though Claude worked well with a first prompt. I continue to like the Sonnet model and find it really helpful in saving time.

It took me longer to write this post than generate the code in 3 places and test it. In terms of saving time, Claude would have saved me at least probably ten minutes, maybe more, as I’d spend time making decisions. With Claude, I got the code in under a minute and then needed to test it.

Always test your code. Including your AI-generated code.

October 22, 2025

Making a PostgreSQL Backup in a Container
I needed to back up a PostgreSQL database as a part of the repro for an issue I had. I hadn’t ever made a backup of PostgreSQL, so this was a learning exercise for me. Plus, a container made it slightly more complex than SQL Server. This post shows what I did.

In looking over the documentation and in searches, everyone seems to use pg_dump to make a backup. This looks hokey and immature to me, essentially a command line tool to script things out.

There is also a file level backup and a PITR backup strategy, but those are more complex for my use case.

So, how do I run pg_dump?

I found this article, which is helpful, but contains a lot of stuff. Essentially, I need to connect to my container and run from there. I’ll use the exec with the it switch from Docker to do this.

So, first I run this to get a shell inside the container.
```
docker container exec -it pgdev /bin/bash
```
The image below shows me connected to the container with a bash shell.

Now I can run pg_dump. I’ll use this command, which connects to the db with a user and sends a database backup to the /usr location.
```
pg_dump -U postgres -Fc bb_fullrestore > /usr/bbfull.dmp
```
Once this is done, I can go check. First, I’ll ls this folder and I see my file.

Summary

As technology advances and I use containers more, I’m hesitant to keep installing stuff on my machine that I don’t need to. I’d rather have scripts. Trying to just get pg_dump installed is a pain, so this post shows how you can access this in a container and create a backup.

If you’ve mapped your folders in a container to your local machine, then you can easily find this file and most it elsewhere for a restore.
February 19, 2025
Inside SQL Server Backup and Restore History Pruning with sp_delete_backuphistory
I had a customer that was looking to document a restore that had occurred on one of their systems and didn’t see it. They had concerns about SQL Server accurately tracking history across time and noted they hadn’t cleaned any history.

We dug through some of their instance jobs and found one that ran sp_delete_backuphistory. The person didn’t realize this removes restore history as well. This post talks a bit about how this works.

The important thing to understand here is that this removes backup and restore history. Not just backups. I don’t know I like this, but it is what is documented (emphasis mine).

In this case, the sysadmin didn’t realize this removed restore entries. Once they did, they stopped worrying about things. We could have potentially restored an old backup of msdb and found this data, but they elected not to do this.

How The Procedure Works

We can actually see the code for this proc. I have expanded the msdb programmability section under system stored procedures.

I won’t show it, but this works in the following way:
1. create three table variables with a single ID column
2. insert data into these two tables from backupset where the date is older than the parameter passed in.
  1. backup_set_id from backupset
  2. media_set_id from backupset
3. insert data into the third table that matches the backup_set_id from the table in A
4. start a transaction
  1. delete from backupfile the matching backup_set_id values
  2. delete from backupfilegroup the matching backup_set_id values
  3. delete from restorefile the matching backup_set_id values
  4. delete from restorefilegroup the matching backup_set_id values
  5. delete from restorehistory the matching backup_set_id values
  6. delete from backupset the matching backup_set_id values
  7. delete from backupmediafamily where the media_set_id values match
  8. delete from backupmediaset where the media_set_id values match
5. commit the transaction (or rollback if errors).
This is a pretty simple flow, and it works well. The tricky part is that the is joins data in a way that makes sense, but might not be what you expect. This doesn’t remove restores based on the date, but based on the backup rows being removed.

Know Your Tools

This is a poorly named procedure, but that’s not an excuse for anyone. If you use this, and likely should, you need to ensure that you understand how it works. The phrasing in the documentation makes sense, but it can be a little misleading as many of us might assume the date is applied to backup and restore history tables.

It is not.
July 3, 2024