Voice of the DBA

Tag: SQLNewBlogger

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
Adding a Computed Column–#SQLNewBlogger
Recently I needed to add a computed column to a table and realized that I didn’t remember the syntax. This short post show how to do this.

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

Adding the Column

I had a table, OrderHeader, and wanted to add a new column, OrderedByDate. I can do this with a simple ALTER statement and an ADD. The inserting part is the computation. You use as AS clause with the computation instead of a datatype. For example, my code was:
```
ALTER TABLE dbo.OrderHeader 
   ADD OrderedBy AS OrderDate;
GO
```
This added a copy of my OrderDate column with a new name. This is useful for zero downtime deployments in some cases, and in this case, I wanted to just have a copy. However, if I wanted some calculation, I could easily do that by specifying this as I would in a SELECT statement. For example, if I wanted the computed column to be a week later, I could do this:
```
ALTER TABLE dbo.OrderHeader 
   ADD OrderedBy AS DATEADDD(DAY, 7, OrderDate);
GO
```
This would add a week to the original value and return that. I can likewise do any sort of string or numeric manipulation I want. A common one is adding or multiplying two columns together for a new value. For example, adding various charges for a total in an order.

When you do this as a computed column that is not persisted, no space is taken in the actual table rows. If you persist this, then space is used.

More information on Microsoft Learn.

SQL New Blogger

I realized that I needed to double check the syntax in the docs, and when I did, I took this as an opportunity to write a short blog post. I grabbed a link, wrote some code, and then spent 10 minues knocking out this post.

If some employer does this a lot, they might search your blog to see if you can do this. A nice few posts on how to do this, what persisted does, how you index this, etc. Take a few minutes and start blogging on topics like this throughout your week.
March 1, 2023
Context Info Across Databases–#SQLNewBlogger
Does Context Info work across databases? This post shows it does.

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

The Demo

Someone asked the question, would a trigger in another database see context info from a different database. I thought it should work, but decided to test it.

Here I’m going to create a table and trigger in database compare2. This is looking for a context value.
```
USE compare2

GO

CREATE TABLE TriggerTest (myid INT, mychar CHAR(1))

GO

CREATE TRIGGER tri_triggertest ON  dbo.TriggerTest FOR INSERT

AS

BEGIN
     IF CONTEXT_INFO() = 0x1256698456
         PRINT 'caught'
     ELSE
         UPDATE dbo.TriggerTest
          SET mychar = 'X'
          FROM inserted i
          WHERE i.myid = dbo.TriggerTest.myid

END

GO
```
Now, back in DB 1, I’m going to set CONTEXT_INFO and insert a value into the database. This should give me a result where the trigger updates the table. A “normal” action.
```
USE compare1

GO

SET CONTEXT_INFO 0x000

GO

INSERT compare2.dbo.TriggerTest (myid, mychar) VALUES (1, NULL)

GO
```
This does, as the table contains a 1 and X.

Now, same connection, let’s set the magic value for context and insert a row. Now the trigger should avoid the update, letting my bypass the normal action. This is what someone was trying to do.
```
SET CONTEXT_INFO 0x1256698456;  

GO  

SELECT CONTEXT_INFO();  

GO  

INSERT compare2.dbo.TriggerTest (myid, mychar) VALUES (2, NULL)

GO
```
When I look at the results, I have the “caught” message. The final results from the table are shown here:

As you can see here, the context is with the connection, not the database. The database doesn’t matter for this value, it’s whether or not the connection that sets the context (the session really) is still alive when it accesses the other database.

SQL New Blogger

This was a quick test for me to answer a question and prove this to someone (and myself). I thought this would work, but I spent 5 minutes devising a test. It took me less than 10 minutes to put this post together.

This shows volunteerism (helping someone), testing ability, and diligence to prove something I suspected was true. I didn’t assume, I tested. Lots of employers love that.

You can raise your brand and be a SQL New Blogger like this, showing your knowledge.
February 1, 2023