Voice of the DBA

Tag: administration

Restore One Backup From Many in a Device–#SQLNewBlogger
I wrote recently about finding multiple backups in a file. This post looks at how to restore one of those. The one you choose.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Setup

In the previous post, I did these things:
- took a backup
- added a table and data
- took a second backup
- truncated the table
- took a third backup
If I restore the default last backup, I get my table without data. You can read that post to see how I got here.

Let’s restore things.

Restoring a Backup

I cheat with restores. I remember some syntax, but typing it in and trying to remember the order is a pain, even with SQL Prompt. So I click restore database in SSMS and fill out the dialog. I pick the device and when I change the name in the Destination database, the file names change. Once I have the dialog below, I click “Script” at the top.

This gives me code in a new window. In my case, I get this code:
```
USE [master]

RESTORE DATABASE [sandbox2] 

FROM  DISK = N'D:\SQLBackup\sandbox.bak' 

WITH  FILE = 3,  

MOVE N'sandbox' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sandbox2.mdf',  

MOVE N'sandbox_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sandbox2_log.ldf',  

NOUNLOAD,  STATS = 5
```
By default, this gives me file=3, which is the third backup. If I run this and then query the new database, I see this:

That’s what I expect. The third backup had the table with no data. Let’s restore the second one. First delete the database and then change File=3 to File=2. Once I run the restore and the same query, now I see data:

If I restore file=1, then there is no table.

Alter the FILE parameter to pick the backup in the file.

SQL New Blogger

This post took less than the 10 minutes of the previous post. I basically restored my database a few times with a query. The code was a couple minutes to generate and modify in SSMS, and this writeup was short.

The key was doing this immediately after the previous post and reusing the setup and code. Plus, the concept was in my mind.

As with the previous post, this is a good way to show knowledge and learning, and in this case, 20 minutes got me two posts.
May 17, 2023
What Backups Are In This File?–#SQLNewBlogger
I had a question on multiple backups in a file and had to check my syntax. This post shows how to see which backups are in a file.

Note: Don’t do this. Put backups in separate files.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Setup

I have a sandbox database. I made a backup of this.
```
BACKUP DATABASE [sandbox] TO  DISK = N'D:\SQLBackup\sandbox.bak' 
   WITH NOFORMAT, INIT,  
   NAME = N'sandbox-Full Database Backup', 
   SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
```
Note I used INIT, which will ensure this is the only backup in this file.

I then changed something, in this case, I made a new table (I was testing things for Rich).
```
CREATE TABLE testforrich (myid INT)
GO
INSERT dbo.testforrich (myid) 
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM sys.columns AS c
GO
```
I then ran another backup. However, this time I wanted to append to the existing file.
```
BACKUP DATABASE [sandbox] TO  DISK = N'D:\SQLBackup\sandbox.bak'
  WITH NOFORMAT, NOINIT,  
  NAME = N'sandbox-Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
```
The NOINIT keyword is in here, which appends the backup to the same file. In essence, sandbox.bak will then contain two different backups in one file. For this test, I then made another change and another backup.
```
TRUNCATE TABLE testforrich
GO
```
```
BACKUP DATABASE [sandbox] TO  DISK = N'D:\SQLBackup\sandbox.bak'
  WITH NOFORMAT, NOINIT,  
  NAME = N'sandbox-Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
```
Now I have three backups in the file.

Checking Contents

If I were to click the restore item in SSMS and pick the file, I see this:

Note that the position is listed as “3”, which means this is restoring the newest (most recent) backup by default. I don’t seem to be able to edit this, though if I click timeline and change the time, I can get a different backup. I see different backups in there:

However, when are those backups? This timeline isn’t great.

I can use RESTORE HEADERONLY. The command I ran is:
```
RESTORE HEADERONLY FROM DISK = 'd:\sqlbackup\sandbox.bak'
GO
```
This gives me all three backups, which are shown as different positions in the file.

From here, I could perform a restore with a different backup if I needed to.

SQL New Blogger

This was a quick post that I wrote after I spent 5 minutes creating a test for something. I grabbed my code, took a few screen shots, and it took about 10 minutes to assemble this.

Easy for you, and this shows a potential interviewer or manager that you can dig into a small issue, learn, and solve it. Try it for yourself and write a blog post.
May 10, 2023
The Top SQL Server Engine Errors

For many of us, SQL Server just works. We might get some syntax errors if we mistype things, but for the most part, SQL Server runs smoothly in many environments. However, there are some common situations that do occur regularly, and I wonder if you can guess which errors often occur?

I saw a blog this week from the SQL Server Support group where they covered the top 25 errors that come in support calls. Their goal was to see if they could document and help people better solve problems themselves and reduce the support load.

Can you guess what the first error was? I’m assuming these are in descending order, but that’s not clear. In any case, the top error was #18456, which I didn’t recognize at first. Reading the documentation page shows this is the “login failed for xxx” error, which is probably my most common error. Often because I can’t type a password correctly, but also because of an inability to select the right instance or user name. There are other causes, and it’s nice to see a long list of things people can check.

The next error was 19407, which is a cluster communication error. If that’s the second most common error, then maybe clusters and AGs need a bit more resiliency or better setup guidance. Third is an OS error with NTFS, which I’ve never run into.

If you flip through the list, I wonder how many of these errors are common for you. Do they come up often? I know I’ve seen people post on 912, which is an upgrade error and very annoying. I think some of the upgrade scripts for CUs aren’t that well written and should have better error handling inside them. That would seem like an easy one to fix and reduce call volume.

There are plenty of network errors, including the “error occurred while establishing a connection” one. That one is usually is a typo from me or a misconfiguration of an instance after installation. Lots of other errors seem network or backup related, which may not be common, but those are errors that likely cause people to call Microsoft Support.

Maybe the most interesting one is 9002, log out of space. While I know lots of people might not know how to manage space, I also see lots of accidental DBAs get caught here because they set up full backups and not log backups. Their databases are small, storage is cheap, and they encounter this a year or so after they’ve set things up. To me, this is really low-hanging fruit by making it really easy to have an automatic backup process added for each database. Just add tooling to help make this easier, or create a job when a new database is created. If this isn’t needed, let it be disabled, but for those that are installing SQL Server for some COTS application, make this a easy.

A lot of these errors are ones I’d never call support for, but I can imagine others not feeling that way. Plenty of these are errors I’ve never seen, but I’m glad the documentation is more than just a description of what happens. These updated pages give some possible causes and things that the user can do. That’s something all of us would like in documentation when something goes wrong.

Steve Jones

March 4, 2023
The Complexity of Metrics

Monitoring your SQL Server instances is important to ensure you can meet your SLAs. Availability, performance, reliability, quality, whatever you care about, it’s important that whoever is responsible is looking at how the database is performing. At Redgate, we have multiple teams working on SQL Monitor to enhance and grow it to meet your needs.

A short while ago there was an internal conversation recently about page life expectancy. We’ve had some customers ask about this and setting alerts to watch this value. Our developers and sales engineers asked for a few thoughts from Grant and others on how to respond. There are a variety of opinions, some saying monitor it, some saying don’t bother.

I think both pieces of advice have merit, which is to say that this isn’t a metric that you can look at in isolation. There is no value of PLE that is good or bad, or that says x is wrong or y is right. There is both a subtlety and a complexity to understanding what PLE is telling you about your system. If PLE is growing, you have to look deeper. If it’s falling, same thing. If it suddenly drops, there are multiple possible causes, and you need to examine other things. However, in many cases, this isn’t an actionable metric, but one that provides context about what might be happening in the database when combined with other values you monitor.

This certainly isn’t a metric that you want to set an alert on because it can rise or fall and many times the change isn’t indicative of an acute problem.

This is just one metric of many that are available in SQL Server, and knowing which ones to monitor is something good administrators learn. They know that very few values they instrument have a good or bad value, and often the rate of change needs to be combined with the actual reading to determine if there is a problem. We also often want to know if a high (or low) reading appears for an extended period of time. Having 100% CPU being used for 3 minutes likely isn’t an issue. If it lasts for 3 hours, I might feel differently.

Metrics have more complexity than just having a range in which we ignore them and a limit at which we alert people. They are intended to be combined with each other, with observations by clients, and with the experience of looking at past observations over time. Our systems often develop patterns, and we don’t get too concerned about any values when the pattern repeats. It’s when something new happens and someone complains that we dig in to determine if there is a problem or the start of a new pattern.

We definitely need monitoring of our database metrics, but we also need to understand why values move and the implications of them doing so. That’s something which isn’t as simple as setting alert for each one based on some value we think should never be exceeded.

Steve Jones

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

January 30, 2023