Tag: AI

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

Prompt AI helping with Auditing

I had a conversation with a customer asking this question: how can I tell who called a stored procedure so I can audit the action?

I decided to see if Prompt could help me here.

This is part of a series of experiments with AI systems. This is also part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.

The Setup

I had this code, which is a simple stored procedure. I sketched this out, but I’m looking for a function to add to my code that helps me audit the caller.

I opened the Prompt AI (ALT+Z) and asked the question you see in the image. I also asked to assign it to the variable, but didn’t exactly specify everything.

In the image below, you can see the code that changed. Prompt AI added the System_User function, which is exactly what I wanted.

I accepted this and then asked to change this to the user instead of the login.

Again, the function I wanted was added: CURRENT_USER.

I know what I needed, or I had an idea. However, if I wasn’t sure what function to use or if there was one, this is handy. I’m working inline, rather than going to a browser and hitting Books Online or Googling for the answer. I can keep coding or looking at my code as the AI runs.

If I ask for an explanation, I get one, which helps me judge if this is actually the code I want.

If you have SQL Prompt, get Prompt AI a try by opening it with ALT+Z.

If you haven’t tried SQL Prompt, download the eval and give it a try. I think you’ll find this is one of the best tools to increase your productivity writing SQL.

Video Walkthrough

I show this live in the video below.

October 15, 2025
Adding a Local Model to Ollama through the GUI

I saw some good reviews of the small gemma3 model in a few places and wanted to try it locally. This

If you want to get started, read my post on setting up a Local LLM. This post gives an alternative to connecting to the container from the CLI and running a command.

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

Adding a Model

Using the Ollama-WebUI server, it’s easy to add models. I wrote about setting up the UI and I’m running that as my interface to the local model. I created a new container for this post, so when I see the interface, there are no models to choose from. You can see this below.

I need a model because if I try to ask a question, I get an error that the model is not selected. You can see the error at the top in this image and the query at the bottom.

If I click the gear icon by the model drop down, I get a list of settings. One of these is “Models”, which is third on the left.

I click Models and get this list. As you can see I have no models. Notice I need a model name to enter. However, where the cursor is below, there is a “click here” for a list of models.

This opens the Ollama site at https://ollama.com/library

If I scroll down, I see the gemma3 model. If I click this I see a few different ones. I am not a big fan of latest, but this is a test. I decided to just grab it since I’m not programming things.

If I enter this in my WebUI and click download, it starts downloading.

Progress is shown, and it’s not quick. I went on to other work and this is the view about 15 minutes later.

Once this is complete, I close settings and see my model in the list. Since this is the only model for this container, I’ll click “set as default”.

Now I can get my fun fact:

Now I have a safe, secure, local model to use. If you want to see this run in real-time, check out this video:

October 13, 2025
AI Model Size, Parameters and Download
At SQL Saturday Boston 2025, I gave a presentation on local LLMs and there was a great question that I wasn’t sure about. Someone asked about the download size vs the model size as listed on Hugging Face (or elsewhere). It was a good questions, and I assumed the parameter size (258M, 7B, etc.) relates to the download size or size on disk.

I did a few searches and this was a great article on LinkedIn, called The Intergalactic Guide to LLM Parameter Sizes. In it, there’s a quick guide:
- Tiny (1-3B parameters): 1-2GB on disk
- Small (4-8B parameters): 3-5GB
- Medium (10-15B): 8-15GB
- Large (30-70B): 20-40GB
- Enormous (100B-200B): 60-150GB
- Apocalypse-Inducing (500B+): 300GB+
That’s a good rule of thumb, and a good thing to know. Now, the storage isn’t that important, but the power consumption is something.

I have a laptop which has these specs:
- 12 core Ultra 7 Intel CPU, 2.1GHz (base)
- 32GM RAM
I asked a simple question of my local model (Ollama, mistral 7b), so a small model, and I see this in task manager

The power consumption in the guide says a small desk fan and any modern laptop can run it. I was playing with this on a plane and could see my battery going down. I don’t think that the storage is a problem on most modern laptops/desktops, though certainly you want a T/S/M/L model running. The power, however, can be a challenge, especially if you don’t have a good GPU. My laptop has an Intel NPU (AI Boost) designed to help AI stuff though I don’t know if the container uses it. I also have a GPU from Intel, but still, this GenAI stuff uses power.

Be careful on a laptop that isn’t plugged in if you need to get a lot of work done.
October 1, 2025