Voice of the DBA

Tag: SQLNewBlogger

What Database am I in?

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

I saw someone trying to get the database context recently and they were confused. They had some code referencing @dbname, but received an error with this code. I thought maybe they meant @@DBMAME, but when I looked, there isn’t a variable tracked by SQL Server.

A quick search (really experimenting with SQL Prompt) showed me there is a DB_NAME() function. Without a parameter, this returns my current context. That’s useful in programming systems.

If I pass in a database ID, I get the name.

I haven’t typically had an issue here. In code, if I doubt anything, I issue a USE statement to set context, but if you are looking to take actions in a generic script based on the database, this can help.

March 17, 2016
Finding Strings

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

A quick one today, just looking for strings. I wrote an article on this, so there’s more detail there, but here’s a bit of code you can look through and see what it does.

CREATE TABLE dbo.MyTable ( mystring VARCHAR(200) );

GO

INSERT dbo.MyTable

    ( mystring )

VALUES

    ( ‘This is a 7’ )

, ( ‘There is a 7 in this string’ )

, ( ‘Why must 7 always be here’ )

, ( ‘No 7s here. JK’ )

, ( ‘My 7s here’ )

, ( ‘An 7s here.’ )

, ( ‘On 7s here.’ );

GO

SELECT

    ‘SetPosition’ = SUBSTRING(mystring, 4, 1)

, ‘FindThe7’ = SUBSTRING(mystring, CHARINDEX(‘7’, mystring), 1)

FROM

    dbo.MyTable AS mt;

DROP TABLE dbo.MyTable;

March 11, 2016
Restoring a Striped Backup–#SQLNewBlogger

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

Recently I uncompressed some SQL Backup Pro files. Since multiple threads were used to make the backup, I ended up with multiple files in my file system, as shown here:

Each of these is part of a striped backup, a piece of a backup file. To restore the backup, I need all the files to reassemble the backup. This is fairly simple, but you should be aware of how this works and how to perform a restore.

In my case, you can see I have 7 files for each database. They are the same name with an _0x at the end, with x being the number of the file.

To restore these, I go to SSMS and click the restore choice. That gives me my restore database dialog, where I can select that I’m restoring from devices. As you can see below, no devices (files) are selected.

I then click the ellipsis to get a dialog allowing me to add files.

Hopefully you’ve done this before, and you can click “add” to add files. You need to naviate to the location of your backup files if it isn’t the default.

Next you can select the files. Holding down CTRL, I can multi-select files.

Once I pick them, I click OK and then I see them all in the device dialog.

Now I click OK and SQL Server reads the headers, and I end up with a single database to be restore, as shown below.

Now, I can click OK, and often do in development areas. HOWEVER, if you are doing this in production, please, please, please, click the Script button instead. You’ll get a new query window, and you can cancel out of this dialog to see the code.

From here, you should click “Save” and save this, then execute it.

As you can see above, the statement is simple. List each disk file, separated by a comma. Then the rest of the RESTORE statement is a standard restore.

SQLNewBlogger

This is a fairly simple task, one I’ve done dozens of times, but it’s worth practicing. If you want to write about it, what happens if you’re missing a file? What if you change the order of files? This was a 10 minute blog, and it’s a good chance to play and practice your restore skills, which are as important as anything you do as a DBA.

March 7, 2016
Create a Database Master Key–#SQLNewBlogger

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

One of the first things you need in a SQL Server database in order to implement encryption is a database master key, DMK. This is simple to create, though you need one in each database that will support encryption.

The syntax is easy, with only really an option to specify a password. There is no name, as there’s a single DMK per database. Set your context to the correct database and end enter:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Som3thingR3ally$|tr0ng’;

When you execute this, you’ll just get a result message. At least, if it works you will. The password must conform to the password requirements of your Windows OS, which is good.

Note: This is a securable code, like the password for a user account. Make sure you store this in a password manager for your organization.

By default, this is protected by your password as well as the Service Master Key (SMK) on your instance. In practice this doesn’t usually mean much for you, but be aware of this.

You do need CONTROL permission on the database, though usually I’d expect a db_owner or more permissions to actually create these keys.

And, of course, back up the key as soon as you can.

SQLNewBlogger

This was about 5 minutes work for me. I would guess most new bloggers could read, understand, and produce an explanation of this in 30 minutes.

References

CREATE MASTER KEY – https://msdn.microsoft.com/en-us/library/ms174382.aspx

Create a Database Master Key – https://msdn.microsoft.com/en-us/library/aa337551.aspx

February 29, 2016