Voice of the DBA

Tag: SQLNewBlogger

Restore with Standby–#SQLNewBlogger
A customer had a question about restoring with standby, so I wrote a quick post to explain how this works.

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

The Scenario

Sometimes you want to restore part of your data, but you still want the option to continue restores. A classic example of this is when you are restoring a number of transaction logs and want to check the data to find a place where certain values haven’t been changed.

Suppose someone deletes a bunch of data between 10am and 11am from the supplier table. You know that they added “Acme” to this table before the delete. You might restore up to 10am and check the supplier table for the old data and look for Acme. If it’s not there, maybe you restore the 10:05am log backup and check again. If it’s not there, then the 10:10am log, etc.

SQL Server lets you query a restored, but not recovered database with the STANDBY option. If you use NORECOERY, you can’t query the data. I won’t delve into the technical process in this blog, but for now, this is what we want to do: query a restored db, but not prevent future restores.

Setup

First, create a database and then take a backup. I created the “sandbox” database in my system and backed it up. I won’t cover that. Now, let’s set up a restore. I’ll choose a new name, since I’m looking for data. Here’s the statement I picked:
```
USE [master]

RESTORE DATABASE [sandbox4] FROM  DISK = N'D:\SQLBackup\New folder\sandbox_20210308.bak' WITH  FILE = 1,
   MOVE N'Sandbox' TO N'D:\SQLServerData\SQL2017\Sandbox4.mdf',
   MOVE N'Sandbox_log' TO N'D:\SQLServerData\SQL2017\Sandbox4_log.ldf',
   STANDBY = N'D:\SQLBackup\New folder\sandbox_RollbackUndo_2023-08-07_11-51-39.bak',  NOUNLOAD,  STATS = 5

GO
```
This is complex, and I haven’t memorized this syntax. Plus I don’t want to type all those paths. Instead, I use SSMS. I’ll set up the restore there. Here are the three screens. Note in the first, one I’ve pointed to the “Script button”, which is what I pushed to get this command.

I’ve picked the device here and changed the restore to database name.

File options. Make sure the filenames don’t conflict with existing ones.

Set the standby option and remove the tail log backup if this is the same instance as the original database.

Restoring and Querying

When I run this command, I’ll get this type of output. I like getting stats, in case something sticks.

In the object explorer, I’ll also see the database as standby/read-only.

If I open a query window, I can get data from this database.

However, I can’t update tables.

Summary

This is a quick look at how to get a database in standby. I covered one use case above, but not all the ways in which you use standby or what’s happening behind the scenes. If you need to query a database in the middle of the restoring state, use the standby option.

SQLNewBlogger

This post took me about 15 minutes to assemble. I set this up and tested it for a client, then I had to redo the work with screen shots and add the text. It’s not too long, but this is a good example of how I set up a post that leads to others. I need to create these posts:
- what happens with the standby option
- restoring more backups to this database with standby
- restoring more backups to this database without standby
- coming out of standby mode
- automating this to look for a data change
You could do this and showcase your knowledge of this feature and how you might use it
August 30, 2023
Finding Encrypted Stored Procedures–#SQLNewBlogger
I had a client ask about how to deal with encrypted stored procedures in their database. This post looks at how to find them and I’ll have future posts that show how to decrypt these and also how Flyway helps.

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

The Scenario

A client was trying to start putting their code in a VCS, but they ran into the issue that they had procedures which were encrypted. In their case someone had done this in the past and the current staff wanted to know how to get the code from production.

As a setup, here are two procs I’ve created that are encrypted:
```
CREATE PROCEDURE EncryptedOne WITH ENCRYPTION

AS

SELECT 1 AS One

GO

CREATE PROCEDURE EncryptedTwo WITH ENCRYPTION

AS

BEGIN
     DECLARE @i INT = 1;
     WHILE @i < 100
     BEGIN
         SELECT @i = @i + 1
     END
     SELECT @i / 50

END
```
These two procs don’t do anything weird, but if I try to script them like this in SSMS:

I get an error:

The error says that the text for the procedure is NULL.

The text is stored in the sys.syscomments DMV, which we can see below. This is the text that was submitted for procedures without the WITH ENCRYPTION. You can also see a NULL entry for the procedures I created above.

I can filter on this with a
```
  WHERE [text] IS NULL
```
Or I could use ObjectProperty(). This has an IsEncryted parameter I can send in with this code:
```
select name, OBJECTPROPERTY(object_id, 'IsEncrypted') AS Encrypted, OBJECT_DEFINITION(object_id) AS Code

from sys.procedures

WHERE OBJECTPROPERTY(object_id, 'IsEncrypted') = 1
```
Of course the text column isn’t needed as all the code is null here. If I wanted just a list, I’d likely only have the first two columns.

That’s it. With this script I can see those procs which are encrypted. In my case, it’s four.

SQL New Blogger

This is a quick post that shows how to find those procedures (or views) which were created with WITH ENCRPTION. I’ve run into this a few times and while this is a focused, small post, I also took the opportunity to break this into multiple posts rather than doing just one long one.

You could do this as well and showcase how you break a problem down. This took me about 10 minutes to do this post.
August 9, 2023
Finding Identity Columns–#SQLNewBlogger
I had to find a set of identity columns recently and through this would make a good blog post.

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

Getting a List of Tables and Columns with Identity Properties

Finding out which tables have an identity isn’t very easy, especially in SSMS’s Object Explorer. This property is somewhat hidden, which is annoying to me as I use them often. However, I get this is just a property.

This is, however, stored in sys.columns.is_identity. This is set to 1 if the property is set, so filtering on this is good thing. If we join to sys.columns on object_id, we can get a list of table names.

Here’s a short script to do this.
```
SELECT
   o.name AS TableName
, columns.name AS ColumnName
, is_identity
FROM
   sys.columns
   INNER JOIN sys.objects AS o
     ON o.object_id = columns.object_id
WHERE sys.columns.is_identity = 1;
```
However, there’s an easier way. There is a sys.identity_columns view which inherits from sys.columns and does the filtering for you. You can use this code instead.
```
SELECT
  o.name AS TableName
, columns.name AS ColumnName
, is_identity
FROM
  sys.identity_columns AS columns
  INNER JOIN sys.objects AS o
    ON o.object_id = columns.object_id
```
SQL New Blogger

I had to do this as a quick test for a client that wanted to do some checking of identity seeds. They asked for a list of tables to check, and I showed them how to get this quickly.

This was literally about 2 minutes to set up and about 5 minutes to write this post. Something you could easily do.
August 2, 2023
Which Schema is Queried First?–#SQLNewBlogger
I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. I wrote about that, but one of the things that occurred to me is the need to know how security works with schemas and the priority of queries.

The Scenario

A user in a database needed to access certain objects, which were going to be located in a separate schema. The previous post looked at the setup of the user, but what happens with queries. Let’s set up a few tables that will help us learn.

I’ll create two tables, one in each schema.
```
CREATE TABLE dbo.Location (locationname VARCHAR(20))
GO
INSERT dbo.Location (locationname) VALUES ('dbo schema')
GO
CREATE TABLE webapi.Location (locationname VARCHAR(20))
GO
INSERT webapi.Location (locationname) VALUES ('webapi schema')
GO
```
Let’s see what happens when we query the tables. If I write a query that qualified objects, I get what expected. I’ll login as APIUser and then run this code.
```
SELECT * FROM dbo.location
SELECT * FROM WebAPI.location
```
When I do this, I get the results expected.

If I don’t qualify the table, do you know what happens? You should, and this post shows the results that the default schema is queried first.

If I didn’t have a WebAPI.Location table, the dbo.location result would be shown. It is important that you understand SQL Server security if you manage instances or write queries, as you could otherwise feel you need to grant far too many permissions.

SQL New Blogger

This was a minor part of something else I was doing. I noted this in the previous post, but also realized the ALTER was a good second post and verifying the permission hierarchy was a good third post. I could have added this to the first one, but I like separating and focusing posts. Better for SEO if you care, but better for your workload and producing most posts.

Outside of the work I was doing, the sketch of these notes took about 2 minutes, and then the entire post was a little longer, with more code to write. This was about a 15 minute post.

You can do this.
July 26, 2023